0

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.. :)

stkertix
  • 81
  • 1
  • 10

1 Answers1

0

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • While that is true it doesn't really answer the question, which is how to link a Jet/ACE database from within SQL Server. Like @stkertix I have tried to do that several different ways, under SQL 2008 R2 and 2012, and have never gotten it to work. I'll be interested to see if anyone has. – Gord Thompson Apr 23 '13 at 13:36
  • All he's asking for is how to join the two tables. He isn't explicitly saying the join has to be done inside SQL server. The original question might need an edit. – Walter Mitty Apr 23 '13 at 14:57
  • Well, he *did* mention the "OPENDATASOURCE" and "Linked Server" methods, which are SQL Server features.... – Gord Thompson Apr 23 '13 at 15:15
  • OK, I added a para to deal with the criterion that the join must be done over in SQL server land. I have never have had good luck with getting the JET engine to act like a server to another DBMS acting as client. – Walter Mitty Apr 23 '13 at 15:34
  • Hi, thanks for all of your answers. so, it's getting so hard to let it done haha :D – stkertix Apr 24 '13 at 08:22