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
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
-
If you're importing from an .MDB file, use the Jet 4.0, not the ACCDB nightmare. The Jet one actually works.
-
pietlinden (9/22/2015)
If you're importing from an .MDB file, use the Jet 4.0, not the ACCDB nightmare. The Jet one actually works.IIRC, I don't believe that JET will work on 64 bit boxes.
Also, no one needs to build a linked server for this using the ACE drivers. You can use OPENQUERY or OPENROWSET.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just wondering, where are the 64-bit ACE drivers available? I seem to have the 32-bit ones, and SQL Server is throwing a fit. (I suppose I could do an end around and push from Access to SQL Server, but that's really annoying.)
-
pietlinden (9/22/2015)
Just wondering, where are the 64-bit ACE drivers available? I seem to have the 32-bit ones, and SQL Server is throwing a fit. (I suppose I could do an end around and push from Access to SQL Server, but that's really annoying.)Here's the link for the download.
http://www.microsoft.com/en-us/download/details.aspx?id=13255
I'll be back in a few with the installation instructions because the ones they give you are pretty bad... especially if you have ANYTHING installed in the 32 bit mode. DO NOT uninstall any of your 32 bit applications. It's just not necessary.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... once you've downloaded from the URL that I gave in my previous post, you need to following these (below) instructions instead of the ones that are posted on the URL...
[font="Arial Black"]INSTALLATION INSTRUCTIONS[/font]
The Website you downloaded from has a set of instructions that will work but ONLY if there are no 32 bit applications on your machine. DO NOT UNLOAD YOUR APPLICATIONS! IT’S NOT NECESSARY!!!
Instead, find where you the downloaded the AccessDatabaseEngine_x64.exe file to, open a DOS window with the "RUN AS ADMIN" option, change to that directory, type the following command, and press enter.
AccessDatabaseEngine_x64.exe /quiet
The command above will install the driver/provider whether or not any 32 bit programs exist on the server.
Be sure to allow any changes when prompted. You should have administrator privs on the box to do this installation.
You’ll need to restart SSMS (not the server) for the changes to take effect.
Once that's done, Open the Object Explorer and drill down to find it.
If it’s not there, then it didn’t install correctly. Try again.
[font="Arial Black"]CONFIGURATION INSTRUCTIONS[/font]
Once that's done, then you need to configure the provider by running the following code.
--===== Configure the "ACE" Drivers
USE [master]
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
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'SqlServerLIKE', 1
GO
I'll be back with some usage examples that I used at work a couple of weeks ago.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's what I used to read from an ACCESS table using the ACE drivers. This will also read from an ACCESS View or Stored Query... no linked server required. "Admin" is the ACCESS password. Use a linked server if you don't want to or shouldn't expose it. My run was a "one off" that only I would be running as the DBA. Obviously, you need to change the file information, the name of the "table", and possibly the password. The reason why you were getting the authentication error is likely because you didn't provide the ACCESS password. If you don't know the password, try 'Admin'.
SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'd:\path\filename.mdb';
'password';'',
TableName
) acc
;
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Okay, THANKS. Got part of it working... at least the linked server shows up... I can't query it, but I can see it. =) (About as much fun as the cookie jar that you can't reach!)
Here's all the code I ran...
sp_configure 'show advanced options', 1;RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
SELECT TOP 5 *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'C:\Users\Pieter\Desktop\ColliePowerPivot\BookData.accdb';
'';'',
DimDate
) acc
When I run the query, I get:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
.
I'm kinda waiting for the sirens to go off... "Access Denied!! SQL Knucklehead detected!"
Uncle! I give for now.
Pieter
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply