Pages

Oct 14, 2009

How to read a MS Access file from SQL SERVER using OPENDATASOURCE

Reading an MS Access database from SQL SERVER is possible and quite simple.

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source=\\server1\C\TEMP\orderDB.mdb')...[orders]



Here the above SQL Query uses OPENDATASOURCE method to read the MS Access file .
 
Note:
1. 'Orders' is the name of the table in MS Access mdb file.
2. The 3 dots (...) should be there before specifiying the table name.
3. “Ad hoc distributed queries” has to be enabled to used the OPENDATASOURCE method.

No comments: