Access linked table - login without saving passwor

Question

I have an Access database which I am moving to an Azure SQL server back-end.

I want the users to be able to use a custom form to set the details used for the linked tables.

Currently I am using an AttachDSNLessTable to re-create the linked table with a users credentials however this saves the details to the Access file which isn't very secure. I thought about removing the linked tables during the DB close but if the task is killed the connection details will remain.

Is there a way I can specify a username and password for a linked table only for the current session?

Answer 1

If you are performing your re-linking from code, then you should be able to prevent the password from being saved by NOT specifying the dbAttachSavePWD attribute of the TableDef object when you create the linked table. To facilitate this, you also should always delete the SQL linked table each time before re-linking it to make sure that your options are respected, and that Microsoft Access updates the schema for the linked table.

Here is some sample code that will delete a linked table and re-link it, showing the dbAttachSavePWD attribute usage:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConn As String
Dim AzureTableName as String
Dim AccessTableName as String

Set db = CurrentDb()
AzureTableName = "dbo.YourTable"
AccessTableName = "YourTable"
strConn = "ODBC;Driver={SQL Server};Server=YOURSERVER\SQLINSTANCE;Database=MYDATABASE;Trusted_Connection=No;UID=MyUserName;PWD=MyPassword"
db.TableDefs.Refresh
For Each tdf In db.TableDefs
    If tdf.Name = AccessTableName Then
        db.TableDefs.Delete tdf.Name
        Exit For
    End If
Next tdf
Set tdf = db.CreateTableDef(AccessTableName)

'===============================
'If you want the password to be saved, include the following 3 lines of code
'to specify the dbAttachSavePWD attribute of the TableDef being created
'If you don't want to save the password, you would omit these 3 lines of code
'===============================
If InStr(strConn, "PWD=") Then
    tdf.Attributes = dbAttachSavePWD
End If

tdf.SourceTableName = AzureTableName
tdf.Connect = strConn
db.TableDefs.Append tdf

When you omit the dbAttachSavePWD attribute, the table is linked and available during this session. When you close the Microsoft Access database and re-open it (prior to running any re-linking code), the table appears to still be linked (shows up in the database window), but if you try to access it, you will receive an error, preventing access to the data without proper re-linking.