Copying a table from Access to SQL Server

I have a table in MS Access which I need to move to SQL Server. The main problem, as I see it, is that the table has 193 columns and about 9,000 rows. Through Access, I have generated an XML file containing just one row, and I can generate an Excel file, but not sure how to get this into SQL Server.  Obviously, the solution I am looking for will have to be able to upload all 9,000 rows.

Help please!
dsi-master.xml
dsi-master.xsd
rick_dangerAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If this is a one-time migration you can use the SQL Server Migration Assistant to move your table:

http://www.microsoft.com/en-us/download/details.aspx?id=28763

If you need to do this on an ongoing basis you'd need to develop code to handle this. The simplest way to do that would be link the SQL Server table you would like to receive your data, and then delete the contents and reinsert:

Currentproject.Connection.Execute "DELETE * FROM YourLinkedTable"
CurrentProject.Connection.Execute "INSERT INTO YourLinkedTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM YourLocalAccessTable"

If you don't want to remove the existing contents, then don't run the DELETE statement.
0
 
Ross TurnerManagement Information Support AnalystCommented:
You can try
http://www.microsoft.com/en-us/download/details.aspx?id=28763

Or you can just use access up scaling tool just to convert the one table
0
 
Ross TurnerManagement Information Support AnalystCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rick_dangerAuthor Commented:
I'm just trying out the SSMA - I'll report back if it does the job.

The problem with using the Access Upsizing Wizard is that it keeps encountering problems, maybe to do with connectivity.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The Upsizing Wizard is well known to have issues ... SSMA seems to be more reliable and robust.
0
 
rick_dangerAuthor Commented:
I'd never heard of the SSMA, but it worked, and it migrated the entire table in a couple of minutes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.