Copying a table from Access to SQL Server

Posted on 2013-06-01
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!
Question by:rick_danger
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2

Expert Comment

by:Ross Turner
ID: 39212689
You can try

Or you can just use access up scaling tool just to convert the one table

Expert Comment

by:Ross Turner
ID: 39212690
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39212693
If this is a one-time migration you can use the SQL Server Migration Assistant to move your table:

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.
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


Author Comment

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

Author Closing Comment

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

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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.

733 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