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
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you sure the login credentials you're using for the DSNless connections have sufficient permission to update the tables included in the recordset?
0
AZZA-KHAMEESAuthor Commented:
yes i am using the same credential used in DSN connection when i created the ODBC before
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the query you're using to create the recordset?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AZZA-KHAMEESAuthor Commented:
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

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
AZZA-KHAMEESAuthor Commented:
i think one table dose not have a primary key, do i need to specify the primary key?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, you must specify a Primary Key, preferably on the SQL Table. If you're not able to do that, you can create a unique index on the Access linked table side of things. Essentially, after linking your tables you'd run SQL like this:

Currentdb.Execute "CREATE UNIQUE INDEX YourIndexName ON YourTableName(YourFieldName) WITH DISALLOW NULL"

See here for more information: https://msdn.microsoft.com/en-us/library/bb177891%28v=office.12%29.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.