January 5, 2006 at 1:13 am
Hi All,
i do have a the data in the microsoft access database that needs to be accessed on the daily basis from microsoft sql server.
Could you please let me know if there is any code that could be written to connect to the microsoft access database from the sql server query analyser
regards
suresh
January 5, 2006 at 1:20 am
Suresh, there are three approaches:
1) preferably, move the data into SQL Server, and link the tables back into Access. You can even rename the linked tables in Access back to the original name, so most of the queries and code should not be affected.
2) or, write a DTS package to copy the data from Access into SQL Server on some regular basis. DTS has a connection object for MS Access.
3) create a linked server in SQL Server to a JET datasource which is your MS Access db; you can then directly query the Access data from SQL Server, using either OPENQUERY or the 4-part naming convention.
Allow me to encourage the 1st approach and highly discourage the 3rd approach. The linked server will be tricky to setup, will perform slowly, and be highly fragile (if you move the Access db, or rename it, the link won't work - and it may have issues with getting to the data if you already have the Access db open and don't have it set up in multi-user mode).
HTH
-A
January 6, 2006 at 7:28 am
To connect to it from within Query Analyzer, have a look at OPENROWSET. If this will be a permanent need, also look into using a linked server.
January 16, 2006 at 8:18 pm
I 've used below query from query analyser, its working fine.
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply