Access linked SQL Server ADO error

[casey]

Hello,

I have an Access to SQL Server linked database with a VB front-end. It seems to be linked correctly and permissions seem to be set up fine. I am able to execute queries of all types, select, insert, update from vb. I am also able to run ADO code to update the recordset.

However, when I try to use the ADO rs.Addnew, I get an ODBC call failed message and the app bombs with an
error description - ODBC--call failed Err.number -2147467259

any ideas?

Thanks

 

[pdx_man]

Your recordset is not updateable. What is the source?

 

[casey]

Good point!

The code opens a table and updates a few fields and closes it. Then opens the same table and tries to AddNew or Update records and the application crashes with an ODBC failure. Is it possible that SQL Server-Access linked databases lock the tables after the recordset is closed with VB ADO? The recordsets seem to be locking even though I've closed them each time. Plus this code worked fine on just Access. Could this be another issue with the Access-SQL linked setup. There could be some differences? Other than that the tables are Accessible and Permissions granted.

i am still using the same or similiar tables each time so I can't rule out that they're not updateable. Even though I can update them a moment before. The next couple of lines there locked. I'll have to test that more thoroughly.

I've heard of other wierd problems faced with this setup that might be due to the memo/date fields between Access and SQL Server causing truncating and reformatting. Any other ideas?

 

[boblarson]

Are you linking SQL Server tables to Access or merely using ADO to connect at run time?

 

[casey]

I'm using linked (Access 2000-SQL Server 2000) connection. The code is written as if updating tables directly in Access, but they're linked to SQL. I've never used this type of setup. Not sure why there would be any differences bewteen the two. Most of the ado code works fine. It's just a times when it won't addnew or update and I have not yet worked out when it works and when it fails.

Are there settings in Access that can be changed that might help?

 

[casey]

This error was being caused by RI rules that were setup in SQl and not in access. Thanks to all who responded.