CODEWITHSUNDEEP
sql-serverms-accessvbaodbcms-access-2007
Kambiz
Kambiz

Reputation: 321

How to use connection string in ODBC to create a table in Access permanently linked to a SQL source?

I have created a table in my Access front end application, and try to connect it to the back end database with the following subroutine:

Sub createFlowTable()
Dim db As Database
Dim tblDef As TableDef
Set db = CurrentDb
Set tblDef = db.CreateTableDef("myTable")
tblDef.Connect = "ODBC;DRIVER=SQL Server;SERVER=myServer; Trusted_Connection=No;UID=<myUID>;PWD=<myPWD>;APP=2007 Microsoft Office system;DATABASE=myDataBase;Network=DBMSSOCN;TABLE=dbo.myTable"
tblDef.SourceTableName = "mySourceTableName"
db.TableDefs.Append tblDef
End Sub

After I close the front end Access database, and upon reopening it, the table fails to open. Even though I have set the Trusted_Connection to "No" in my string, the table still tries to use the Windows Authentication. Also, when I open the table on design view, I see in front of "Description":

ODBC;DRIVER=SQL Server;Server=myServer;APP=2007 Microsoft Office System;DATABASE=myDatabase;Network=DBMSSOCN;Table=dbo.myTable

So obviously Access has not saved the UID and PWD, nor has it saved the instruction on setting the Trusted_Connection to "No".

I insist to get this done with the connection string, and using DSN will not work for the purpose of my application. Help would be greatly appreciated.

Upvotes: 1

Views: 838

Answers (1)

PhilS
PhilS

Reputation: 1661

You need to add the dbAttachSavePWD-Attribute to the created table to store your credentials with the linked table in Access.

Before appending the tabledef you should put this line of code:

tblDef.Attributes = (tblDef.Attributes Or dbAttachSavePWD)

Upvotes: 3

Related Questions