Upsizing Wizard Nightmare

[JM]

I'm using Access 2003 to attempt an upsize to SQL Server 2000. The
Upsizing Wizard is giving me tons of problems with error messages like:

1. "Object is invalid. Extended properties not permitted on . . ."

2. "No primary or candidate keys in referenced table . . . that match
referencing column list in foreign key . . . Could not create
constraint.

3. "Incorrect syntax near . . . " (When trying to upsize queries.)

So, I'm led to believe that Microsoft cannot understand its own Access
table relationships or query syntax enough to translate it to another
one of its products, SQL Server.

This is going to be very time consuming to debug. All I really want to
do is get the back-end tables into SQL Server for security and
continuity reasons. What's the best way to do this that will allow me
to use the existing Access queries as-is? I'll guess I'll have to
insert the triggers by hand.

Thanks in advance,

JM

 

[Kevin3NF]

Use SQL Server's DTS to import the tables.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

www.experts-exchange.com - experts compete for points to answer your
questions

 

[Philipp Stiefel]

Use SQL Server's DTS to import the tables.

While this will help to transfer the basic structure and
the data to SQL-Server, one will lose any extended properties
like Indexes, Defaults, Constraints etc. of the tables.

Cheers
Phil

 

[Rick Brandt]

While this will help to transfer the basic structure and
the data to SQL-Server, one will lose any extended properties
like Indexes, Defaults, Constraints etc. of the tables.

As with all wizards, the upsize wizard simplifies some things but in doing
so it makes some assumptions for you and often those assumptions are wrong.
I never recommend its use. Better is to build the table structures that YOU
want on the server and then move the data into them.

A big move like changing to a server back end is not supposed to be a simple
10 minute process. Microsoft't attempt to make is into one is misguided
(IMO).

 

[Bas Cost Budde]

On my site, under Code Modules, you find SQL tool. It converts your
table structure into SQL DDL commands. I believe (long time since I
uploaded this) you can have INSERT commands for all your data as well.

 

[Philipp Stiefel]

As with all wizards, the upsize wizard simplifies some things but in doing
so it makes some assumptions for you and often those assumptions are wrong.
I never recommend its use. Better is to build the table structures that YOU
want on the server and then move the data into them.

A big move like changing to a server back end is not supposed to be a simple
10 minute process. Microsoft't attempt to make is into one is misguided
(IMO).

I mainly agree with you, but still I think the Upsizing Wizard is
a tremendous help when moving an Access-Backend to SQL-Server.
I do not mean that the wizard will do the work for you but it can
help you get the work done.

I done a lot of upsizing to SQL-Server and the approach I deem
quite effective for most applications is the following:

- Use the Upsizing Wizard (the one that comes with AccessXP is
useable) to create an inital version of you SQL-Server-Database.

- Use the "Generate SQL Script"-Feature of Enterprise Manager to
create a complete script of that DB.

- Drop the database created by the Upsizing Wizard!

- Chop the SQL-script created by Enterprise Manager in manageable
chunks (plain tables / single table constraints + indizes / foreign
key constraints, etc) and edit these Chunks manually until they
match your expectations/requirements.

- Create a SQL-Server-Database containing the basic tables using
your scripts. (no constraints yet!)

- Import the data from Access to your SQL-Database using DTS.

- Run all the other scripts to complete your database.

This approach definitely works for me!

Cheers
Phil

 

[Tony Toews]

I believe (long time since I
uploaded this) you can have INSERT commands for all your data as well.

Yup, I've done this in the past as well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

I'm using Access 2003 to attempt an upsize to SQL Server 2000. The
Upsizing Wizard is giving me tons of problems with error messages like:

Do you have Office 2003 SP1 installed?

This is going to be very time consuming to debug. All I really want to
do is get the back-end tables into SQL Server for security and
continuity reasons. What's the best way to do this that will allow me
to use the existing Access queries as-is? I'll guess I'll have to
insert the triggers by hand.

Nothing on your specific problems but for some alternative methods of
moving data across see my Random Thoughts on SQL Server Upsizing from
Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm for some over
all
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm