2
I have a weird problem going on at work.
People have been using MS Access to connect to SQL Server db and lately people are getting sporadic problems with connecting to the servers. It's not always the same users and it's not always a problem, which makes it a real pain to try to solve.
One example of a related problem.
A person has a linked table to a table and she would filter the table or write a query on the table to return where itemsku = 'ABCD1234'. It would return one record but the ItemSku LMKN7486 and everytime it would return the wrong record but consistently the wrong record so itemsku abcd1234 always returned LMNK7486
One would think it might be a driver problem, but it also could be a user problem. Just posting the question to see if anyone else has had similar senerios.
Thanks
Dwight T
Posted 2010-04-08T19:58:16.897
Reputation: 173
When tables have fields added/renamed/removed or when a view is edited, the link in the front-end Access database has to be recreated. Refreshing the link is insufficient, and the results if you don't create a new link can be quite unreliable. – David W. Fenton – 2010-04-10T23:50:45.517
Don't believe this is the problem, but will look into it. – Dwight T – 2010-04-12T18:32:52.740
is the linked table also an access table? and what version of access? – datatoo – 2010-06-12T05:49:48.793
@DAvid W. Fenton: Why would refreshing the link be insufficient? @Dwight T:What kind of SQL-Server did you linked in? Which Access-Version do you use and is the access-db an *.accdb or an *.mdb? – Ice – 2010-06-12T18:43:36.920
It's insufficient because refreshing the connect string doesn't refresh all the metadata. Add columns to a linked view on the server, and they won't show up in the linked table unless you delete it and recreate it. Certain other changes on the server side can cause a linked table to become read-only, etc. This is SOP for ODBC linked tables and has been for as long as I've been using them. – David W. Fenton – 2010-06-12T20:10:16.077
@Dwight T the related issue you mention, is there any chance it is a caps-lock, num-lock issue. How is the query for the filter constructed? – datatoo – 2011-06-27T14:28:48.857