Linking tables with DDL

  • Thread starter Thread starter Ivan Debono
  • Start date Start date
I

Ivan Debono

Hi all,

I have to mdb databases and I want to link a table in one mdb into the other
mdb. Is it possible to do it using DDL?

Thanks,
Ivan
 
Actually, I misread your original post.

You cannot set up relationships between tables in different MDBs, regardless
of what technique you use.

Tables must exist in the same MDB file to be related.

Sorry for the confusion.
 
The DDL equivalent would be to create a view
that references the source database.

(david)
 
Jeez, not a great day for me! <g>

To link a table to the table whose name is in strTableNm, in a database
whose full path is stored in PathToBackendDatabase using ADOX:

Dim objFECatalog As ADOX.Catalog
Dim objFETable As ADOX.Table

Set objFECatalog = CreateObject("ADOX.Catalog")
objFECatalog.ActiveConnection = _
CurrentProject.Connection
Set objFETable = CreateObject("ADOX.Table")
objFETable.Name = strTableNm
Set objFETable.ParentCatalog = objFECatalog
objFETable.Properties( _
"Jet OLEDB:Link Datasource") = _
PathToBackendDatabase
objFETable.Properties( _
"Jet OLEDB:Remote Table Name") = _
strTableNm
objFETable.Properties( _
"Jet OLEDB:Create Link") = True
objFETable.Properties( _
"Jet OLEDB:Link Provider String") = _
"MS Access;PWD=Admin;"
objFECatalog.Tables.Append objFETable
 
Application.CurrentProject.Connection.Execute _
"create view myview as select * from [c:\dev9.mdb].[table1]"

The view will not be visible in the Access database window
unless the database is in "ansi" mode, but you can use
the view in queries or code:

?codedb.openrecordset("myview").Fields(0).name
id

(david)
 
Back
Top