Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    14

    Unanswered: Link tables using code

    Hi

    Can anyone help? I need to link two tables from one (access) database to another (access) database using code but I don't know where to start.

    Any help would be appreciated.

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try
    DoCmd.TransferDatabase acLink, "Microsoft Access", _
    "C:\My Documents\NWSales.mdb", actable, "yourSourceTableName", _
    "yourDestinationTableNameInTheCurrentDB"

  3. #3
    Join Date
    Mar 2005
    Posts
    14
    That worked a treat. Thanks.

    If the table is already linked it creates a duplicate link with a slightly different name. Do you know how I can check whether the table already exists first and delete it if it does? (the joys of locking down a PC so the user doesn't have mapped drive letters).

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Make a function call it myTableExist like that

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    PHP Code:
    Function myTableExist(TableName As String) As Boolean
      Dim db 
    As Database
      Dim tbl 
    As TableDef

      On Error 
    GoTo ERR_
          Set db 
    CurrentDb
        
        Set tbl 
    db.TableDefs(TableName)
        
    myTableExist True
        
    EXIT_
    :
      
    Set db NothingSet tbl Nothing
      
    Exit Function

    ERR_:
        
    myTableExist False
        Resume EXIT_
    End 
    Function 
    Last edited by hammbakka; 11-30-06 at 15:57.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you want to refresh the link you can use:

    DoCmd.DeleteObject

    I typically use this command with error trapping to delete and relink a table:

    On Error Resume Next
    DoCmd.DeleteObject acTable, strTableName
    On Error Goto 0

    DoCmd.TransferDatabase ...

    The result is, if you have the table already setup it will delete it and refresh it. If you don't have the table setup the error trapping will continue processing and set up the link.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •