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