Link to home
Start Free TrialLog in
Avatar of AZZA-KHAMEES
AZZA-KHAMEESFlag for Bahrain

asked on

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

Hi experts
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

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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Are you sure the login credentials you're using for the DSNless connections have sufficient permission to update the tables included in the recordset?
Avatar of AZZA-KHAMEES

ASKER

yes i am using the same credential used in DSN connection when i created the ODBC before
Can you show the query you're using to create the recordset?
do you mean the query that create the DSN-less ?

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=sqlserver;Database=DB1;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
      
End Sub

Open in new window

No, the query that is not updateable.

Do you have Primary Keys on all your SQL Tables? Failing to do this can cause them to be non-updateable.
i think one table dose not have a primary key, do i need to specify the primary key?
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial