Good questions/reasons (see responses in-line below).
If you decide to go for a SQL-based database, you (or someone) will need to
serve as DBA and system administrator. Have you factored that "cost" in?
NeedHelp said:
Well, for a few reasons, some/all of which may be incorrect:
* I had read that Access was not great at multi-user handling
If you do a bit more searching, you'll find quite a number of folks who have
quite successfully used Access as a multi-user database/application tool. I
count myself in that group.
* I had tried to setup Security with Groups and Users and
could not get it to work. Though I understand the concept
of separating code and data, I have not used Split for
simultaneous users.
"Splitting" your database (separating the data from the front-end) is not,
in and of itself, in any way related to Security. But if Security is an
issue, you will need to either learn the Access security (held by some not
to be all that secure) or use Network and/or SQL-based security.
* I don't want to have to have Access for all the users
due to cost. But I just read something about a run-time
capability? Can this be done and for what cost?
Depends on which version of Access you are considering. You can get a
run-time license to distribute for some cost (older versions) or no cost
(newer version).
* Can this be done over a non-server network using
a shared directory on an XP/Pro system?
Yes
* If I have the database on a shared directory, how can
I prevent users from deleting/modifying the file?
By setting permissions on the database (oops!, if you have an Access
back-end, users will need read/write permissions to update it from the
front-end). This may be the reason you move to a SQL-based database
product. But you or someone else will still need to set up the network
permissions to prevent this.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP