Article ID: 159691
Article Last Modified on 1/19/2007
APPLIES TO
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q159691
Advanced: Requires expert coding, interoperability, and multiuser skills.
SUMMARY
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.
MORE INFORMATION
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:
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
Note that the ODBCTableName represents the name of the table in the source database (in this example, SQL Server), and that the LocalTableName represents the name assigned to the linked table in the Access database.
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()
Note that you receive the message "Refreshed ODBC Data Sources." Also note that a new linked table called Authors exists in your database, and a new User DSN called Pubs exists in the ODBC Administrator in Control Panel.
REFERENCES
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 ACC: Creating Virtual Indexes with SQL Data-Definition Queries
Additional query words: inf
Keywords: kbhowto kbusage KB159691