help in switching form OLE DB to ODBC

[stephenc]

I have an Access database on my local hard drive, which I need to move to an
SQL server, meaning that I have to change my code from using OLE DB to using
ODBC. However, I have encountered a personal knowledge deficit, so to speak.
I <think> all I need to do is change the connection string and adjust a few
references but I am getting errors.

I am currently using the usual route, which works fine:

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\Work\MyDatabase.mdb"
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset

I am trying this instead:

sConnectionString = "Provider=MSDASQL.1;" & _
"Persist Security Info=False;" & _
"User ID=" & "myusername" & ";" & _
"Extended Properties=""" & _
"DRIVER={MySQL ODBC 3.51 Driver};" & _
"DATABASE=" & "Mydatabase.mdb" & ";" & _
"SERVER=" & "My Test PC" & ";" & _
"UID=" & "" & ";" & _
"PASSWORD=" & "" & ";"";"

with the following references:
"Microsoft ADO Ext. 2.8 for DDL and Security"
"Microsoft ActiveX Data Objects Recordset 2.8 Library"
"Microsoft ActiveX Data Objects 2.8 Library"

but this throws up the error "no default driver specified". What driver
should I be using?

 

[stephenc]

.... Just thought I should point out that my code is running in Word 2002, and
connecting to an Access database, so I think I am posting in the correct
forum.
Regards,
Stephenc