Tuesday, April 2, 2013

DSNless Access Table with Primary Key Index

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:
SourceObjectLocalObjectServerDatabaseKeyField
tableXYdbo_tableXYSVRNameDBName(null)
viewXYdbo_viewXYSVRNameDBNameviewkeyid

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