Solved

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

Posted on 2014-01-14
9
423 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 7

Expert Comment

by:Steve
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
Hi,

You could try the SQL import wizard from SSMS.

HTH
  David
0
 

Author Comment

by:newjeep19
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Expert Comment

by:Steve
Comment Utility
Export to a csv file. Import the csv to sql.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now