Jet >SQL conversion

[Kevin]

I've been asked to convert an app with a 20mb front end which uses DAO
and a Jet backend to an SQL 2000 backend. I've also just finished
reading Rick Dobson's 700-page Programming Access 2003, which addresses
this in exquisite detail.

Several "friends" had told me earlier how easy it was to migrate from a
Jet backend to an SQL backend.... "just use the upsize wizard.."

Uuh, so far I ain't seein' this.

Can anyone point to papers which show how it's not as hard as Dobson
makes it sound, or how to do something to at least semi-automate a DAO
to ADO code conversion, which sounds like it's necessary? (App will
have max 20 users, running on Terminal Server.)

Thanks,

Kevin

 

[Albert D. Kallal]

Can anyone point to papers which show how it's not as hard as Dobson

makes it sound, or how to do something to at least semi-automate a DAO
to ADO code conversion, which sounds like it's necessary? (App will
have max 20 users, running on Terminal Server.)

Why, or where did you hear that you must dump the dao code? I mean, dao is
fine for 100 or 200 users, and in most cases will differ VERY little from
ado in terms of performance anyway. There is certainly some benefits to ado,
but certainly not for a system with just such few users (50 or so).

In fact, about 90% or more of your dao code will run as is if you used odbc
linked tables. Why re-write all this stuff when you don't have to?

In fact, the only dao code I had to change is that code which assumes/grabs
a autonumber id BEFORE the update occurs. So, I now code everything as:

dim rstRec as DAO.RecordSet
dim lngNewID as long
set rstRec = currentdb.OpenRecordSet("yourtable")

rstRec.AddNew
rstRec!City = "Edmonton"
lngNewID = rstRec!ID
rstRec.Update
rstRec.Close
set RstRec = Nothing

At this point, lngNewID is set to the last id created.

When using sql server, you have to force the update, so, all my code (which
works both for JET, and sql server) is now:

rstRec.AddNew
rstRec!City = "Edmonton"
rstRec.Update
rstRec.BookMark = rstRec.LastUpdated
lngNewID = rstRec!ID

So, some code needs to be changed with dao..but not a lot.

a few more things that effecft conversion:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and tables are small....in the 50 to 75k records range.

So, with my 5 users..I see no reason why I can't scale to 15 users with
such small tables in the 75,000 record range.

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

However, Sql server is a robust and more scalable product then is JET.
And, security,
backup and host of other reasons make sql server a good choice.

However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records, or 20 users.

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...much applies when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server applications):

http://www.attcanada.net/~kallal.msn/Search/index.html

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

So, if your current designs are good, and don't load forms direct to a huge
table without some restrictions, then MUCH less work will be required to
make the application work with sql server.


** Don't use quires that require more then one linked table

(this ONLY applies to odbc to sql server...you CAN and are FREE to do this
with a mdb JET file share..and also with ADP projects to sql server).

When you use
ODBC, one table could be on the corporate server, and the other ODBC might
be a FoxPro table link 3 computers from the left of you. As a result..JET
has a real difficult time joining these tables together..and JET can not
assume that the two tables are on the same box..and thus have the "box" join
the tables. Thus,while jet does it best..these types of joins can often be
real slow. The simple solution in these cases is to change the query to
view..and link to that. This is the least amount of work, and means the
joins occur on the server side. This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or two combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view (JUST USE the view name...the sort, and any sql need to
be in the view and NOT on ms-access side).
The result is quite good combo box load performance. (and
again, not very much work. There are other approaches that can even speed
this up more..but we have to balanced the benefits VS. the amount of work
and coding. I don't think one should re-code all combo boxes to a call back
with a pass-through reocrdset..but that can be a solution also).

If you are using a ADP access project, the above points about the joins
with more then one table does NOT apply..since all queries execute
on the sql server side. (perhaps you could consider converting the
application to a ADP project. It would at least force you to make
most sql run on the server side. However, ODBC is just fine
and is usually EQUAL in performance if you do things right).

And, converting to a adp project is MORE work. So, for
existing, it best to keep the application as is.

** Of course, if you do have sql with more then one table..then pass-though
is the best if using odbc. (again..this does NOT apply to a mdb JET file
share).

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice,a and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100's of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

*** Make sure a timestamp field is exposed to ms-access. This fixes a lot of
update problems (especially with
sub forms). Ms-access uses the timestamp field to determine if the record
was, or needs updating. I think
it is obviously that also a primary id (autonumber (identity)) be exposed
also.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducing the bandwidth
(amount of data) goes hand in hand. So, better applications
treat the users well..and run faster! (this is good news!).

It also seems that good designs also convert MUCH more easily to sql server
then do bad ones.

 

[Kevin]

Oh, thank you Albert! I was reacting to Rick Dobson's statement on page
1 paragraph 1 of Programming Access 2003: "While DAO can still perform a
few selected tasks for the Jet engine that are impossible or very
difficult to do with ADO, accessing data from sources other than Jet
using DAO can be very awkward or impossible depending on the context."
Other statements reinforce this.

Thanks for your other comments... a lot of good ideas there. Many of my
forms are unbound with only combo boxes which prompt the user for the
record desired, and the "main" form is really a subform of that one with
the recordset populated based on the combo boxes, so I appreciate the
"one record" approach. But the tip about combo boxes is interesting:
many of my forms have a dozen or so combo boxes on them, and some load
pretty slowly, even with a one-record recordset. I wonder if populating
the rowsource OnEnter into the combobox control and dropping it OnExit
would speed things up for those that don't use dataless keys?

Thanks also for the tip about rstRec.BookMark = rstRec.LastUpdated
before grabbing the autonumber ID. That would have bit me big time!

With the exception of the few forms which load slowly, as above,
performance hasn't been an issue, and this app can have several hundred
thousand records in a number of related tables. It's the occasional
compact and repair which pops up that's more of a reason to move to SQL.

I'm afraid Mr. Dobson gave me quite a scare... thanks for ameliorating it!

Kevin

 

[Kevin]

Oh, thank you Albert! I was reacting to Rick Dobson's statement on page
1 paragraph 1 of Programming Access 2003: "While DAO can still perform a
few selected tasks for the Jet engine that are impossible or very
difficult to do with ADO, accessing data from sources other than Jet
using DAO can be very awkward or impossible depending on the context."
Other statements reinforce this.

Thanks for your other comments... a lot of good ideas there. Many of my
forms are unbound with only combo boxes which prompt the user for the
record desired, and the "main" form is really a subform of that one with
the recordset populated based on the combo boxes, so I appreciate the
"one record" approach. But the tip about combo boxes is interesting:
many of my forms have a dozen or so combo boxes on them, and some load
pretty slowly, even with a one-record recordset. I wonder if populating
the rowsource OnEnter into the combobox control and dropping it OnExit
would speed things up for those that don't use dataless keys?

Thanks also for the tip about rstRec.BookMark = rstRec.LastUpdated
before grabbing the autonumber ID. That would have bit me big time!

With the exception of the few forms which load slowly, as above,
performance hasn't been an issue, and this app can have several hundred
thousand records in a number of related tables. It's the occasional
compact and repair which pops up that's more of a reason to move to SQL.

I'm afraid Mr. Dobson gave me quite a scare... thanks for ameliorating it!

Kevin

 

[Albert D. Kallal]

Oh, thank you Albert! I was reacting to Rick Dobson's statement on page
1 paragraph 1 of Programming Access 2003: "While DAO can still perform a
few selected tasks for the Jet engine that are impossible or very
difficult to do with ADO, accessing data from sources other than Jet
using DAO can be very awkward or impossible depending on the context."
Other statements reinforce this.

The above is actually true to a point. You can for example build a dao
connect object (this is called jet direct), but ado is more suited to this.
The same applies to ado, and building, or executing stored procedures on the
server side (ado is again somewhat better).

However, for existing stuff, and most stuff, dao is ok. I mean, sure...if
you start a new application, then I certainly would suggest using ado when
working with sql server, but really, linked dao tables work just fine.

But the tip about combo boxes is interesting:
many of my forms have a dozen or so combo boxes on them, and some load
pretty slowly, even with a one-record recordset. I wonder if populating
the rowsource OnEnter into the combobox control and dropping it OnExit
would speed things up for those that don't use dataless keys?

You certanly can consier the above. As metnoend, after just a few combo
boxes, the form will start to load slow. However, if you read carauflay, not
only do I mention to create a vew, but ALSO to make sure you do NOT use sql
in the combo box souce, but JUST the view you created on the server side.
This approach speeds things up quite a bit (saves the sql load and
translation). So, building a view and useing that for the combo souce does
make things more snappy. This means you can get away with more combo boxes
and not having to write a lot of code. The fastest way to solve the combo
box load stuff is to use a call back function, but then we are talking more
work.

With the exception of the few forms which load slowly, as above,
performance hasn't been an issue, and this app can have several hundred
thousand records in a number of related tables. It's the occasional
compact and repair which pops up that's more of a reason to move to SQL.

I'm afraid Mr. Dobson gave me quite a scare... thanks for ameliorating it!

You are correct that upsizing to sql is some work, but the secret is to get
things working, and the attach each performance bottle neck one by one. And,
of course from a reliability and security point of view...sql server is
certainly a good path to go.

 

[Joe Fallon]

I wonder if populating the rowsource OnEnter into the combobox control and
dropping it OnExit
would speed things up for those that don't use dataless keys?

I do this often.
It is a nice trick.

Albert's detailed message was "spot on".
I am very much in agreement with it.

The simplest thing to do is simply convert all the tables to SQL Server and
then re-link your app to those tables instead of to Jet tables. Depending on
your code, etc. you may not have to change a thing (to get it running.)

Then you can begin optimizing it so it takes advantage of the power of SQL
Server.
Whether you use SPs or Views or...

Also, consider getting Mary Chipman's book on SQL Server and Access.
Especailly if you are new to SQL Server.
It is very highly regarded.