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.