[AccessD] Migrate to SQL Server

John,

 For some great tips on using Access with SQL Server, download "Best of both
worlds" from here:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp


 Also not sure if it was you or someone else that posted a MSKB/MSDN link
that had tons on the deep internals of how Access uses a unique key with
table linking and how you could control it in Access.  I'll have to dig for
that one.

  I know it was posted to the list at one point (pretty sure I posted the
above link as well in the past, but it's worth a re-post).

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, December 23, 2011 10: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