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
cipriano555Asked:
Who is Participating?
 
RasmusReinholdtCommented:
SSIS uses 32bit in development and 64bit in p´roduction, so if you only installed the 64bit driver it will not show up in BIDS, try installing the 32 bit driver as well, you should then be able to choose it in BIDS.

/Rasmus
0
 
RasmusReinholdtCommented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cipriano555Author Commented:
@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.
0
 
RasmusReinholdtCommented:
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
0
 
cipriano555Author Commented:
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.  
0
 
cipriano555Author Commented:
Here are some screen shots.
Access-01.JPG
Access-02.jpg
0
 
cipriano555Author Commented:
I am assuming the driver I installed was not installed correctly or it is the wrong driver
0
 
cipriano555Author Commented:
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.

0
 
cipriano555Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.