By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 800 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Create DSN and Link SQL Table in Access - Solution

P: n/a
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 !

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Even easier not to bother with the DSN!

Check http://www.accessmvp.com/DJSteele/DSNLessLinks.html for one approach.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Bruce Lawrence" <BL*****@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
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 !

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.