How to connect to a SQL database

[spence27]

hi

I am running Excel 2000 and need to . 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

 

[sfvb]

Before running this, and while you're in the VBA Editor:
From the menu, select Tools-->References... and add a reference to Microsoft ActiveX Data Objects xxx Library
where the xxx = 2.5, 2.6, 2.7 or whichever is the highest number, you have available.

When you're testing it, you may need to set a fully qualified path to the data source. For example, instead of
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=.\NorthWind.mdb;"
you may need:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\blahblahblah\NorthWind.mdb;"

You'll probably run into other problems that you may or may not be able to solve on your own, but hopefully this will get you started.

 

[spence27]

Thanks, That sorted it out this VBA stuff looks like Lotus Script ,but really is totaly different.

Spence