The best way to import an access table into SQL Server database.
How to take an Access table and get it into SQL Server database.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question probably has been resolved
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.