I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link -
Here is the code that I have tried -
sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = 'MSAccessConnect')
EXEC sp_addlinkedserver 'MSAccessConnect',
'C:\SQL Project\TestDB1001.mdb' -- put here your datasource path
SELECT * FROM OPENQUERY(MSAccessConnect, 'SELECT * FROM [Table1001]') -- put table name here
sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override
The access database file path is - 'C:\SQL Project\TestDB1001.mdb'
The Table from which I want to import the data is - [Table1001]
but when I run this script, I get this error -
9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0"
for linked server "MSAccessConnect" reported an error. Authentication failed.
I am not being able to figure out, how to make it work.
Secondly I need to make 2 more changes to the code posted above.
1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')