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...
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(st rTblName As String) As Boolean
On Error Resume Next
Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(st rTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function
Function CreateODBCLinke dTables() As Boolean
On Error GoTo CreateODBCLinke dTables_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.Regist erDatabase "nameofdsn" , _
"SQL Server", _
True, _
"Description=DS Ndescription" & _
Chr(13) & "Server=SQLserv ername" & _
Chr(13) & "Database=SQLdb Name"
' ---------------------------------------------
' Link table
' ---------------------------------------------
strTblName = "AccessTableNam e"
strConn = "ODBC;"
strConn = strConn & "DSN=nameofdsn; "
strConn = strConn & "APP=Micros oft Access;"
strConn = strConn & "DATABASE=SQLdb name;"
strConn = strConn & "UID=userna me;"
strConn = strConn & "PWD=passwo rd;"
strConn = strConn & "TABLE=SQLtable name"
If (DoesTblExist(s trTblName) = False) Then
Set tbl = db.CreateTableD ef(strTblName, _
dbAttachSavePWD , ("SQLtablename" ), _
strConn)
db.TableDefs.Ap pend tbl
Else
Set tbl = db.TableDefs(st rTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If
CreateODBCLinke dTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinke dTables_End:
Exit Function
CreateODBCLinke dTables_Err:
MsgBox Err.Description , vbCritical, "MyApp"
Resume CreateODBCLinke dTables_End
End Function
_______________ _______________ _______________ ___
Not sure if this is the most streamlined way to do it but it works.
Have Fun !
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...
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(st rTblName As String) As Boolean
On Error Resume Next
Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(st rTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function
Function CreateODBCLinke dTables() As Boolean
On Error GoTo CreateODBCLinke dTables_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.Regist erDatabase "nameofdsn" , _
"SQL Server", _
True, _
"Description=DS Ndescription" & _
Chr(13) & "Server=SQLserv ername" & _
Chr(13) & "Database=SQLdb Name"
' ---------------------------------------------
' Link table
' ---------------------------------------------
strTblName = "AccessTableNam e"
strConn = "ODBC;"
strConn = strConn & "DSN=nameofdsn; "
strConn = strConn & "APP=Micros oft Access;"
strConn = strConn & "DATABASE=SQLdb name;"
strConn = strConn & "UID=userna me;"
strConn = strConn & "PWD=passwo rd;"
strConn = strConn & "TABLE=SQLtable name"
If (DoesTblExist(s trTblName) = False) Then
Set tbl = db.CreateTableD ef(strTblName, _
dbAttachSavePWD , ("SQLtablename" ), _
strConn)
db.TableDefs.Ap pend tbl
Else
Set tbl = db.TableDefs(st rTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If
CreateODBCLinke dTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinke dTables_End:
Exit Function
CreateODBCLinke dTables_Err:
MsgBox Err.Description , vbCritical, "MyApp"
Resume CreateODBCLinke dTables_End
End Function
_______________ _______________ _______________ ___
Not sure if this is the most streamlined way to do it but it works.
Have Fun !
Comment