Access 2007 gives "ODBC--call failed" error message

[hARSH007]

I am running Access 2007 SP2 on my Windows 7 which is having SQL server 2008 as backend. It works fine when I try to link and relink SQL server 2008. However, the problem occurs when I try to link the table manager by relinking with "Always prompt for new location" option selected. I get an ODBC error message when I do so which says the call failed. There error message only gives me the option to click on OK to close the message window but there is no information about it. This happened since I have installed Office 2007 on my computer. This was not the case with my previous installation, which was Access 2003. Does new office suite is the culprit? Can I do something about it?

[MahaGuru]

"ODBC Call Failed" error occurs when you are attempting to run a query which has an outer join on a linked SQL Server table. Microsoft already know about this problem and in fact they have come up with the solution to the problem. They have already released a fix with the latest edition of the Jet Service Pack. All you have to do is install the latest Jet update to keep your Jet database in good working condition. Before going for the latest Jet database, check out the current service pack level. Go to Start, and then click Search. In the Search Results pane, click on All files and folders. In the All or part of the file name box, type Msjet40.dll, and then click Search. In the search result that is produced, right-click on the Msjet40.dll file (which must be located in the Windows\System32 folder or in the Windows\System folder) and then click on Properties. Click the Version tab, and then determine the current Jet 4.0 service pack level.

[!const]

There are couple of suggestions to your problem. How are you trying to relink? Are you selecting a lot of tables when you are selecting "Always prompt for new location" option? Do you see any changes when making necessary changes over here like selecting only one table instead of couple of them or vice verse? I said so because may be you have a some tables which is causing the trouble. Try to make a new blank Access database and try to link it to your SQL table. Can you notice the same error message? Also check out the same test with some other database on your SQL Server and see if the database produces this error? Also try the above test with some other computer to ensure it is not system related problem. Once you are confirmed about this not being the case then you can take your test further by viewing ODBC trace or Process Monitor Log to see if there is anything mentioned about the cause of the failure.

[hARSH007]

I have tried linking and relinking on another computer which is running Windows XP and never faced any problem. This happens only on the computer which is running Windows 7. I have also observed that the same thing works on Access 2003 on the same Windows 7 computer. So I think the problem lies with my Access 2007 installation. I think may be the problem lies in the installation package but since I am not sure about this I came here to ask you about any suggestions. Do you think I should reinstall Access 2007 on my computer or the Office 2007 suite itself completely? I am thinking of to install Access only because I never noticed any problem on other office suite package like Word, Powerpoint, Excel etc.

[Badrunath]

This kind of error message is quiet normal and expected. This happens if any instance fails resulting into closing of all connections. It is similar to shutting down your server and restarting it once again. In spite of this, your ODBC test created a new connection and it is back to normal in working condition. But Access is unaware about this and could not even know how to restart once the connection is lost. This is the main reason why it fails. If possible, then you can definitely customize your code by including error handling and reconnecting to the database once this kind of problem occurs. You can even mention how it can detect this problem and how to deal with situations. You can even take care of if you want the user to know about this problem or else let him be transparent to this situation.

[CodGuru]

Which ODBC driver and version are you using? Did you tried out a different ODBC driver in your DSN to ensure if that fixes the issue. This connection error may even occur if you are missing any service pack that you need to install. Check out the official site and see if there is something that you need to install which supports Windows 7. Yes operating system place an important role over here and you should take special care about it. Also make sure you have the proper permissions for the tables on the server. Then check out the server connection timeout that is set.