Create MS Access ODBC link to SQL Database 1

[benaround]

I could really use some VB Access 2000 code to create an ODBC link to a MS SQL database. We have 9 SQL db's, that run many of the same reports, this code will help out a great deal in streamling reporting.

I've looked at TableDefs and other methods but am lost on how to proceed.

Please do not suggest creating a link table like tblA-db1, tblA-db2, etc. and then run queries by changing the db1 to db2, then run the queries for the other db's, thats just to simple of a solution that I have already tried to suggest.

Code to create ODBC links to a SQL db is needed here. Thanks ahead of time for any help, its greatly appreciated.

 

[Elysium]

Are there DSNs set up on the machine?

Randy

 

[cmmrfrds]

If do it through code then when the app is moved to a client there is no need to set up an ODBC on their PC.

a couple of functions that should get you started.


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={bigtuna};" & _
"Database=pubs;" & _
"Uid=sa;" & _
"Pwd=;"

' 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 = "authors"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "authors"
.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={local};" & _
"Database=pubs;" & _
"Uid=sa;" & _
"Pwd=;"

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

 

[benaround]

Yes there are DNS's on the machines but I think that cmmrfrds has solve my problem / question. Thanks to everyone for all your help its greatly appreciated.