Joinning SQL Server table with *.mdb file

Question

I'm trying to joinning SQLServer 2008 R2 tables with msaccess table (*.mdb). I already tried "OPENDATASOURCE" and "Linked Server", but no one of them is work correctly.

in example, I've got the following message:

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.".

the other error message:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MDBTest" returned message "The Microsoft Jet database engine cannot open the file '\10.55.56.34\Shared Folder\LBUS.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

and many more :D

can anyone give the working tutorial? thanks in advance.. :)

Answer 1

The easiest way is to do the join inside ms-access.

Set up a table link in your access database that references the sql-server table you want to join.
Then build a query in access that joins that table with one or more tables in the access database.

If you want to join more than one sql-server table, first create a view in sql-server that combines all the relevant tables. Then set up your table link to reference the view.

If, for some reason, you must do the join inside SQL server, you will have to use a different technique, or use the table link feature to "push" data from the access table to a (previously defined) sql server table. Then, it's just an ordinary join.