Stand Alone App Gone Multi User

I created this application a long time ago (Office 95) and it was used by a
few users who were able to work through and with each other to search and add
info. Now the demand has increased to about 50 users needing information from
it.

When do you determine if SQL should be used over Access for your application?

Can you salvage any code, forms, queries or reports in the transition from
Access to SQL?

To help you determine if you need to use a different database engine, JET
(the database engine in Access) has these limits:

- If there will be more than a few dozen users.

- If you envision many millions of records in some tables

- If you need serious security

- If you need to use the database on a network with unreliable connections
(including WiFi, WAN, ...)

- If the database must run 24x7 (i.e. you cannot ever take it off-line to
make backups or perform maintenance.)

If none of those apply, you can use the tables in Access. 50 users is
significant, but a well-designed Access application will cope with that. If
many are just reading, not modifying data, it may cope with more.

If you move the back end to SQL Server, and keep the front end as Access,
you will probably need to modify the application significantly, as it is not
really designed as a client/server app. For example, it's quite common to
see an Access form bound to a table with 100k records, with half a dozen
subforms bound to large tables, with each one having several combo boxes.
This kind of thing works fine in Access with JET tables, but you won't find
it usable under SQL Server.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian" wrote in message
...
I created this application a long time ago (Office 95) and it was used by a
few users who were able to work through and with each other to search and
add
info. Now the demand has increased to about 50 users needing information
from
it.

When do you determine if SQL should be used over Access for your
application?

Can you salvage any code, forms, queries or reports in the transition from
Access to SQL?

Allen,

Where would I go to read about the different approach using SQL vs Access.
I've read a few short articles about this, but they did not explain much
other than it involves some work.

When I design a system, I would like to design it so it can be converted to
SQL with as least work as possible.


--
Dennis

Hmm. Others may be able to suggest resources for this.

From Microsoft:
http://support.microsoft.com/kb/241743
http://support.microsoft.com/kb/237980
http://www.microsoft.com/sql/solutio...s/default.mspx

From others:
http://www.jstreettech.com/cartgenie...rDownloads.asp
http://www.ssw.com.au/SSW/Database/D...DocsLinks.aspx

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dennis" wrote in message
...

Where would I go to read about the different approach using SQL vs Access.
I've read a few short articles about this, but they did not explain much
other than it involves some work.

When I design a system, I would like to design it so it can be converted
to
SQL with as least work as possible.

Allen,

Once again, I want to thank you for you assitance.

--
Dennis

"Allen Browne" wrote in
:

it's quite common to
see an Access form bound to a table with 100k records, with half a
dozen subforms bound to large tables, with each one having several
combo boxes. This kind of thing works fine in Access with JET
tables, but you won't find it usable under SQL Server.

I wouldn't say that works well with a Jet back end, to be honest. I
was avoiding designs like that long before I ever upsized to SQL
Server, simply because they performed incredibly poorly (as should
be obvious from the description).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

=?Utf-8?B?RGVubmlz?= wrote in
:

When I design a system, I would like to design it so it can be
converted to SQL with as least work as possible.

I think Allen overrates the difference between a well-designed
Access front end for use with a Jet back end and for use with SQL
Server. The same things that make a client/server app efficient will
also enhance the efficiency of an app using a Jet back end. The main
principle is:

Don't retrieve data until the users needs it.

That means:

1. never bind a form to a full table -- ask the user which
record/records to load and then write the recordsource on the fly,
with a custom WHERE clauses limiting the number of records to
exactly those that are needed.

2. don't populate subforms that aren't visible to the user -- if
you've got a tabbed form with subforms on different forms, don't
load the subforms until the tab is displayed. Alternatively, don't
assign a recordsource to the subforms until the tab is displayed.
The tab control's OnChange event can handle this easily. This is one
where you will likely compromise, as heavyweight subforms may take a
long time to populate, so you may want to take the performance hit
for those particular subforms in the OnCurrent event of the main
form instead of in the OnChange event of the tab, simply because it
may be better for your users to have the wait to load the data in a
different place.

3. limit all listboxes and combo boxes to reasonable numbers of
records -- there is no such thing as a user-friendly combo box with
10,000 records. In those cases, load the recordsource on the fly
after the user has typed 2 or 3 characters. This will mean that
there is no hit to retrieve the combo box data until the user
actually tries to *use* the combo box. There are some issues with
this, such as displaying previously-entered data, but that can be
worked around in the OnCurrent event of the form the combo box is on
by assigning the recordsource on-the-fly to display just the record
with the current value the combo box is bound to. This can also
cause issues with continuous forms, but my opinion is that
continuous forms should not be used for editing, so combo boxes
don't belong on them in the first place.

That's just three things that follow from the main principle. These
things will enhance the efficiency of an application regardless of
what the back end is, and I've used these principles for years in my
Access/Jet apps on the theory that I might one day upsize to SQL
Server. The end result is that whenever I *have* ended up upsizing
(and it's been surprisingly seldom, precisely because the apps
worked so well as designed with a Jet back end), it's taken very
little work to make things efficient with SQL Server -- that work
was already part of the underlying design of the app from the very
beginning.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/