none
Linking Access 2007 database to a table in another Access 2007 database using VBA

    Question

  • I wish to use VBA to change a linked table from one Access 2007 database to another Access 2007 database. 

    The goal being to distribute a front end / back end solution.

     

    I've tried and tried to search existing posts to solve the problem.

     

    I'm getting this error:

    Runtime error '3170'

    Could not find installable ISAM

    I know that this error message can mean many things:  missing registries, but also  a bad connection string.  My guess is that in this case it is a bad or inappropriate connection string.

     

    Could someone please help straighten me out?

     

    -          Frank

     

    Here is my code:

     

    Function RefreshLinks()

     

        ' Declare variables

        Dim currPath As String

        Dim myConnectionString As String

        Dim tdf As TableDef

        Dim strTableName As String

        Dim strSourceDatabase As String

        Dim strSecurity As String

       

        ' Initialize variables

        currPath = CurrentProject.path

    '    myConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source="""

        strSourceDatabase = "OIAdatabase_Tables.accdb"""

        strSecurity = ";Persist Security Info=False"

       

        strTableName = "tTitle"

       

       'If fTableExist(strTableName) = True Then CurrentDb().TableDefs.Delete (strTableName)

     

        With CurrentDb

            Set tdf = .CreateTableDef(strTableName)

           

            tdf.SourceTableName = strTableName

            tdf.Connect = myConnectionString & currPath & strSourceDatabase & strSecurity

     

          .TableDefs.Append tdf                                      ' HERE IS WHERE THE RUN-TIME ERROR 3170 OCCURS

           .Close                   ' Release Object

        End With

       

        DoCmd.Beep

     

    End Function

     

    Function fTableExist(TableName) As Boolean

     Dim td As TableDef

     For Each td In CurrentDb().TableDefs

       If td.NAME = TableName Then

         fTableExist = True

         Exit For

       End If

     Next

    End Function

    Sunday, February 06, 2011 8:04 PM

Answers

  • You cannot use OleDB when linking tables (or queries) in Access. You must use ODBC. And in actual fact, you cannot use ODBC from Access to Access.
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Edited by Douglas J Steele, MVPMVP Tuesday, February 08, 2011 11:37 PM Previous answer was incomplete (and misleading).
    • Marked as answer by Bessie Zhao Monday, February 14, 2011 10:00 AM
    Sunday, February 06, 2011 8:35 PM
  • To determine what you need in a connect string manually link a table in the backend.  Then:
    Ctrl-g
    ?CurrentDb.TableDefs("<linked table name>").Connect

    Will show you exactly what is needed.

    You can also look in the MSysObjects table.  Find the row for the linked table.  There are two columns of interest:
    Connect - this contains everything but the database path\name
    Database - this contains the database path\name

    You will notice that the password for the backend, if it has one, is clearly visible in the MSysObjects table.

    • Marked as answer by Bessie Zhao Monday, February 14, 2011 10:00 AM
    Sunday, February 06, 2011 10:45 PM
  • As Doug says, you need to use regular linked Access tables, not ODBC.
    Start in External Data and establish the links there.
     
    After you have linked the tables, you need a way to switch back-ends.
    You're welcome to use our free J Street Access Relinker on our J
    Street Downloads page:  http://ow.ly/M56Q   It's a lot nicer than the
    Linked Table Manager.
     
    It's some simple code that you copy into your front-end application
    (replacing all the code you showed in your post).  It handles multiple
    Access back-end databases, ignores non-Access tables, and can
    automatically and silently relink to back-end databases in the same
    folder as the application (handy for work databases or single-user
    scenarios).  There's a ReadMe table with instructions.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    • Marked as answer by Bessie Zhao Monday, February 14, 2011 10:00 AM
    Tuesday, February 08, 2011 11:15 PM

All replies

  • You cannot use OleDB when linking tables (or queries) in Access. You must use ODBC. And in actual fact, you cannot use ODBC from Access to Access.
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Edited by Douglas J Steele, MVPMVP Tuesday, February 08, 2011 11:37 PM Previous answer was incomplete (and misleading).
    • Marked as answer by Bessie Zhao Monday, February 14, 2011 10:00 AM
    Sunday, February 06, 2011 8:35 PM
  • To determine what you need in a connect string manually link a table in the backend.  Then:
    Ctrl-g
    ?CurrentDb.TableDefs("<linked table name>").Connect

    Will show you exactly what is needed.

    You can also look in the MSysObjects table.  Find the row for the linked table.  There are two columns of interest:
    Connect - this contains everything but the database path\name
    Database - this contains the database path\name

    You will notice that the password for the backend, if it has one, is clearly visible in the MSysObjects table.

    • Marked as answer by Bessie Zhao Monday, February 14, 2011 10:00 AM
    Sunday, February 06, 2011 10:45 PM
  • As Doug says, you need to use regular linked Access tables, not ODBC.
    Start in External Data and establish the links there.
     
    After you have linked the tables, you need a way to switch back-ends.
    You're welcome to use our free J Street Access Relinker on our J
    Street Downloads page:  http://ow.ly/M56Q   It's a lot nicer than the
    Linked Table Manager.
     
    It's some simple code that you copy into your front-end application
    (replacing all the code you showed in your post).  It handles multiple
    Access back-end databases, ignores non-Access tables, and can
    automatically and silently relink to back-end databases in the same
    folder as the application (handy for work databases or single-user
    scenarios).  There's a ReadMe table with instructions.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    • Marked as answer by Bessie Zhao Monday, February 14, 2011 10:00 AM
    Tuesday, February 08, 2011 11:15 PM