Solved

How can insert data in an 2010 Access database into a 2008 SQL database table

Posted on 2014-01-14
9
433 Views
Last Modified: 2014-05-07
I need to take data from a 2010 Access database (that has 149,004 recrods/rows) and insert/import that into a 2008 SQL Database table.
I am not a DBA I do not have any experince in doing this. Please help!
0
Comment
Question by:newjeep19
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 7

Expert Comment

by:Steve
ID: 39780093
There is an "Upsize" feature in Access 2010 under database tools on the ribbon that will either create a new SQL Server DB or import to an existing one provided you have the credentials.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39780102
You can use the upsizing wizard which is pretty simple or you can use the SSMA (SQL Server Migration Assistant) tool which is more complicated and more flexible.  You can download SSMA from the Microsoft download site.  When you install it, two versions are actually installed if you are running 64-bit windows.  You'll need to find the 32-bit version and use that one if you are using a 32-bit version of Access.

If you don't know anything about SQL Server, I would start with the built in Wizard.  Choose the option to upsize the tables AND data and link the tables.

Keep in mind that simply converting the data to SQL Server may in fact cause your application to be slower than it was.  You may also need to make some FE changes to your forms or make some changes to your queries to take advantage of having a SQL Server BE.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39780985
Hi,

You could try the SQL import wizard from SSMS.

HTH
  David
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:newjeep19
ID: 39782630
Thank you for your posts. When I try to use the upsizing wizard I can't map to the SQL database table that I want to import the data to (from Access). When I try to use the SQL Import wizard I get an error message which basically tells me that the SSIS has suddenly failed to be able to access data sourced from Access. Is there a way to take the data from Access db and import it into SQL?
0
 
LVL 7

Expert Comment

by:Steve
ID: 39782646
Export to a csv file. Import the csv to sql.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39782865
When I try to use the SQL Import wizard I get an error message which basically tells me that the SSIS has suddenly failed to be able to access data sourced from Access. Is there a way to take the data from Access db and import it into SQL?
That is how you do it.

Are you using any of the data types introduced in A2007 such as multi-value fields?  If you are, they can't be upsized which is one of the many reasons experts recommend avoiding them.

If you aren't, then your database may be corrupted.  Try compact and repair, try decompile, try creating a new empty db and importing all objects.

Try downloading the SSMA tool and see if you have better luck with that.

If you still can't get it to work, delete all but a few rows of data (obfuscate anything sensitive), compact and upload here.
0
 

Author Comment

by:newjeep19
ID: 39786160
Ok could export the SQL Table then copy the data in the access database table then past it into the exportred table and then import that table back into SQL?
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39786339
I believe you can import from Excel so I would export to Excel from Access and then import from Excel in SQL Server.  Of course that means that if you can't get either of the upsizing methods to work, you will need to create the tables in SQL Server manually.  This tedious and error prone which is why people use the wizards.

I have a tool I got for a different project.  I had to convert FoxPro to ACE and the ODBC driver was giving me fits.  As it happens, this tool also includes SQL Server among the various from/to options.  I found it to be worth every penny.

http://www.spectralcore.com/fullconvert/?app=fc-ent&trial=0&from=helpweb
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 39799197
Hi,

SQL Server 2008 usually has no import driver for ACCDB files. If you want to import data from ACCDB files you must simply convert your ACCDB file into an MDB file which then can be imported using the SQL Server Management Studio "Import and Export Wizard" available with a right click on the database name under "Tasks" and "Import Data".
You must have the full version of SSMS, the wizard is not available in the SQL Server Express version.

Cheers,

Christian
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Ms Access 2010 Setup (Executable file) 4 67
SQL query 45 39
How to build a logic for passwords according to initials? 13 45
SQL Query 20 18
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question