Unanswered: Create linked table 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.
You can try this. It worked in 2000, assume it would work in 97.
Dim tbl As TableDef, conStr As String, tblNm
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
'set connection string to SQL Server database
conStr = "ODBC; DATABASE=yourDB; UID=yourUID; PWD=yourPWD; DSN=yourSQL Server data source name"
tblNm = "testLink"
Set tbl = CurrentDb.CreateTableDef(tblNm)
tbl.Connect = conStr
tbl.SourceTableName = tblNm
'add table to local tabelDefs
Debug.Print tblNm & " linked."
This code links a table, and assumes you have an ODBC DSN setup pointed to your SQL Server. If you don't need to link the tables, you could also just connect to the SQL Server database and then use the tables via recordsets as if they were linked.