Linked Table Question 1

[Ray1127]

I have an Access Database with a couple of local tables and a couple of linked SQL Server tables. There is a field in the local tables that link to the SQL Server tables. the issue is that because user Access to SQL Server is generic I cannot use a trusted connection. There is 1 user account in SQL Server that all users are using to connect. The issue is that since I can't use a trusted connection the user's must type in the username and password for the linked tables. I'm trying to create the code in the autoexec so that the link is refreshed when the Access DB is opened but I'm not sure how to refresh the link in code. Any help is appreciated.

 

[FancyPrairie]

When you link to the SQL tables, there is a box in the bottom right corner of the Link Tables dialog box that you can check that says "Save password". Simply check it when you link the tables and you won't get prompted for user name and password after that.

But you will need to delete your linked tables and then relink them.

 

[jsteph]

Ray,
To do this in code, you'll need to store the names of the tables you wish to relink in a local access table.

Then loop through the table, use the code below as a guide (assume all variables are dim'd, etc):

Do Until rsTB.EOF
    On Error Resume Next 'ignore error below if table already deleted
    DoCmd.DeleteObject acTable, rsTB!tblname 'delete old link
    On Error GoTo MyHandler 'go back to error handler
    Set td = db.CreateTableDef(rsTB!tblname) 'Name of Acceess td    
    td.Connect = strMyDSNString 'Whatever your DSN string is
    td.Attributes = 131072 'dbAttachSavePWD
    td.SourceTableName = rsTB!tblname 'This doesn't neccessarily need to match td.name
    db.TableDefs.Append td
    rsTB.MoveNext
Loop

You can name the linked tabledef differently than the source table in sql server, but you need to have that in another field in the table if you wish to do that.
--Jim

 

[Ray1127]

Thanks JSteph but for what I needed this time the tip from FancyPriairie was just what I needed.