asked on

Importing data from Access to Sql Server 2008

I have an Access database from Access 2010. I know this creates .accdb files, NOT .mdb files. I am trying to import data from the access database into sql server 2008 R2. I installed this:

Microsoft Office 12.0 Access Database Engine Provider

Problem is, it doesn't work. If I select it in the import wizard, it errors out and sql server closes. There were no problems during the installation problem, and there was a message saying the installation had completed successfully.

I did try rebooting the computer and trying it again. Nothing has worked.

How can I import new format access data into sql server?

Thanks,

Cipriano

Andrew

Here are a couple of articles that might help:

http://databases.about.com/od/sqlserver/ht/importing_data_with_SSIS.htm

http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/importing/Import_Access_DB.asp

HTH
Andy

RasmusReinholdt

you might also try to install the "Microsoft Access Database Engine 2010 Redistributable" from http://www.microsoft.com/download/en/details.aspx?id=13255, please note there is a version for 32 bit and another for 64bit.

/Rasmus

cipriano555

ASKER

@Andy: Those links describe exactly what I did. The software I downloaded did not work can caused the import wizard to abort.

@Rasmus: I am running 64 bit OS, so maybe the thing I installed was for 32bit. I tried the link you gave, installed the 64bit application. Result was I didn't see the driver in the import wizard list.

Amazing that SQL Server 2008 supports neither access 2007 nor xls! Someone is asleep at the wheel.

RasmusReinholdt

sql2008 support access 2007 and excel, I'am using with out any problems using the dirver I linked to and SSIS. cant get the linked server to work though. How are you connection the sql server to the access db?

/Rasmus

cipriano555

ASKER

I can only import from old version of excel and access. I did create an SSIS project, but couldn't use it because Access was not in the list of data sources. Oracle was there...SQL Server was there...old Excel was there...Flat files...but alas, I can't get data from the latest Microsoft Office products, which is what I need.

cipriano555

ASKER

Here are some screen shots.
Access-01.JPG
Access-02.jpg

cipriano555

ASKER

I am assuming the driver I installed was not installed correctly or it is the wrong driver

ASKER CERTIFIED SOLUTION

RasmusReinholdt

membership

This solution is only available to members.

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

Start Free Trial

cipriano555

ASKER

So as I have learned, the real answer is, the Microsoft driver simply does not work properly. At this time you cannot connect to an Access 2007 data source with Sql Server 2008 64 bit. Evidently the 32 bit driver works, but I don't have a 32 bit environment and so I just have to export the data into flat files and go from there.

cipriano555

ASKER

Correction to my comment above.

Take a look at these files which I have found in investingating this issue:

AccessDatabaseEngine.exe
AccessDatabaseEngine_x64.exe
AccessDatabaseEngine2007.exe

Turns out that running the last file, AccessDatabaseEngine2007.exe does fix the problem. The other two do not.