I was recently working on a project where I used MS Access 2007 to build a multi-user application with a SQL backend. The nature of the application required the linked SQL tables to be DSNless.
I attach my DSNless tables in code, which offers a few benefits. By having the objects to attach listed in an a local Access table, I can attach and delete the linked tables on demand without the user knowing, and I can easily port this application to a new SQL database without much work. More useful is the ability to ensure that each time the application is opened, the table schemas are refreshed and changes made to SQL tables and views are reflected in Access.
My list of attachable objects looks like this:
| SourceObject | LocalObject | Server | Database | KeyField |
| tableXY | dbo_tableXY | SVRName | DBName | (null) |
| viewXY | dbo_viewXY | SVRName | DBName | viewkeyid |
I use a function that I found online and have since adapted to do the extra stuff I needed.
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim idx As Index
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
The problem:
In Access when you link a table manually (with a DSN) you will be prompted to select the primary key field if Access cannot get the information from the source object. If there is a key field assigned in SQL, that information will carry over to Access. This is not the case for SQL views.
Why you need an index in Access:
You cannot update records in a linked SQL view/table without a primary key index setup in Access. Your Acess statusbar will say' Recordset not updateable.' This will be likely to happen if you are converting an Access application to a SQL backend.
Solution - creating an index in code:
Attaching SQL objects in code will not present a primary key selection prompt, so you'll need a way to create that index in code.
There are a lot of posts online about using the DAO object model to create and attach an index kind of like this:
Set tdf = currentDB.CreateTableDef("tablename") 'new table def
currentDB.TableDefs.Append tdf 'append to the db
Set idx = tdf.CreateIndex("PrimaryKey") 'new index
With idx
.Fields.Append .CreateField("ID")
.Primary = True
End With
tdf.Indexes.Append idx 'append index
This never worked for me. Tons of people swear that it works, but I could never get it to work. *Notice that the tableDef has to be created and attached to the currentDB before creating the index.
What works for me:
I added an optional variable to the function called "keyField" that brings in that value from my table, which is only populated for objects that do not already. have a primary key in SQL.
Add this to the function definition: Optional stKeyfield As String
Function AttachDSNLessTable(stLocalTableName As String,
stRemoteTableName As String, stServer As String, stDatabase As String,
Optional stUsername As String, Optional stPassword As String, Optional stKeyfield As String)
Access will automatically setup an index for objects with primary keys, so in my list of objects to attach (a table), I only assign a keyfield to items that are SQL views because trying to create an index on a table will report that an index already exists.
In the function I add this code after appending the tableDef to the currentDB:
If Len(stKeyfield) > 0 Then
CurrentDb.Execute "CREATE UNIQUE INDEX 'PrimaryKey' ON " & stLocalTableName & " (" & stKeyfield & ") WITH PRIMARY"
End If
This works fine for me. I get to pre-define which field will be my primary key and every time that object is attached, it also adds the index. The SQL view is updateable in SQL, and with that index it is also updateable in Access.
Final function:
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String, Optional stKeyfield As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim idx As Index
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
If Len(stKeyfield) > 0 Then
CurrentDb.Execute "CREATE UNIQUE INDEX 'PrimaryKey' ON " & stLocalTableName & " (" & stKeyfield & ") WITH PRIMARY"
End If
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
