Openrowset to pull data from Access database in SQL Server

Openrowset to pull data from excel in SQL Server:

Openrowset is a function which can be referenced after the "FROM" Clause of T-SQL.

This function is used to pull data from different databases by utlising the ODBC drivers installed in the particular windows system.

How to Pull the data from access db SQL Server :

If access db file is of old version, its extension would be (.mdb).

For the above type file:

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\dba_temp\DBA_Work\test1.mdb';'admin';'', SQLData)

If access db file is of old version, its extension would be (.accdb).

SELECT *FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'D:\dba_temp\DBA_Work\test1.accdb';'admin';'', SQLData)

Hope the above information helps

Thanks