I recently rewrote an old ACCESS 97 application to work with MSSQL 7.
The program works fine until I use a form whiche opens several linked tables at the same time. When closing the Form I get (At unpredictable moments) an ODBC SQL SERVER DRIVER update failure error message.
All rights are set on the tables for Public and even then i keep getting this error at very odd moments. I also had a look in Access at the ODBC refresh rate which is set at 10sec , OLEDB refreshrate 10 sec, and refresh is set at 5sec.
I tried different combinations of timeout settings and even renewing the MDAC to version 2.7.
Can anyone give me a tip as to look for the possible source of the error and how to fix it?
When a form makes use of only one or two tables, i never get any ODBC timeout errors. Reports (And therefore queries) that frequently make use of several tables at once never create any trouble...weird isn't it?
What's even weirder is that the ODBC error refers to a linked table which is rarely updated (tblAddresses). When checked in SQL all the rights on that table are the same as all the other tables.
I tried once more to change refreshrates in Access etc. but it doesn't seem to help. I forgot to mention Access97 is running on a Windows 2000 workstation...but I don't know if that's relevant.
In any case thanks for your reply... If you know of any more concrete tips i'll welcome it with open arms.