Solved

Copying a table from Access to SQL Server

Posted on 2013-06-01
6
306 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
Comment Utility
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
Comment Utility
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:rick_danger
Comment Utility
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 84
Comment Utility
The Upsizing Wizard is well known to have issues ... SSMA seems to be more reliable and robust.
0
 

Author Closing Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now