Solved

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

Posted on 2014-01-14
9
428 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
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 35

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 35

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 35

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

839 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