Attaching a table to a remote Database

[Pulse]

Hello !

I'm trying to make a link between two Access databases. Actually there are a
few Tables in one of them I want to attach to the other (attach or add)
knowing that the schema of the tables changes depending of which database is
opened in the first place. Consequently I can't write a SQL script I can run
against the second database.
Can anyone help me doing this ?
I don't have an idea on how to program such an operation in Visual Basic.NET
:/

-Pulse

 

[Mary Chipman]

Linking Jet tables in code can be accomplished using either DAO or
ADO, so you'll have to go to COM Interop for this if you want to do it
in code. I don't there's any way to do this by running Access SQL.

-- Mary
MCW Technologies
http://www.mcwtech.com

 

[Paul Clement]

¤ Hello !
¤
¤ I'm trying to make a link between two Access databases. Actually there are a
¤ few Tables in one of them I want to attach to the other (attach or add)
¤ knowing that the schema of the tables changes depending of which database is
¤ opened in the first place. Consequently I can't write a SQL script I can run
¤ against the second database.
¤ Can anyone help me doing this ?
¤ I don't have an idea on how to program such an operation in Visual Basic.NET

Here is an example that uses ADO/ADOX (Microsoft ADO Ext 2.x for DDL and Security). You will need to
add these COM references to your project:

Sub CreateAttachedAccessTableWithADOX()

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection

Try

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

ADOXTable.Name = "Table11Linked"
ADOXTable.ParentCatalog = ADOXCatalog
ADOXTable.Properties("Jet OLEDB:Link Provider String").Value = "MS Access;DATABASE=E:\My
Documents\AccessDB.mdb"
ADOXTable.Properties("Jet OLEDB:Remote Table Name").Value = "Table11"
ADOXTable.Properties("Jet OLEDB:Create Link").Value = True
ADOXCatalog.Tables.Append(ADOXTable)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try

End Sub


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Pulse]

Thanks a lot to both of you