Importing Access 2010 tables to SQL Server 2008 R2

Question

I'm trying to import a series of Access 2010 tables to Sql Server 2008 R2.  The Access import drivers are for *.mdb (which if I recall was the file extension for Access when I was a kid, and don't recognize the .accdb file extention).  Similarly, the Excel driver is for Excel 2003.  Isn't there a driver and method to import directly to SQL 2008 from Access 2010?

SQL is installed on my server, but Access is not installed on the server.  When I copy the file onto the server and try and open it directly into SQL, I get a 'no editor installed' error.

I can't get the 'upsize' wizard to work becuase it won't open the connection to SQL, even though I enter the userid and password of the SQL DB owner.  I get the following error:

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

Connection failed:

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

I have to say I'm stumped.  The rest of the Office 2010 suite works really well together - perhaps I'm missing something very simple?

Thanks!

 

 

I guess I could export my tables as Excel 2003 and then import them using Integration Services, or install SQL Express on my laptop and 'upsize' to that instance, but

SQL State: ‘0100’

SQL Server Error: 11004

[Microsoft][ODBC SQL Server Dirver][TCP/IP Sockets]ConnectionOpen (Connect()).

Connection failed:

SQL State ‘08001’

SQL Server Error: 6

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specificied SQL server not found.

Answer 1

The ".accdb" file extension started with Office 2007 and is the Access Database file extension. (Word went from ".doc" to ".docx" and Access went from ".mdb" to ".accdb". It's that simple.)

If you have Access 2010 installed on your workstation, then you can connect to Access databases, including .accdb and .mdb files. But your server needs something as well. And since most system admins are loth to install Office on a server, you need something else. Fortunately there is: the Mocrosoft OLE DB drive for Office databases, or something like that. (If I was at work now I would give you the full name to google for, sorry). We use it to allow our servers to connect to and read the data from Excel ".xls" and ".xlsx" files. It's foot print is only about 10 or 20 meg.

---

Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Answer 2

Hi. In the host where u execute the package or in dev machine, you need the Office 2010 runtime if dont have office 2010 installed, and you can download from here --> http://www.microsoft.com/downloads/details.aspx?familyid=57a350cd-5250-4df6-bfd1-6ced700a6715

For use excel 2010 workbooks, also you must configure some extended properties

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties="Excel 12.0;HDR=YES";

(example from http://www.connectionstrings.com/excel)

Hope this help.

---

Víctor M. Sánchez García (ES) (BI) Hope this help. if this answer your question, please mark as it.

Answer 3

Thanks.

I've installed the package suggested, but now have a new problem:

TITLE: SQL Server Import and Export Wizard
------------------------------

The operation could not be completed.

------------------------------
ADDITIONAL INFORMATION:

ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

------------------------------
BUTTONS:

OK
-----------------------------

My server runs 64 bit, the Access Database is 32 Bit. 

1. I originally installed the Access 2007 runtime for 32 bit, but the ODBC driver wouldn't allow access to the Access 2010 file saved on the server. 

2. I then installed Access 2010 64 bit (after uninstalling the 32 bit version) and the Access file is now recognized, but I get the architecture error noted above

3. I've tried using the 32 bit ODBC manager with the 64 bit Access 2010 runtime installed, but there is no driver for Access shown.

4. I've tried creating ODBC access to the Access 2010 file, but without luck.

 

All I want to do is migrate Access 2010 32 bit tables to SQL 2008 R2 64 bit.  Surely there's something simpler I'm missing?

Thanks!

Answer 4

 

In MS-Access 2010,  click 'Database Tool' -> "SQL Servers"   -> Use Exisiting Database ->  Click Next  ... It should prompt you for a 'File Data Source' ... -> Click "New". follow the remaining steps to create a new File DSN and your database import should work. I tested it using MS Access 2010 (32-bit) and SQL Server 2008 (64-bit).

Your DSN file should be similar to the following:

[ODBC]
DRIVER=SQL Server
UID=abcuserid
DATABASE=testData
WSID=Machine1
APP=Microsoft Office 2010
Trusted_Connection=Yes
SERVER=Machine1\SQLEXPRESS
Description=DSN for testData on SQL Server

Thanks.

Answer 5

This worked perfectly.  I can't thank you enough!  This has greatly simplified my work.  Thank you!

Answer 6

Smooth.

Thanks as well.

Greetz, Frits

www.myfightclub.nl

 

Answer 7

Hello. Is very difficult get answers from an answered thread. Please open another one to answer you.

Regards.

---

Víctor M. Sánchez García (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky

Answer 8

Thank you, Great question, brilliant Answer ;)

Answer 9

Thanks a lot.

Answer 10

The answer from TechNerd2010 worked excellently, but for anyone following this path you'll get asked if you want to upsize Table Relationships using DRI or triggers.  To save you a search DRI is Declared Referential Integrity, which means that Access will create primary and foreign key relationships in the SQL Server database.

There's also a detailed guide to the Upsizing Wizard at http://office.microsoft.com/en-us/access-help/use-the-upsizing-wizard-HP005273009.aspx

Answer 11

As this thread was the first one I found on Google, I thought I would post another alternative.

I needed to create an import package in SQL Server so I needed to use the import tool. I just renamed the file that Access 2010 created from *.accdb to *.mdb, and SQL Server import wizard was able to open it and import the data.

Answer 12

If anyone is still looking for a solution, this worked for me

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

Answer 13

Same here, thanks.

Answer 14

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

It is great. I think Sp171976  got  the best answer.

Thanks a lot to Sp171976.

Answer 15

Thank you very much for such a nice help.

Answer 16

THANK YOU