Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
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 Apr 10 '10 at 23:50
    
Don't believe this is the problem, but will look into it. –  Dwight T Apr 12 '10 at 18:32
    
is the linked table also an access table? and what version of access? –  datatoo Jun 12 '10 at 5:49
    
@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 Jun 12 '10 at 18:43
    
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 Jun 12 '10 at 20:10
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.