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

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.

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.

Hi,

You could try the SQL import wizard from SSMS.

HTH
  David

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?

Export to a csv file. Import the csv to sql.

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.

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?

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