ODBC Error updating a recordset (rst.Update)

Question

ODBC Error updating a recordset (rst.Update)

jpserra3

Say Hey Kid

Points: 676
More actions
June 15, 2015 at 3:07 pm

#305124

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?

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

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

jpserra3 (6/15/2015)
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?

This probably ought to be posted in the MS Access forum, but let me ask a question. How can you be doing an "update" with a SELECT query ? You need to be very detailed about exactly what you're doing in Access so that it's clear as a bell and as detailed as a microscopic examination.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lowell SSC Guru Points: 323518 More actions · Answer 2

jpserra3 (6/15/2015)
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?

it's been a while since my ASP days , but i believe that error is multi step operation failed, right?

i seem to remember three situations that that can occur:, and i'd bet that it is rs.Movenext...assign a value or something...rs.Update.

make sure you are testing for EOF and BOF:

If (Rs.BOF and Rs.EOF) Then

Response.Write "<tr><td valign=""top"" align=""left"">No Results Were Returned.</td></tr>"

End If

you loaded a recordset, and it had no rows(need ot check rs.BOF and EOF

if you populated the recordset, and you refer to a member that doesn't exist, or the recordset was moved to the next pointer(which did not exist)

are you in a loop, and you called rs.MoveNext? and then called update? you'd get the error then too.

finally, if you call an object that did not exist all all, ie rsWrongName.Update, or rs.Item("WrongColumnName") = SomeValue

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jpserra3 Say Hey Kid Points: 676 More actions · Answer 3

Hi Steve, Hi Lowell, Thank you for responding.

Steve, I can re-post this in the access forum, but I was not sure if this may be a permissions error in SQL. Can a post in a forum be moved to another forum? The Select query I am referring to is what is populating the record-set to begin with to give it the structure.

Lowell, I ran this in debug mode and all of the values were populated correctly, so I know I am not at the EOF.

Thanks Again,

Joe

sgmunson SSC Guru Points: 110640 More actions · Answer 4

jpserra3 (6/17/2015)
Hi Steve, Hi Lowell, Thank you for responding.
Steve, I can re-post this in the access forum, but I was not sure if this may be a permissions error in SQL. Can a post in a forum be moved to another forum? The Select query I am referring to is what is populating the record-set to begin with to give it the structure.
Lowell, I ran this in debug mode and all of the values were populated correctly, so I know I am not at the EOF.
Thanks Again,
Joe

Then you may be running into a SELECT that touches a view without an index, and thus Access isn't going to be able to update it, no matter what you do to Access. Another possibility is that it's a permissions issue on SQL Server, in that you may not have the rights via that connectivity to update the underlying data. Not sure what else is left.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)