ADO - External Access Database Acquire

[sanders720]

I am trying to use an ADO connection to pull data out of another Access database into my current one. This syntax does not seem to run through, as the debug.print statements are not working. Also, should db_file be a string or a database?

Thanks in adnavce for the help...


Private Sub cmdGetData_Click()
Dim conn As ADODB.Recordset
Dim db_file As Database

db_file = "v:\" + Str(Me.cboJobNo.Value) + ".mdb"

Debug.Print db_file

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Mode=Read;" & _
"Persist Security Info=False"

Debug.Print conn

 

[cmmrfrds]

"Persist Security Info=False"

'- open the connection and then check
conn.open

Debug.Print conn

It doesn't make any difference how you construct the data source identification as long as it ultimately resolves to the correct syntax.

Debug.Print conn.ConnectionString

If you ever have any doubt on the connection string you can build with the UDL Wizard and test before including in your program.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft Access provider – this will be Jet 4.0.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.