linked table

[erased]

hello all,

how do i create a linked table with
a 'CREATE TABLE' statement?

greets, alex

 

[Ed2020]

If you want to link in a table that already exists in another MDB file this should do the trick:

Public Sub ConnectTable(strMDBPath As String, strTableName As String)
'Links the specified table into the current mdb file

Dim Mainbase As DAO.Database
Dim MyTabledef As DAO.TableDef

Set Mainbase = CurrentDb()
Set MyTabledef = Mainbase.CreateTableDef(strTableName)
MyTabledef.Connect = "MS Access" & ";DATABASE=" & strMDBPath
MyTabledef.SourceTableName = strTableName

Mainbase.TableDefs.Append MyTabledef

End Sub

Remember that you will have to refresh the database window to actually see the table.

No error handling either...... sorry.

Ed Metcalfe
ed_metcalfe@hotmail.com

 

[erased]

thanx

im going to try that.

 

[erased]

i also found a way to do this through ado;

------------------------------------------------------
Private Sub linktable()
Dim adoCn As ADODB.Connection
Dim adoCat As New ADOX.Catalog
Dim adoTbl As New ADOX.Table

'Open database
Set adoCn = New ADODB.Connection
With adoCn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open "e:\atemp\geo_test.mdb"
End With

'Create Link...
Set adoCat = New ADOX.Catalog
Set adoCat.ActiveConnection = adoCn

Set adoTbl.ParentCatalog = adoCat
adoTbl.Name = "geo_main"

adoTbl.Properties("Jet OLEDB:" &_
"Link Datasource&quot "c:\temp\geo_transfer.mdb"

adoTbl.Properties("Jet OLEDB:" &_
"Link Provider String&quot = "MS Access"

adoTbl.Properties("Jet OLEDB:Remote Table Name&quot = "main"
adoTbl.Properties("Jet OLEDB:Create Link&quot = True

'Append the table to the tables collection
adoCat.Tables.Append adoTbl

End Sub
-------------------------------------------------------

this is a slightly modified piece of code from the msdn lib

 

[Ed2020]

Good example, but ADO is slower when working with native MS Access tables. I'd stick with DAO.

Ed.