Access Upsize Wizard to SQL Server

[Guest]

Currently our access database is split, front and backend and I'm trying to get some direction on how to migrate it to SQL Server 2000. It's in Access 2003 in Access 2000 format. My instinct is to recreate the tables in SQL Server than ODBC connect them to the access front end, kinda just doing a swap of the backend from access to SQL Server and keeping my front end in tact, just adjusting for ODBC.

I'm doing some tests now and decided to try the upsize wizard. I tried on the backend, it migrated most tables but not all. Anyone have a reason as to why not all of them were migrated, especially my maine table enforcing referential integrity?

Second question, it creates duplicates of the table, I'm guessing access does this so updates and deletes are done faster? First in the local table then migrate to SQL? Correct me if I'm wrong.

Then I attempted to bring the database together, one MDB and use the upsize wizard.

Any comments on the most beneficial way of upsizing? Is my first instinct correct, or should I replace my existing queries in access with stored procedures?

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

Currently our access database is split, front and backend and I'm trying

to get some direction on how to migrate it to SQL Server 2000. It's in
Access 2003 in Access 2000 format. My instinct is to recreate the tables in
SQL Server than ODBC connect them to the access front end, kinda just doing
a swap of the backend from access to SQL Server and keeping my front end in
tact, just adjusting for ODBC.Not necessary & it takes too much time for a large database. Use the Upsize
Wizard.

I'm doing some tests now and decided to try the upsize wizard. I tried on

the backend, it migrated most tables but not all. Anyone have a reason as
to why not all of them were migrated, especially my maine table enforcing
referential integrity?Something went wrong in the upsizing process for you. When I did (A2K2
software with A2K0 file format), all Tables were migrated correctly. For
the main Table, the problem may be related to MSKB article:

http://support.microsoft.com/?id=295231

After the upsize, you generally need to use the EM to check & to modify the
SQL Server Tables a bit more, e.g. changing nvarchar to varchar if you don't
want Unicode, changing Index names (Upsizing creates weird Index names),
some relationships, etc ... (see at end for more info.)

Second question, it creates duplicates of the table, I'm guessing access

does this so updates and deletes are done faster? First in the local table
then migrate to SQL? Correct me if I'm wrong.Definitely something wrong! This is not what I observed when I upsized the
database in my current project.

Then I attempted to bring the database together, one MDB and use the upsize wizard.

Any comments on the most beneficial way of upsizing? Is my first instinct

correct, or should I replace my existing queries in access with stored
procedures?There are a few things you can do:

1. Get Mary Chipman's Microsoft Access Developer's Guide to SQL Server

http://www.amazon.com/exec/obidos/ASIN/0672319446

and read it thoroughly.

2. See Tony Toews' article:

http://www.granite.ab.ca/access/sqlserverupsizing.htm

3. Go to Microsoft site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;KBHOWTO

select Access 2003 and search for "Upsize Wizard". You will find a lot of
articles that may help you.

 

[david epsom dot com dot au]

Second question, it creates duplicates of the table, I'm guessing access
does this so updates and deletes are done faster? First in the local table
then migrate to SQL? Correct me if I'm wrong.

That's an option in the wizard isn't it? Something like 'keep local tables'
??????

I'm doing some tests now and decided to try the upsize wizard. I tried on

the backend, it migrated most tables but not all. Anyone have a reason as
to why not all of them were migrated, especially my maine table enforcing
referential integrity?

If you haven't done this before, you could start with the
upsizing page at Tony's site:
http://www.granite.ab.ca/access/sqlserverupsizing.htm

And at least have a look at the free download version of
http://www.ssw.com.au/ssw/UpsizingPRO/Default.aspx

(david)

Currently our access database is split, front and backend and I'm trying

to get some direction on how to migrate it to SQL Server 2000. It's in
Access 2003 in Access 2000 format. My instinct is to recreate the tables in
SQL Server than ODBC connect them to the access front end, kinda just doing
a swap of the backend from access to SQL Server and keeping my front end in
tact, just adjusting for ODBC.

I'm doing some tests now and decided to try the upsize wizard. I tried on

the backend, it migrated most tables but not all. Anyone have a reason as
to why not all of them were migrated, especially my maine table enforcing
referential integrity?

Second question, it creates duplicates of the table, I'm guessing access

does this so updates and deletes are done faster? First in the local table
then migrate to SQL? Correct me if I'm wrong.

Then I attempted to bring the database together, one MDB and use the upsize wizard.

Any comments on the most beneficial way of upsizing? Is my first instinct

correct, or should I replace my existing queries in access with stored
procedures?