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?

[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.

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
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
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.

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
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

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
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
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 2008

From novice to tech pro — start learning today.