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
  • 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.
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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Open Report with SQL Parameter 11 30
Update a text value in another table 10 40
Criteria for Date for DCount 4 26
SQL Query assistance 16 26
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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