This article shows you how to create a lookup table that
contains the necessary information to register an Open Database Connectivity
(ODBC) Data Source Name (DSN) and to create new, or refresh, existing ODBC
tables in your application.
This article assumes that you are
familiar with using the tools supplied for setting up and using ODBC Data
Sources.
When you link a table to a Microsoft Access database using
an ODBC Data Source, the information regarding that connection is stored in the
Description property of the table. If you move the database to another computer
that does not contain the Data Source Name (DSN) for the ODBC connection to the
linked table, you receive the following error when you try to open the table:
ODBC--connection to <ServerName> failed.
Visual Basic for Applications supports the RegisterDatabase method
to create or modify the DSNs on a computer. Use it to refresh your existing
ODBC connections with new information or create new TableDef objects based on
the DSN.
When you implement this technique in your database, you
ensure that a code mechanism exists that will set up and relink to any ODBC
Data Sources that you use in your application.
The steps in the
following example create a DSN for a SQL Server database:
- Create a new database called TestODBC.mdb.
- Create the following table to store SQL Server ODBC Data
Source information. You can modify the fields in this table to store the DSN
information for any ODBC driver. For many ODBC connections, the Server and
Database entries will not be necessary because these values are saved with the
DSN.
Table: tblODBCDataSources
-------------------------
Field Name: DataBase
Data Type: Text
Field Size: 50
Field Name: UID
Data Type: Text
Field Size: 50
Field Name: PWD
Data Type: Text
Field Size: 50
Field Name: Server
Data Type: Text
Field Size: 50
Field Name: ODBCTableName
Data Type: Text
Field Size: 50
Field Name: LocalTableName
Data Type: Text
Field Size: 50
Field Name: DSN
Data Type: Text
Field Size: 50
Table Properties: tblODBCDataSources
------------------------------------
PrimaryKey: LocalTableName
- Create a record in the tblODBCDataSources table with
information about your ODBC databases. This example uses a SQL Server
connection to the Authors table in the Pubs database; substitute the correct
information for your environment, and add a record for each linked table in
your database:
Field Name Value
-------------------------------------
DataBase Pubs
UID <username>
PWD <strong password>
Server SQLPUBS
ODBCTableName dbo.authors
LocalTableName Authors
DSN Pubs
- Create a module and type or paste the following code. If
you are not using all the connection-related parameters--for example, if you
are not using the Server and Database arguments--comment out or omit the
corresponding lines in the strConn definition. Otherwise, passing the arguments with empty values
may cause your connection to fail.
'***************************************************************
'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.
'***************************************************************
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
Set rs = db.OpenRecordset("tblODBCDataSources")
With rs
While Not .EOF
DBEngine.RegisterDatabase rs("DSN"), _
"SQL Server", _
True, _
"Description=VSS - " & rs("DataBase") & _
Chr(13) & "Server=" & rs("Server") & _
Chr(13) & "Database=" & rs("DataBase")
' ---------------------------------------------
' Link table
' ---------------------------------------------
strTblName = rs("LocalTableName")
strConn = "ODBC;"
strConn = strConn & "DSN=" & rs("DSN") & ";"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
strConn = strConn & "UID=" & rs("UID") & ";"
strConn = strConn & "PWD=" & rs("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableName")
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If
rs.MoveNext
Wend
End With
CreateODBCLinkedTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, "MyApp"
Resume CreateODBCLinkedTables_End
End Function
- To test this function, type the following line in the Debug
window, and then press ENTER:
?CreateODBCLinkedTables()
For more information about the RegisterDatabase() function,
search the Help Index for "RegisterDatabase method," or ask the Microsoft
Access 97 Office Assistant.
For additional information about handling linked tables that
are connected to SQL Server views, click the article number below to view the
article in the Microsoft Knowledge Base:
112131
(http://support.microsoft.com/kb/112131/EN-US/
)
ACC: Creating Virtual Indexes with SQL Data-Definition Queries
Back to the top
