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 -
http://www.codeproject.com/Articles/21351/Import-Data-to-SQL-Server-from-Excel-or-Access-usi
Here is the code that I have tried -
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')
Thanks
http://www.codeproject.com/Articles/21351/Import-Data-to-SQL-Server-from-Excel-or-Access-usi
Here is the code that I have tried -
Code:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = 'MSAccessConnect')
BEGIN
EXEC sp_addlinkedserver 'MSAccessConnect',
'Access 97',
'Microsoft.ACE.OLEDB.12.0',
'C:\SQL Project\TestDB1001.mdb' -- put here your datasource path
END
GO
SELECT * FROM OPENQUERY(MSAccessConnect, 'SELECT * FROM [Table1001]') -- put table name here
go
sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override
GO
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')
Thanks