CODEWITHSUNDEEP
ms-accessvbams-access-2007dsn
Noora No
Noora No

Reputation: 309

Run-time error '3326' This Recordset is not updateable

i have an application that was created using Access 2007, its working perfectly with DSN, i update the application to use DSN-less connection to SQL-Server, i didn't change any code in the application, but when i run the application i am getting this error

Run-time error '3326' This Recordset is not updateable

Sub DnsLessLinkTable()

    Dim td As TableDef
    Dim stConnect As String
    Dim strTablesName(0 To 7) As String
    strTablesName(0) = "dbo_Directorate"
    strTablesName(1) = "dbo_Nationality"
    strTablesName(2) = "dbo_personal"
    strTablesName(3) = "dbo_Qualification"
    strTablesName(4) = "dbo_Qualimain"
    strTablesName(5) = "dbo_Qualisec"
    strTablesName(6) = "dbo_Section"
    strTablesName(7) = "dbo_Trips"

    For Each td In CurrentDb.TableDefs
       For Each TableName In strTablesName
           If td.Name = TableName Then
              CurrentDb.TableDefs.Delete TableName
           End If
       Next
   Next

   stConnect = "ODBC;Driver={SQL Server};Server=ServerNametest;Database=DBName;Uid=user;Pwd=password;"
   For Each TableName In strTablesName
       Dim splitTarget As Variant
       splitTarget = Split(TableName, "_")
       Set td = CurrentDb.CreateTableDef(TableName, dbAttachSavePWD, splitTarget(1), stConnect)
       CurrentDb.TableDefs.Append td
       AttachDSNLessTable = True
   Next
   Err.Description
End Sub

the only table that dosnt have a primary key is [Personal] in SQL server and i linked it to [dbo_personal]

the DSN-less module was run successfully and updated all the linked table, i am really struggling with this application, note that before DSN-less connection i was using the same connection string on building the DSN

any suggestion that will help

Upvotes: 1

Views: 8166

Answers (1)

Christian Specht
Christian Specht

Reputation: 36421

Access is able to update linked tables only when they have a primary key.
When they don't have one, you'll get the "This Recordset is not updateable" error.

If the underlying SQL Server table has a primary key, Access will usually detect it and use it in the linked table as well.

If the table does not have a primary key on the server, or if it's a view and not a table, Access is not able to set a primary key by itself.

If you link a view manually per DSN, a window will pop up and ask you to select the columns which Access should consider as the primary key:

select unique record identifier

When you link the table DSN-less (i.e. per code) and you don't specify a primary key, Access will link the table without primary key.

So I suppose that the table you're talking about is either a view or it has no primary key.
When it was originally linked with DSN, someone selected the primary key in the popup window shown above.
When you re-linked it with your DSN-less code, you probably didn't set the primary key, and that's why you're getting the "This Recordset is not updateable" error now.

Solution: After linking the table with your DSN-less code, explicitly set the primary key via VBA code.

Upvotes: 1

Related Questions