Formerly working update query won't work after upgrade

[KHerm]

Hello,

I've had great help from you (plural) here in the past and hope there's a guru out there who can shed some light on this perplexing problem.

We run a very important process at each month end that requires an Access table to be updated with an update query. It has been working for almost a year in Access97 using SQLServer 6.0. The company for whom I consult recently upgraded to SQLServer 7.0 (it's possible the new version is 6.0 from an earlier one, I was not involved in the SQLServer update).

The update query works fine until the SQLServer tables and views are linked to the new version of SQLServer. We believe the problem is with the view (which is linked as a table). The update query itself joins an Access table, a SQLServer table, and a SQLServer view and updates a field in the Access table. We also believe there is something going on with the ODBC connection, but all we can confirm is that the query gives "You must use an updatable query" as soon as the tables are linked to the new version of SQLServer.

Does this problem ring a bell with anyone? This has caused quite a problem because there are quite a few updates that must be made that are not working now.

Thanks for any help!

Ken

 

[Guest_imported]

Based on the error: You must use an updateable query; I am led to believe the problem is with your primary keys. From my experience, in an update query, any join must be on a primary key in one of the tables. I.e. if you have two tables an are joining on CustomerID, CustomerID has to be the primary key in at least one of the two tables. This is because Access cannot perform an update when it is possible to have a many to many relationship.

Make sure all your joins are on at least one key field and you will likely meet with success.

-SnakeDog

 

[KHerm]

Well, I'll check to see if the new database has any new structures or changing of keys, but it is my understanding the database was converted as it was in the previous version of SQLServer.

I'm suspecting something else going because the query will not work when linking back to the original SQLServer database. In other words, if I take a copy of the Access database (which works on the original database) and relink the tables to the new SQLServer, the query fails. Then if I try to relink back to the original SQLServer the query still fails. But the query still works if I start from the Access database that was never relinked.

I'll let you know, too, if I find any changing of keys in the new database.

Thanks for your help!

Ken

 

[randusoleis]

There are issues with running SQL6.x and 7.0 at the same time from what I understand. Why have they not just upgraded all to 7.0? Randusoleis.....

 

[KHerm]

Message to SnakeDog. You pointed us in the right direction and we're up and running again. Big thanks!! Seems the person who was doing the linking didn't choose the primary keys during the relinking! <sigh>

Thanks again!

Ken