ODBC Call Failed - Different scenario

Question

Hi guys,

I have an Access 2007 database with a SQL Server 2008 R2 backend. This app has been running fine for the past two years or so.

A few days ago, the customer called me to advise that they're getting an ODBC Call Failed error when they enter a new record into a continuous subform. The parent form contains a pre-existing record, and all they're trying to do is enter a new record in the subform. They do that by entering all the data and clicking the record selector (the pencil icon). I've also tried saving it by moving to a different subform record or to the parent form. So, whenever they try to save the record, the ODBC Call Failed error pops up.

The subform's RecordSource was a query which simply pointed to the table. I replaced the query name with the table name, but that didn't change anything. I tried changing the RecordSetType from Dynaset to Dynaset (Inconsistent Updates), but that did nothing. This subform contains no code whatsoever (and yes, it's linked correctly).

I also tried dropping and re-linking the table, with no effect.

Anyone got any ideas?

Graham R Seach

---

Regards, Graham R Seach Sydney, Australia

Answer 1

No recent changes to the table? Can you update the query outside of the form? Anything in the SQL Server logs?

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 2

Hi Doug,

No, no changes for about a year. I can add records using my copy of the database (which I restored from backup last night), I didn't think to try it on their instance. We're located in different states and although I can remote in to their server, I can't get to individual desktops.

While writing this response, I received a phone call. An interesting development just occurred.

Last night, I'd backed up the Production database and restored it to UAT. I changed the subform's RecordSource again, from Dynaset (Inconsistent Updates) --> Dynaset --> Dynaset (Inconsistent Updates), pointed it at the UAT database, and sent it to them for testing. They called a little while ago to say it worked!

OK, good, so I send them the same frontend version (but pointing at Production), and guess what - the phone call I just received said it failed!

From that I conclude that there's got to be something wrong with the table in Production, and that the act of backing up and restoring solves the problem. That would explain why I can't reproduce the problem at my end.

I have to wait until 4:30PM (Sydney time) before I can try backing up and restoring Production to itself. Hopefully, that'll magically fix it.

Thanks for your help Doug. I'll get back to you later this afternoon (my time).

---

Regards, Graham R Seach Sydney, Australia

Answer 3

OK, here it is. There was a corruption in the SQL Server table. Backing up and restoring fixed it.

Thanks Doug. :-)

---

Regards, Graham R Seach Sydney, Australia

Answer 4

Graham R Seach wrote:

OK, here it is. There was a corruption in the SQL Server table. Backing up and restoring fixed it.

Oh, now that's interesting.  Thanks.

Tony

---

Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/