-- list of available providers
exec master..xp_enum_oledb_providers
-- Three ways to read in Access data (file: c:\db1.mdb, tabel: TEST)
1a) Using openrowset with older MS Access version:
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM TEST')
1b) For 2010 or more recent (*.accdb)
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM MAANDEN')
2) Using opendatasource:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\db1.mdb;')...[TEST]
3) Using a linked server called TEST for 2010 or more recent:
EXEC sp_addlinkedserver
@server= N'TEST',
@srvproduct = N'MSDASQL',
@provider= N'MSDASQL',
@provstr= N'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db1.mdb;Uid=;pwd=;'
-- Read in Excel (file: c:\employee.xls, worksheet: TEST)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\employee.xls;Uid=;pwd=;','SELECT * FROM [TEST$]')
-- Insert into text file
insert openrowset(
'Microsoft.ACE.OLEDB.12.0',
'Text;HDR=yes;Database=c:\tmp\',
'select * from test.csv')
(testname, testaddress)
select testname, testaddress
from vw_test;
Note:
- MS Access needs to be installed on the same server where SQL Server is installed
- Make sure that the use of ad hoc names is allowed:
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
Possible error messages and solutions:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"
Give read/write access to "everyone" on the server to the following folder:
C:\Users\<SQL Server Service Account>\AppData\Local\Temp
Make sure this is set on the server where the code is running, i.e. on the client PC where the code is executed within Management Studio.
