Linking tables with DDL

[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

 

[Douglas J. Steele]

AFAIK, it's not possible using DDL. You can use DAO, or you can use ADOX.

 

[Ivan Debono]

AFAIK, it's not possible using DDL. You can use DAO, or you can use ADOX.

How would I do that with ADOX?

Ivan

 

[Douglas J. Steele]

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.

 

[david epsom dot com dot au]

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

(david)

 

[Douglas J. Steele]

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

 

[Ivan Debono]

I usually do that with SQL Server (Enterprise Manager) but how about with
DDL?

Ivan

 

[david epsom dot com dot au]

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)