asked on

How to Import Data to SQL Server from Access Database using TSQL Script

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 -

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

Open in new window

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

SOLUTION

atherh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peter Chan

Hi,
You can also create insert statement, based on the records within Access Database, while the Insert statement is in Sql server syntax.

Peter Chan

It means you have to scan all the tables, record by record, to generate the Insert statement, to Sql server.

Vitor Montalvão

atherh, do you still need help with this question?

happy 1001

ASKER

Thanks guys for the suggestions.

And @ Victor, please stop using that line on my threads. If you want to share something then simply share it, otherwise pass on my thread. Please do not ask like this, on any of my threads in the future. Thanks a lot.

atherh

@Vitor Montalvão : I didn't ask this question Its happy 1001's question .

@happy 1001 : It wold be great if you let us know which one work for you .

Thanks,

happy 1001

ASKER

Since I am trying to import from .MDB file, therefor I am supposed to use MS Jet 4.0 OLEDB driver.

For example this code -

IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = 'MSAccessConnect')

 BEGIN

     EXEC sp_addlinkedserver 'MSAccessConnect',

        'Access 97',

        'Microsoft. Jet.OLEDB.4.0',

        'C:\SQL Project\TestDB1001.mdb' -- put here your datasource path

 END

 GO

Open in new window

This link explains the process by using 3 different methods -

http://bifuture.blogspot.in/2012/03/importing-data-from-access-into-sql.html

But I seem to be still making some error somewhere and not being able to get it to work !

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. Jet.OLEDB.4.0',

        'C:\SQL Project\TestDB1001.mdb' -- put here your datasource path

 END

 GO




SELECT * FROM OPENQUERY(MSAccessConnect, 'SELECT * FROM [Table1001] WHERE xdate  = "2015-9-16 00:00:00.000"')  -- put table name here

go
sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
GO

Open in new window

Thanks

happy 1001

ASKER

@ Ryan Chong

Thanks for the idea of LINKING.
But first I want to try the method of doing it with script. If I fail to do it this way, then I will definitely try the LINK method.

@atherh
Thanks for your comment. I checked the original article here -
http://stackoverflow.com/questions/3145270/select-from-access-database-file-and-insert-to-sql-database

but failed to get it to work.

@ Victor
Thanks for your comment.
And I repeat my request that please do not use that statement on any of my threads in the future. You are free to skip my threads. I have not seen any one else here to use that statement, but you use it quite often. I do not think that all other experts are idiots who simply share their opinions without using that kind of a statement every time before proposing a solution.
Please do not take it personally. It is just a request.

With my Best Regards to all.

happy 1001

ASKER

ok Victor. Thanks

Since I failed to get the SQL Script solution, so I decided to settle with the alternate method of linking SQL table with Access DB and then using the Append Query.

Thanks to all.