link SQLSERVER table from ACCESS 3

This is how I did it in my app. I created Function in module that runs on started from the AutoExec.


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=rptinvapps;" & _
"Pwd=mypassword;"

' 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=rptinvapps;" & _
"Pwd=mypassword;"

' 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

Hi,
This is a really great piece of code that I would love to use in an application I am working on. I'm running into one small problem however... I don't know which references to use in Access to make it work...

I appreciate your help, and someday I hope I am as smart as y'all!!

-Elysynn

'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.X for DDL and Security

The extention is 2. something.

If you don't already, also, set a reference to the ADO library. The latest is around version 8.