Upsizing large Access table to SQL Server

[S N]

I am using SQL Server 2005 Express and want to upsize large tables from MS
Access to SQL Server.
The upsizing wizard is skipping the large tables without any error.
The QueryTimeout value in Registry was also changed to 0 to avoid the
problem in Upsizing wizard, but the problem still remains and the large
tables are being skipped by the upsizing wizard.
Kindly advise on how to export/upsize large Access tables to SQL Server.

[Erland Sommarskog]

I have no experience of Access, and thus not of the upsizing wizard.

But what I have learnt from Mary Chipman who has been an MVP of both
products (she is now with Microsoft), is that you should not use the
wizard at all. There are so big differences between Access and SQL Server
that you cannot use a wizard and get a good result.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Paul Shapiro]

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D96E651...@127.0.0.1...

You can use the wizard as a starting point, but tell it to only convert the
data structures and not move the data. Then you can make any
necessary/appropriate adjustments to the SQL data structures and then load
the data yourself. You can either use Access with a linked SQL Server table,
or use SQL Server Integration Services. Make sure you specify a primary key
for all the tables in SQL Server, because without one Access cannot copy
data to SQL Server.

[Mary Chipman [MSFT]]

To add to what Erland and Paul said, you need look at the data itself.
By large table, do you mean number of rows or number of columns? There
isn't an exact mapping between Access/Jet data types and SQL Server --
see
http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html
for mapping info. Also, if you have datetime values in Access earlier
than 1753, then the wizard will not be able to convert that data (this
usually happens due to user input errors).

What I would recommend is to create the data structures in SQL Server,
and then import the data. You can use SSIS if you need extensive data
type conversions. If you don't want to go the manual route, check out
SSMA for Access --
http://www.microsoft.com/sqlserver/2005/en/us/migration-access-learning.aspx.
It does more than the Upsizing Wizard, but you need to have an idea of
where you want to go with the data ahead of time so that you select
the correct options.

--Mary

On Mon, 14 Jun 2010 00:11:05 +0530, "S N" <uand...@invalid.com>
wrote:

[S N]

But how do I install and run the SSMA for Access?
Where do I get the installation files for SSMA as indicated by you?
Please advise.

"Mary Chipman [MSFT]" <mc...@online.microsoft.com> wrote in message
news:qvac169qlrnbu1hdr...@4ax.com...

[Mary Chipman [MSFT]]

Go to http://www.microsoft.com/downloads/ and search on "SSMA Access"
and select the download that matches the version of SQL Server you are
running.

--Mary

On Mon, 14 Jun 2010 22:58:20 +0530, "S N" <uand...@invalid.com>