Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002

    Unanswered: Create a linked table in access to SQL server programatically

    I'm very sorry to join and ask for help immediately without helping first, but I am at wits end with this.

    I need to know how to promatically create a linked table in Microsoft Access 97 to a Microsoft SQL Server 2000 table. The SQL server is set to use trusted connection for authentication. Security is set up correctly because manual creation of the linked table works fine and the table can then be accessed normally. But we have tried many different methods to create this linked table programatically and all have failed.

    This Microsoft Access application links tables to both an AS400 and SQL Server 2000 and we are currently using the DoCmd.TransferDatabase function. It works fine to create the linked tables to the AS400 but fails to create the linked table for SQL Server.

    The whole purpose of this is because the Access application is coded with a debug mode. At each start, the application destroys linked tables and rebuilds them either locally (debug mode) or to their real homes (live mode).

    We have looked at hundreds of knowledge base entries and browsed for nearly 2 days trying to find answers. Please help.


  2. #2
    Join Date
    Dec 2001
    Toronto, Canada
    I just opened up an Access97 database and entered this code into a macro
    Function CreateLink()
      DoCmd.TransferDatabase acLink, "ODBC Database", _
      "ODBC;DSN=ATLAS;DATABASE=pubs;Trusted_Connection=Yes ",_
       acTable, "Authors", "dboAuthors"
    End Function
    Where the DSN = ATLAS is and ODBC connection to SQL Server 2000 on another machine. This code works fine, I ran it and the link was created.

Posting Permissions

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