Open 2nd Access database

[klm2klm2]

If I'm using VBA to access the currently open Access database, how can I concurrently open a 2nd Access database such that I can process both databases in the same procedure?

Thanks,
Kim

 

[GHolden]

One way is to use ADO to make connections to the different databases...

eg.

Dim cnnOne As New ADODB.Connection
Dim cnnTwo As New ADODB.Connection
Dim rstOne As New ADODB.Recordset
Dim rstTwo As New ADODB.Recordset

'set connection to Test.mdb
cnnOne.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=Z:\Sample Access DB\Test.mdb;" & _
"Uid=admin;" & _
"Pwd="

'set connection to db2.mdb
cnnTwpen "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=Z:\Sample Access DB\db2.mdb;" & _
"Uid=admin;" & _
"Pwd="

'open recordset on connection one (tblName)
rstOne.Open "tblName", cnnOne, adOpenForwardOnly, adLockOptimistic, adCmdTable

'open recordset on connection two (tblTest)
rstTwpen "tblTest", cnnTwo, adOpenDynamic, adLockOptimistic, adCmdTable

'DO PROCESSING OF RECORDSETS

'close connections/recordsets
rstOne.Close
rstTwo.Close
cnnOne.Close
cnnTwo.Close

Set rstOne = Nothing
Set rstTwo = Nothing
Set cnnOne = Nothing
Set cnnTwo = Nothing

You will need a referenct to Microsoft ActiveX Data Objects.



There are two ways to write error-free programs; only the third one works.

 

[TonyJollans]

Or, a little more simply, if your second database is just your own local Access Database you don't need a connection, just open it ...

Dim myDatabase As Database
Dim myRecordset As Recordset

Set myDatabase = OpenDatabase("D:\My Documents\db1.mdb")
Set myRecordset = myDatabase.OpenRecordset("Table1")

' And away you go ... until you're done, then ...

myRecordset.Close
myDatabase.Close

Set myRecordset = Nothing
Set myDatabase = Nothing

Enjoy,
Tony