A97 DAO to A2K3 ADO

T

Terry

Some time back I had written an Access97 db to handle service calls. The
user now wishes to bring it up to spec with Access2003 and is suggesting
MS-SQL as the backend.

The current front end uses a tabbed form to enable users to drill down from
contracts, to sites, to calls received, and back the other way. The forms
also allow editing of information via command buttons New/Edit/Undo/Save
printing of labels etc. The number of networked users should not exceed 25.

Switching from A97 to A2k3 is not a problem for me, however the change from
DAO tables to SQL I am sure will bring about a steep learning curve.

Q1. Considering the number of networked users, is it worthwhile converting
to SQL tables on MS-SQL Server.
Q2. If SQL is adopted would there be a performance increase/decrease in
access speed.
Q3. If SQL tables were used, would the current method of displaying
information to the users be still possible (see above), There has to be some
'gotchas'.

Any thoughts please?

Regards
 
T

Terry

Great stuff Albert, thanks. I'll have a look at the links and then do some
familiarisation with SQL Server. Perhaps the Express 2005 version would be a
good start here. Nice hint about combo boxes linked to views, thanks for
that. I think I'll suggest moving upto A2K3 with DAO first prior to any move
to SQL, it will give me some time to try things out and in the meantime the
users will have the benefit of row-level locking and not the current
page-level locking.

Regards
Terry
 
A

Albert D. Kallal

Terry said:
Switching from A97 to A2k3 is not a problem for me, however the change
from DAO tables to SQL I am sure will bring about a steep learning curve.

Q1. Considering the number of networked users, is it worthwhile converting
to SQL tables on MS-SQL Server.

25 users is a good bunch. If your application performs well with 25 users,
then obviously you good a good design here, and you did a good job. We often
see users complaining that ms-access is too slow with 2 users! In other
words,
a typical case of the of the developers blaming the tools, and not their
designs. I suppose if the user is complaining of performance with 1 user,
then
how can one expect to get 25 users!

Hence, it would seem if you got 25 users..then you done something right!!

Q2. If SQL is adopted would there be a performance increase/decrease in
access speed.

Great question! In fact, we see weekly posts in the sql server newsgroup
about how an access application was changed to use sql server, and now it
runs
SLOWER!! This is just the same question of people who find ms-access too
slow with one user. SQL server is a heavy duty industrial corporate database
system capable of handling 100's, and even 1000's of users at the same time.
However, just like ms-access, if you don't utilize the engine correctly,
then
it will fall over, and perform slow with just ONE ms-access user!

Note that using assembler, VB6, VB.net, c++, or ms-access all retrieve data
from sql server at the SAME rate here. So, ms-access is NOT to be blamed for
poor performance when using it with sql server.
Q3. If SQL tables were used, would the current method of displaying
information to the users be still possible (see above), There has to be
some 'gotchas'.

Yes, ms-access makes a great front end to sql sever, and linked tables work
quite well.
If you link your tables to sql sever, then you will find about 90% or more
of your code and forms in ms-access will work. Note that you DO NOT need to
change your dao code, or convert existing code to ADO. There is NO
requirement
to do this, and in 99% of the cases, there is NO advantage to convert your
code to ADO. (and no difference in performance). So, general ado, or dao
code and
sql perform identical in 99% of the cases here. So, I am not sure where the
ADO stuff came up, but is certainly not a requirement, or even recommend for
you
to convert the existing code you have.

If I was writing a new application from scratch, then I certainly would
consider
adopting ADO as the development standard, as it does do a few things
cleaner then does DAO. (it is a newer object model..and was designed
somewhat better then DAO which is tied close to JET. I mean, for example,
you can do a update in ADO, and then grab the autonumber primary key
just created, in DAO, you losse the position and have to fetch it via the
lastmodified bookmark. However these are MINOR issues.
There has to be some 'gotchas'.

Sure, there is a few, but it is not that bad!! First, you need some
familiarization
and understanding of sql server. You need some comfort levels, and just like
in ms-access over time you find out what works, and what does not.
For example, opening a form to a large table without restrictions can really
case a huge hit on the server side. And, it can case a HUGE number of
records to be transferred to the ms-access form. On the other hand, I don't
think it takes any kind of rocket science to realize that this is a horrible
design approach, and is even a horrible idea in ms-access when you are
not even using sql server. You mean people actually open up a form that
bound to a table with 50,000 records..and you THEN let the users have at it?
This horrible in ms-access,and even worse when you have ms-access connecting
to sql server. (the recommend approach here is to always ask the user first
what customer id, or whatever, and THEN open a form that uses the "where"
clause to restrict the records loaded to the form to the record(s) that the
user needs.

There are number of other areas that can also hurt performance. For example,
any kind of combo box on a form, and ESPECIALLY any comb box that has a
relation join in it needs to be replaced with a view on sql server, and then
you
link to that view.

You can read up on some stuff here:

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


http://support.microsoft.com/default.aspx?scid=kb;en-us;175619&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Cente

The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.

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

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top