ACC: Procedure to Create Data Sources and Relink ODBC Tables

ID: Q159691

The information in this article applies to:

Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

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.

This article also assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

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:

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:


      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                 sa
      PWD                 &lt;blank&gt;
      Server              SQLPUBS
      ODBCTableName       dbo.authors
      LocalTableName      Authors
      DSN                 Pubs

Create a module and type the following line in the Declarations section if it is not already there:
```
      Option Explicit 
```

Type the following procedures:


      '***************************************************************
      '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.

Keywords : kbusage TblDsign TblModfy OdbcHowto
Version : 7.0 97
Platform : WINDOWS
Issue type : kbhowto