Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkServer".

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server " TestLinkServer" returned message "Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.".

After reading my linked server’s article in SQL Server 2005 at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

http://www.sqlhub.com/2009/03/linked-server-is-not-configured-for.html

One of my subordinate wanted to use it in one of our small application. He started using the same code I gave in one of my above article about Linked Server but he was facing above described error. He tried everything and every possible solution from internet but didn’t find his luck. Finally he told me to look at the error and try to solve it. At the very first glance I found problem with his OLE DB adapter. He was using the MDB file which has been developed in ACCESS 97 but there was ACCESS 2007 in our SQL’s development server. As MDB was old, he was using “Microsoft.Jet.OLEDB.4.0” but he should use " Microsoft.ACE.OLEDB.12.0” as our development server has ACCESS 2007 and it doesn’t understand old provider.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com