How to connect to a SQL database

[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 it(see below)comes up "user defined type not recognised"

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

 

[clarkin]

can't see the problem with it myself, but you could try the VBA forum (forum707 - this VBscript forum is generally associated with ASP pages

They might be able to help you quicker there.