Multiple Database Connection

[MichHart]

I have a program that will be accessing data from over 200 tables in 4 Access Databases.

How do I retrieve data from tables from more than one database at a time??

I am running a select statement that requires data from tables from more than one database how can it be done??


Thanks for any help in advance

MichHart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."

 

[jstiegelmeyer]

What type of database are you connecting to?

The only way I know how to handle this in MSAccess is to create a copy of one of the tables in the database where the other table resides. The easiest way to create a copy is to create a table with the fields you need and spin through a recordset putting each record into the new table one at a time.

If you're connecting to SQL Server, you can connect to different databases by adding MyDB.DBOwner to the name of each table. The default for DBOwner is "dbo", so if you don't know what it is, that's probably it.

ie:
SELECT MyDB1.dbo.MyTable1.MyField1
FROM MyDB1.dbo.MyTable1, MyDB2.dbo.MyTable2
WHERE MyDB1.dbo.MyTable1.MyField1 = MyDB2.dbo.MyTable2.MyField2

Hope this is helpful.

Jamie

 

[MichHart]

I am programming in VB 6, connecting through ADO to ACCESS 2000.

I am lost with regard to your suggestion. Sorry!!

MichHart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."

 

[jstiegelmeyer]

For Access, you either need to use my first option, or create a link to one table inside the database where the other table resides.

Here's the code I use for that:

Public Function CreateLink(p_strTblName As String, _
p_PROJ_DB As ADODB.Connection, _
p_PROJ_DB_SRC As ADODB.Connection, _
p_strDBSrcName As String) As String

Dim objCtlg As ADOX.Catalog
Dim objTbl As ADOX.Table
Dim strTblName As String

strTblName = p_strTblName

Set objCtlg = New ADOX.Catalog
objCtlg.ActiveConnection = p_PROJ_DB

' Delete the link if it already exists
On Error Resume Next
objCtlg.Tables.Delete strTblName
On Error GoTo 0

Set objTbl = New ADOX.Table
With objTbl
.ParentCatalog = objCtlg
.Name = strTblName
.Properties("Jet OLEDB:Link Datasource&quot = p_PROJ_DB_SRC.Properties("Data Source&quot
.Properties("Jet OLEDB:Link Provider String&quot = "MS Access;PWD="
.Properties("Jet OLEDB:Remote Table Name&quot = p_strTblName
.Properties("Jet OLEDB:Create Link&quot = True
.Properties("Temporary Table&quot = True
End With

' Add the table to the Tables collection.
objCtlg.Tables.Append objTbl

CreateLink = strTblName

End Function

Good luck!

 

[Merlijn]

You just have to establish two simultanious connections. One to the first database, one to the second. Then you perform a query on the first database, and with the results you perform a query on the second database.
Kind of what jstiegelmeyer stated in his first reply, but in human language

Hope this helps you out.

Merlijn is the name, and logic is my game.