[AccessD] Migrate to SQL Server

John,

I've done like you describe with just importing the tables, doing all
the fixing up of the tables, relationships, etc... afterwards.  Not fun,
especially with lots of tables, but that was back on Access 97 and SQL
2000.

Have you looked that the SQL Server Migration Assistant (SSMA) at
Microsoft?

http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.asp
x#Access


Here's an old article (July 2003) from Susan Harkins about using the
Upsizing Wizard in Access 2003, probably outdated for newer versions of
SQL Server

http://www.techrepublic.com/article/upsizing-an-existing-microsoft-acces
s-database/5035130


HTH

Rusty


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, December 23, 2011 9:35 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Migrate to SQL Server

I am beginning the long hard task of migrating a client from Access data
stores (many many) to SQL Server.  This is my client, the data is mostly
mine etc.  The client is on board that we do this and in fact is
investing in the server, OS and SQL Server software to do this.

My problem is that while I have used SQL Server a ton over the last few
years it has not been in the normal parent / child / grandchild, enforce
referential integrity, enforce uniqueness and all that jazz.  So I need
to learn some stuff like how to enforce unique values in a column.

I also need to discover how to migrate data from Access to SQL Server.
The data migration wizard in SQL Server is actually quite good however
AFAICT it does not pull relationships in, in fact it does not even
capture the fact that the PK is an autonumber and PK.  It also seems to
default to nvarchar whereas I prefer varchar.

Thus importing data using that wizard does work but it is pretty labor
intensive fixing up the identity and setting the PK to a PK, editing
mappings and so forth.

I would like to start a thread on this aspect of moving to SQL Server.
What has been your experience in this data migration, what tools have
you used, what gotchas have you run into etc.

Thanks,

--
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************