Link Databases Tables using only VBA Code?

[brettatexplace]

When my Access2000 database opens, I want to create access to some tables that are not previously linked to the Program. (trying to hide background tables as much as possible from end users, so they cannot open a table from the tables tab of the database window). When a specific form is opened, I would like to establish the link, and then, on close, remove the link....

Is there something simple like
OnCmd OpenTable,"TableName","fileName",NetworkPath ???

Thanks

 

[PHV]

You may find this of interest:

http://www.peterssoftware.com/tlk.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[brettatexplace]

Thanks, it looks interesting.

 

[cmmrfrds]

Here is the code I use to link an SQL Server table. Run the code when developing the app on the local network and when you copy the app to a desktop Access will use the link stored with the table. The same logic should apply to other data sources.

Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=YourID;" & _
"Pwd=YourPassword;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "dbo_tblReportRequestPrelim"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

Public Function SQLServerLinkedTableRefresh()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=YourID;" & _
"Pwd=YourPassword;"

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
End Function

 

[brettatexplace]

Thanks, I will try it.