ODBC Error updating a recordset (rst.Update)

  • I am getting an error (number -2147217887, error message "ODBC--call failed.") when I try to execute an rst.Update. I have an MS Access 2013 application using an ODBC connection to SQL Server 2008 r2. I am using a query to update a table. It is a simple Select query with no joins. I have checked to make sure the table can be updated.

    Can anyone help me here? I am posting it here because I was not sure if there were any special permissions that I need to set to allow a linked table to be updated in MS Access this way?

  • You shouldn't have an issue with that as long as the SQL Server User has security permissions that allow the updating of the table. One way to test that is to try to do an update to the table directly from the linked table in Access. (If this is a production system, be sure you can revert to whatever the specific record you update had previously.) If you aren't familiar with Security in SQL Server you may want to do some reading on the topic. We typically use Integrated Security so no login process is required at the SQL Server level, but if you aren't on a Windows domain, then you would need to configure SQL Server Security. Let us know how you fare.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • hi Wendell, thanks for responding. I am still having the issue with a table that is linked to SQL. I found that the error is because of a duplicate key so it is how I generate the key that is causing the issue. I generate the key by reading a table that only has 1 field in it called uniqueid. This is part of the existing data structures. If I make the table a local table it works fine. I even tried adding a identity field on the table so the uniqueid field could possibly be updated and not part of the key. no go. Ideally, we should use the identity field in the actual table so we do not need a second table to generate the key field, but this is code we do not want to change (at this point) as it is an application that is used by many and may end up generating duplicate keys if made a local table.

    Thanks,

    Joe

  • To resolve the issue if you have the constraint in Access, and then upsized it to SQL Server, you will have that some constraint in SQL Server, so you would need to remove it in SQL Server and then relink the table.

    But the bigger concern is the fact that you are trying to enter a record with a key that is a duplicate. Rather than using the table that has the single record, which can always go south with a failed update or other issues, we use the Max of the value in the table that actually contains the records. That way you can be almost certain that there will never be a duplicate. The only possible issue is when two or more people are adding records, and one person opens a record, then the second person opens a record and saves it. Meanwhile the first person is distracted by something and when they go to add the record they will get a failure with dup key. For that reason, we almost always use an AutoNumber field (Identity in SQL Server) as the primary key. You may not have much flexibility as it is an existing system, but I would certainly try to redesign the process so you avoid those kind of errors.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply