Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Procedure to Create Data Sources and Relink ODBC Tables

ACC2000: Procedure to Create Data Sources and Relink ODBC Tables

View products that this article applies to.

This article was previously published under Q208295
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

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 ODBC tables in your programs, or refresh existing ODBC tables.

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.

↑ Back to the top

More information

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. 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 to 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:
  1. Create a new database called TestODBC.mdb.
  2. 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

    Collapse this tableExpand this table
    Field NameData TypeField Size
    DataBaseText50
    UIDText50
    PWDText50
    ServerText50
    ODBCTableNameText50
    LocalTableNameText50
    DSNText50

  3. Make the LocalTableName field the primary key, and save the table as tblODBCDataSources.
  4. 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:
    Collapse this tableExpand this table
    Field NameValue
    DatabasePubs
    UIDsa
    PWD<blank>
    ServerSQLPUBS
    ODBCTableNamedbo.authors
    LocalTableNameAuthors
    DSNPubs

  5. Create a module and type the following line in the Declarations section if it is not already there:
    Option Explicit
  6. Type the following procedures:NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

    '***************************************************************
'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 DAO.Database, tbl As DAO.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 DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
   Dim strDSN as String
   ' ---------------------------------------------
   ' Register ODBC database(s).
   ' ---------------------------------------------
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Select * From tblODBCDataSources Order By DSN")
   With rs
      While Not .EOF
         If strDSN <> rs("DSN") Then
            DBEngine.RegisterDatabase rs("DSN"), _
                  "SQL Server", _
                  True, _
                  "Description=VSS - " & rs("DataBase") & _
                  Chr(13) & "Server=" & rs("Server") & _
                  Chr(13) & "Database=" & rs("DataBase")
         End If
         strDSN = rs("DSN")
         ' ---------------------------------------------
         ' 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
  7. To test this function, type the following line in the Immediate 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.

↑ Back to the top

References

For more information about the RegisterDatabase() function, click Microsoft Access Help on the Help menu, type registerdatabase method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB208295, kbusage, kbhowto

↑ Back to the top

Article Info
Article ID : 208295
Revision : 3
Created on : 6/23/2005
Published on : 6/23/2005
Exists online : False
Views : 304