join tables from different mdb

[nedelcho]

Is it possible to use joins of tables that are in different access DB? I am using VB and ADO.

[Iouri]

In the first database, use ADOX to create a link to the table in the
second database. Then the program can act as if both tables are in the
first database.

' Note: This program includes a references to:
'
' Microsoft ActiveX Data Objects 2.6 Library
' Microsoft ADO Ext. 2.6 for DDL and Security
' Source table must have primary key

Private Sub Command1_Click()
Dim app_path As String
Dim db_file As String
Dim conn As ADODB.Connection
Dim adox_catalog As ADOX.Catalog
Dim adox_table As ADOX.Table
Dim rs As ADODB.Recordset
Dim i As Integer
Dim txt As String

app_path = App.Path
If Right$(app_path, 1) <> "\" Then app_path = app_path & "\"

' Open the Depts database.
db_file = app_path & "Depts.mdb"
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn.Open

' Create a link to the Employees table
' in the Emp database.
Set adox_catalog = New ADOX.Catalog
Set adox_catalog.ActiveConnection = conn

Set adox_table = New ADOX.Table
With adox_table
Set .ParentCatalog = adox_catalog
.Name = "LinkedTable"
.Properties("Jet OLEDB:Link Datasource") = app_path &
"Emps.mdb"
.Properties("Jet OLEDB:Link Provider String") = "MS Access"
.Properties("Jet OLEDB:Remote Table Name") = "Employees"
.Properties("Jet OLEDB:Create Link") = True
End With

' Add the table to the Tables collection.
adox_catalog.Tables.Append adox_table

' Perform the query.
Set rs = conn.Execute( _
"SELECT * " & _
"FROM Departments, LinkedTable " & _
"WHERE Departments.DepartmentId = LinkedTable.DepartmentId",
,
_
adCmdText)

' Display the results.
Do While Not rs.EOF
txt = txt & rs.Fields(0).Value
For i = 1 To rs.Fields.Count - 1
txt = txt & ", " & rs.Fields(i).Value
Next i
txt = txt & vbCrLf

rs.MoveNext
Loop
rs.Close
txtResults.Text = txt

' Delete the linked table.
adox_catalog.Tables.Delete "LinkedTable"

' Close the database.
conn.Close
End Sub


Iouri Boutchkine
iouri@hotsheet.com

[nedelcho]

Thanx.Is that reducing seriously the performance compared to tables in one and the same db?Where i can reed more?

[Iouri]

I don't think that will screw the performance. Again it depends on how often you going to link tables.
To read MSDN about ADOX

Iouri Boutchkine
iouri@hotsheet.com