Solved

The best way to import an access table into SQL Server database.

Posted on 2016-08-10
5
62 Views
Last Modified: 2016-08-30
How to take an  Access table and get it into SQL Server database.
0
Comment
Question by:Cad Coder
[X]
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
5 Comments
 
LVL 26

Assisted Solution

by:-MAS
-MAS earned 250 total points (awarded by participants)
ID: 41751677
You best tool is SSMA. https://www.microsoft.com/en-us/download/details.aspx?id=43690.
You should have the same table structure and Foreign key is a must.

If you have only one table to move you can export and import to SQL.
Export https://www.youtube.com/watch?v=ndnL7uzd8EE
Import https://msdn.microsoft.com/en-us/library/ms140052.aspx
0
 
LVL 58
ID: 41751881
Yes, SSMA is the way to go.

However one point is that by default, the option to add a timestamp field is false.   You want to drill down in the settings and set this option to true.

 Adding a timestamp field to your SQL tables turns on row versioning for the table.  This allows Access to work with the table more efficiently.   If Access sees this column, it will use that to help maintain it's cursors rather than trying to build a key in other ways.

Jim.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41752270
When you install SSMA, both the 64-bit and 32-bit versions get installed but only the 64-bit has a shortcut.  You need to find the 32-bit version and make a shortcut for that one since that is the version you will have to use if your Office installation is 32-bit.

The driver installed by default with Access is named SQL Server and it is at least 10 years old and doesn't recognize any file types added after then.  SSMA defaults dates to datetime2 which the old ODBC driver sees as text and so they don't operate as you would expect dates to operate.  To overcome this you have to either get and install the newer ODBC  Driver 11 for SQL Server (I had problems with SQL Server Native Client 11.0 so don't confuse them) or configure SSMA to convert dates as datetime which the old driver sees correctly.  Also watch out for bit fields.  Make sure they always have a default and tat all records are populated.  Access allows null in this data type and SQL Server doesn't.

And finally, the datetime data type in SQL Server supports a different date range than the datetime in Access so you have to make sure all your dates are in the range that SQL Server supports.

It was much easier when we could use the upsizing wizard but that was removed in A2013 so SSMA is what we have today.
0
 
LVL 4

Accepted Solution

by:
Daniel Jones earned 250 total points (awarded by participants)
ID: 41753351
You can also take the help of this link, it will shows you step by step method to migrate a MS Access table to SQL Server using SSIS (SQL Server Integration Services).
https://www.mssqltips.com/sqlservertip/2656/simple-step-by-step-process-to-import-ms-access-data-into-sql-server-using-ssis/  
0
 
LVL 4

Expert Comment

by:Daniel Jones
ID: 41776025
This question probably has been resolved
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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