upsizing wizard

[Sirocco]

I'm new at the adp thing, and trying the upsizing wizard, with the Northwind
database. I'm only upsizing 1 table with 1 column, and the column does not
have a SQL reserved name. The table is being skipped over or not upsizing.
I'm using the SQL 2000 Entrprise trial version.

I didn't install the SQL Server 2000 Service Pack 3a, nor the Access 2000
"Readiness Update" for SQL Server 2000. I wouldn't think these would be
that critical.

Maybe I should just install MSDE 1 instead of SQL 2000 Enterprise, since I
installed MSDE 1 at home and was able to upsize a few practice databases
without a problem.

Would appreciate any advice.

 

[Daran Johnson]

I began as an Access developer and began developing ADP/SQL Server setups a
few years ago. The first time I tried the wizard it did not upsize the
Access database very well. It turned my queries into views, skipped some
altogether and did not use stored procedures. It also turned all of my
character fields into nvar fields.

My conclusion is that unless the Access database is extremely simple (just
some tables), skip the wizard altogether. SQL Server is a completely
different system. You will want to take full advantage of stored procedures
and use views where appropriate and also incorporate views into your stored
procedures.

My advice is to do a full code review of your Access database and migrate to
SQL Server with the idea of taking advantage of what SQL Server has to
offer - recode to optimize for the new system. Also, get a good book on SQL
Server and use books on line (BOL) liberally.

Good Luck,

Daran

 

[Sirocco]

But the upsizing wizard is supposed to convert queries to views. And I'm
aware that only select queries are converted, and that Access words like IIF
aren't converted. And SQL uses different field types than Access. I know
that. I've realized, based on the extensive reading I've done, that the
Access 2000 upsizing wizard isn't really suited to upsize to SQL Server
2000, even though it does satisfactorily upsize (the tables) to MSDE 1.
I'm going to upgrade to Access 2002, which is *designed* to work with SQL
Server 2000.

Even with the upsizing wizard, there's MUCH work to be done both before and
after the upsizing. But you'd think the upsizing wizard would at least
work for a single table that has only one field, wouldn't you? I don't
think the problem is with the table. I'm going to try the solution I've
described, unless you can persuade me not to.

It seems your advice is to recreate all the tables in SQL from scratch, is
this correct?

Thanks for your advice.

 

[Kevin @ 3NF]

The best way I have found to move the table to SQL Server is to import them
using SQL Server DTS. I needed to re-create my invoices, change some
smalldatetime to datetime, and set a default for BIT fields, but it was
easier than re-creating the entire bunch. Also, you will need to re-create
your relationships, keys, etc.

 

[Sirocco]

Ah, that works (and better than using File/Import, which doesn't preserve
the "allow nulls" property) but the wizard, as *presented* in the tutorial
I'm using, seems great in that, with Access 2002 if not 2000, so much is
preserved, like default values and cascading updates and deletes and the
automatic creation of triggers, etc, which would otherwise have to be
created from scratch, which seems very undesireable. The wizard worked on
my home PC (Win 98SE, Access 2000, MSDE-1) as expected, why not here at
work?

It seems that the difficulty I'm having in getting the wizard to work is
caused by compatibility issues with NT, Access 2000 and SQL Server 2000
trial version, possibly even obscure security issues, and not the wizard
itself. I'm upgrading to Access 2002 and hopefully this problem will
dissappear. Am I being unreasonable?

Have you actually used or tried using the Access 2002 upsizing wizard?

Thanks!

 

[Kevin @ 3NF]

Have you actually used or tried using the Access 2002 upsizing wizard?

Yes...once. 20+ tables, some of them with dozens of fields....it failed, so
I just went SQL Server and did it from there...that's where I'm more
comfortable anyway...

 

[Sirocco]

Was the one time you did it with SQL Server 2000? I ask because it supports
"extended properties" of Access tables, so there's more to benefit from
using the upsizing wizard than with SQL 7, in which the additional
properties would have to be recreated manually whether or NOT the wizard was
used. There is now, more than say a year or so ago, more incentive to use
the wizard, esp if one's programming skills are at the novice level. That's
why I'm motivated to get it to work.

Can I hear from some people who've used the upsizing wizard with SQL Server
2000 and Access 2002 successfully?

Thanks!

 

[Kevin @ 3NF]

Yep...SQL 2000...less than a month ago.

 

[Sirocco]

But doesn't it bother you that the wizard failed? Wouldn't it have saved
you some work? There's so much documentation on the wizard, and for it not
to work would be very disappointing, I think. Some Access wizards don't
really save any time at all, but it seems this one would save a lot of time.

Thanks!

 

[Mark A. Sam]

Sirocco,

I have a couple hundred tables in an app and had erros on on a couple. I
suspected that my Order table had bad data, becuase the Autonumber jumped
from 5 digits to abut 10. I deleted all data from that year and prior and
the corresponding data in the orfer subtables, then all of my tables
tranported to sql smoothly.

This query issue is a bear however. With hundreds of select and actions
queries I doubt that I so more than simply keep my .mdb and link to the SQL
server, unless the speed waarants doing so.

God Bless,

Mark A. sam

 

[Sirocco]

Well, I know what the problem is with your Order table is: "Order" is a SQL
reserved word. Even if the table upsizes, the queries that refer to "Order"
will not. Not only did I have this experience in a practice run, but this
particular example, where Order is the name of a table, was given in one of
the books I was reading on this very issue. Change the name of the table
to "Orders", and all references to it.

In fact pages 726-27 in Special Edition using Access 2002, by Roger
Jennings, describes some precautions to take regarding tables before
upsizing. But the issues concerning ADP files in general are so vast,
including limitations on the use of Access language in queries, that simply
linking my mdb file to SQL tables may suffice.

 

[Mark A. Sam]

Thanks Sirocco.

The SQL data engine is MSDE. A good book on the subject is "Microsoft
Access Projects with Microsoft SQL Server", by Ralf Albrecht and Natascha
Nicol. Another book I'm reading is by a guy named Barker, with a green and
black cover, on Access 2002 but with much on adp files. The book by
Jennings tends to have a lot of unnecessary details and, I beleive is poorly
arranged, but does have a lot of information nonetheless.

Sirocco, The table name isn't Order, I was just referring to it an the
Order table. It is called [Order Entry Header], but good observation.

I agree with you on the vast issues of using an ADP file. One thing I found
out about an ADP is that table results are much quicker. I developed a [Task
Scheduler] for for my client which is slow in Access97, becuase of its
complexity, but it really slowed to a crawl with linked SQL Server tables.
I created an ADP to test the performance of this form in that enviroment.
It just flew. The performance was incredible. I did have to remove a few
columns in my query to get it to work, but don't think that would have any
effect. My problem though is this. For some reason the recordset is not
updatable, either in the ADP or the version with the Linked SQL Server
tables. That puzzles me. Actually it annoys me...lol, but I won't get into
that.

The language limitations also puzzle me. It doens't seem like it could be
difficult to allow the same functions in SQL or is it T-SQL, (Im not sure
what version views use or if there are two versions) as in Access SQL. The
code is there. I would think they could easily port it to the SQL Data
engine (whatever it is called...lol. I really am new at this).. Maybe

its

a
portability issue, so that the queries can be used in other systems.


God Bless,

Mark


a
SQL one
of couple.
I prior
and for
it

lot

of because
it with
SQL 2000, why
not wizard

Server

is will
need upsize *designed*
to

Server

is advantage Also,
get column,
and