Van,
Thank you for your valuable information.

How does the change to SQL impact forms and reports? Will
code in the events of the form remain the same. (For
example an After Update event that contains DLookup, DSum,
DCount etc. that look at the backend and give information
to the end user through a MsgBox with options on how to
proceed.)

Thanks again,

Allan
-----Original Message-----
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


"AHopper" wrote in
message
...
I have a Access database split front end Mde and backend
mdb in a network environment, with about 30 workstations
that can run it using Access runtime. Most of these are
data entry at a low level of input.

The front end is presently 62.3 MB and the back end is
39.6 MB.
Recently, I had a conversation that left me with some
concerns. I have not had problems to this point but the
individual I was talking to said that I would likely
soon
start having difficulty because of the number of users
and
the size of the database.

Questions:
1. Is this true? Not true for a well-designed /
implemented database.

It depends on how well the database is designed and
implemented.

For users, Larry Linson regularly reports that he has JET
databases that run
well with 70 users. Personally, I look to move to SQL
Server at about 25-30
regular users (using the database 4 hours during a
normal working day).
However, I move to SQL Server because of the back-up
facilities on SQL
Server (full & incremental back-up as well as transaction
log / transaction
log back-up) rather than difficulties with JET Back-End.

OTOH, 2 or 3 users may be too much for a badly-designed
database!

See next for size.



2. What size is critical? same as 1

The biggest JET Back-End I have had is about 130 MB
(compacted) and I didn't
have any problem. However, my intention is to upsize to
SQL Server at
about 250-300 MB, simply because I think if it is that
big storing valid
data, it is important enough to have proper database /
transaction log
back-up procedure.



3. What steps should I take to protect myself from
trouble?

Regularly compact and back up the Back-End. Back up
daily if you can.
(IMHO, back-up / BU procedure is a weak point for
Access / JET)




SQL
Recently we have installed a SQL Server for another
database we use. I originally chose Access knowing that
it
could be moved to SQL. I am in the process of learning
SQL. I have had someone in the Microsoft training field
indicate that changing to SQL is not necessary and is or
can be complicated.

Questions:
1. Is it difficult to change the database to SQL?

Personally, I didn't find it is difficult to upsize from
JET to SQL Server.
Tony Toews has some info on his Web site:

http://www.granite.ab.ca

I think there are some white papaers on Microsoft Web
site also.




2. Should I keep Access as the front end with the SQL
backend?

That's what I did. Use Access database (MDB / MDE) Front-
End with
ODBC-linked Tables from SQL Server, NOT ADP.





3. Will the change require a lot of programming?
(Someone
suggested that front end design should be done in Visual
Basic.)?

Some mods required as SQL Server Identity Field behave
differently from JET
AutoNumber Field. Also be careful of SQL Server BIT
Field vs JET Boolean
Field. If you use Recordsets, you will need to modify
some arguments when
opening a Recordset (dbSeeChanges option, etc ...).

If you are presently use DAO, you may want to rewrite
code to use ADO when
time permitted (DAO works fine but some experts recommend
ADO for
efficiency).

Visual Basic: only if you have 2 or 3 months to waste!



4. How can I prepare for this move?

Grab Mary Chipman's "Microsoft Access Developer's Guide
to SQL Server"

http://www.amazon.com/exec/obidos/ASIN/0672319446

and read it as much as you can.




I would appreciate very much any insight into what is
involved with this process.

If I should post these questions on a different site
please let me know.

Thank you in advance,
Allan



.