We help IT Professionals succeed at work.

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

481 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!
Comment
Watch Question

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

You could try the SQL import wizard from SSMS.

HTH
  David

Author

Commented:
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?

Commented:
Export to a csv file. Import the csv to sql.
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

Commented:
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?
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.