I know this works for Access 97. Not sure about other versions.
This will create a DSN to a SQL server in your ODBC connections and
then link a table of your choise from that SQL DB to the Access
Database. You could modify it anyway you like. Such as passing
variables to the values you have to change vs. manually typing them in.
Microsoft uses a table to pass the data here...
http://support.microsoft.com/kb/q159691/
I just changed it a bit because I wasn't crazy about having that info
in a table that could accidently be deleted or whatever.
__________________________________________________ __
Function DoesTblExist(strTblName As String) As Boolean
On Error Resume Next
Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function
Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As Database, rs As Recordset, tbl As TableDef
' ---------------------------------------------
' Register ODBC database(s)
' ---------------------------------------------
Set db = CurrentDb
DBEngine.RegisterDatabase "nameofdsn", _
"SQL Server", _
True, _
"Description=DSNdescription" & _
Chr(13) & "Server=SQLservername" & _
Chr(13) & "Database=SQLdbName"
' ---------------------------------------------
' Link table
' ---------------------------------------------
strTblName = "AccessTableName"
strConn = "ODBC;"
strConn = strConn & "DSN=nameofdsn;"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=SQLdbname;"
strConn = strConn & "UID=username;"
strConn = strConn & "PWD=password;"
strConn = strConn & "TABLE=SQLtablename"
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, ("SQLtablename"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If
CreateODBCLinkedTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, "MyApp"
Resume CreateODBCLinkedTables_End
End Function
________________________________________________
Not sure if this is the most streamlined way to do it but it works.
Have Fun !
