hi I am running Excel 2000 and n

[spence27]

hi

I am running Excel 2000 and need to connect to a SQL database. The MS Query editor does not do the job as i will need to 1)create a temp table 2)fill it with data 3)Run a query & return the results 4) drop the temp table. i.e. it is really a collection of Procedures.

I can do all the SQL stuff. I just don't know how to deal with it through excel i have been to MSDN and had a look and found out some stuff about ADO and ODB, but if i copy it into the VBA editor (see below) it comes up "user defined type not recognised", and highlights Dim cnn As New ADODB.Connection

Any help would really be welcome.

ps. Does anyone know a solid book to cover all things VBA Our company is moving from Lotus SS after many years to MS. So i have gone from being very comfortable with lotus script to being a bit uneasy with this VBA stuff.


ADO
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field

' Open the connection
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\NorthWind.mdb;"

' Open the forward-only,
' read-only recordset
rst.Open "SELECT * FROM Customers " & _
"WHERE Region = 'WA'", cnn, _
adOpenForwardOnly, adLockReadOnly

' Print the values for the fields in
' the first record in the debug window
While Not rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Wend
'Close the recordset
rst.Close

End Sub

 

[JohnCHolmes]

I don't have your answer but have general advice that might lead you forward.

In the barfing code highlight
.Connection
and hit F1. Then click on the word Connection. Then click example and choose Connection Object. I believe this will show you the format you need, although I sympathize with you as more frustrations may follow

By the way, in case it comes up ... whenever I start a New Access database I *always* first do this in the VBA window:
tools/references/check Microsoft DAO 3.6 Object Library
get RID of ADO 2.1

Others may disagree on the last suggestion, but vast numbers would not; consider it as an option when you have legitimate code that cluelessly won't compile in VBA.