connect to mdb from accdb

[teel73]

I am trying to connect to an access database format 2003 from my access 2007 database. I would like to create a recordset from a table in the 2003 database and bind it to a listbox in my 2007 database. What would be the best method? I tried to the following code that didn't work.

Dim DNHConn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim connStr, xSource, xSys, xUsr, xPsw
On Error GoTo errH
xSource = "C:\DNHList.mdb"
xSys = "C:\DNH.mdw"
xUsr = "CONTRACTORSDB"
xPsw = "testpswd"
 
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xSource;Jet OLEDB:System Database=xSys;User ID=xUsr;Password=xPsw;"
Set DHNConn = New ADODB.Connection
DHNConn.Open connStr
Set Rs = New ADODB.Recordset
With Rs
    .CursorLocation = adUseClient
    .ActiveConnection = DNHConn
    .CursorType = adOpenForwardOnly
    .LockType = adLockBatchOptimistic
    .Source = dnhSQL
    .Open
End With
Set connDNH = Rs
Set Rs = Nothing
Set DNHConn = Nothing
Debug.Print connDNH

When I try to execute that code, I get an error that the apllication can't start because the worgroup file is either missing or opened exclusive by another user.

What am I not doing? Can anyone assist with this?

 

[Mr. B]

Why not just use the native functionality and create a link to the tables you need from the 2003 database?

 

[Pat Hartman]

I'm with Mr. B. Access is a rapid application development tool and a great deal of what you need to do can be done with property settings. It is always best to use the "Access" way rather than code if you have a choice.

Your error message sounds like the .mdb you are accessing is secured with a work group. If you link to the tables, you will be asked to supply a password.