Selecting tables from another database

[luceze]

I know there is a way to append tables in databases other than the one you are currently working in. But is there any way to append a table in the current database from data in a another database. Linking tables is not an option.

Thanks,
Eric

 

[GHolden]

Don't know if this is the sort of thing you're looking for...

You can use ADO to connect to different data sources..

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
Dim strSQL As String

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

'Open recordset from Connection one
rstOne.Open "tblName", cnnOne, adOpenForwardOnly, adLockOptimistic, adCmdTable

'Open recordset from Connection two
rstTwpen "tblTest", cnnTwo, adOpenDynamic, adLockOptimistic, adCmdTable

'Copy data from one recordset to another
Do While Not rstOne.EOF
rstTwo.AddNew
rstTwo!Text = rstOne!Name
rstOne.MoveNext
Loop


cnnOne.Close
cnnTwo.Close

Hopefully some help?

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

 

[luceze]

Sounds like a good idea. I'll give it a try.