Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Copying a table from Access to SQL Server

Posted on 2013-06-01
6
Medium Priority
?
364 Views
Last Modified: 2013-06-02
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
0
Comment
Question by:rick_danger
  • 2
  • 2
  • 2
6 Comments
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39212689
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
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39212690
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39212693
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:rick_danger
ID: 39212750
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
 
LVL 85
ID: 39212778
The Upsizing Wizard is well known to have issues ... SSMA seems to be more reliable and robust.
0
 

Author Closing Comment

by:rick_danger
ID: 39214144
I'd never heard of the SSMA, but it worked, and it migrated the entire table in a couple of minutes.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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