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
happy 1001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

atherhCommented:
After searching on google here are your options .
I dont have Access and SQL on one machine. Thats why i could try it .
Let me know if any of it worked for you.

Insert into [TableName] select <Field Names> from OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source="<Local Path of MDB on Server or Network Path>";Jet OLEDB:Database Password=test;')...[TableName1]
WHERE something = 'something'

========================================================================


use dev

INSERT INTO OPENDATASOURCE(

 'Microsoft.ACE.OLEDB.12.0', 'Data Source="C:\Repository\Apps\Websites\DNN_Items\Shop\Catalook\Export\catimportitems.mdb"; User ID=Admin;Password='

 )...items 

SELECT  * FROM  Cat_ProductImport

=============================================================================
If you're willing to permit Ad Hoc Distributed Queries on your SQL Server, you could use OPENDATASOURCE to retrieve data from an MDB file.

SELECT * INTO dbo.TestAccess FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\server\share\somefolder\scratchpad.mdb"')...MyTable;
Or after creating the destination table, you might prefer:

INSERT INTO dbo.TestAccess 
SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\server\share\somefolder\scratchpad.mdb"')...MyTable;
In order to get those to run, I had to enable Ad Hoc Distributed Queries like this:

sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE WITH OVERRIDE;
GO

=========================================================================

sp_configure 'show advanced options',1   
reconfigure with override   
go   
sp_configure 'Ad Hoc Distributed Queries',1   
reconfigure with override   
go  


SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:Database Password=test;')...test;
GO


sp_configure 'show advanced options', 1 
reconfigure with override   
go


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

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what about in another way round? Try to use Access to linked to a SQL Server and get the table you want, and then when necessary, just saved that linked table into a physical table in your Access database.

do you think that make sense to you?

read this further on how to do this...

Import or link Access to SQL Server data
https://support.office.com/en-gb/article/Import-or-link-Access-to-SQL-Server-data-ea1b29be-8c57-465e-ac35-18e3225ed9d5

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
Hi,
You can also create insert statement, based on the records within Access Database, while the Insert statement is in Sql server syntax.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

HuaMin ChenProblem resolverCommented:
It means you have to scan all the tables, record by record, to generate the Insert statement, to Sql server.
Vitor MontalvãoMSSQL Senior EngineerCommented:
atherh, do you still need help with this question?
happy 1001Author Commented:
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.
atherhCommented:
@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 1001Author Commented:
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 1001Author Commented:
@ 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 1001Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.