Basic Database Questions - Split?

[Todd]

I'm a relatively new access user.
I've created a database which is used over a LAN by about
15 users.

I have a few basic questions about database design:

1) I know that the "compact and repair" tool decreases
database file size, but what exactly does it do. Is there
any danger of data loss?

2) What contributes the most to increasing the size of a
database file, and is there any maximum size or limit
where performance is decreased?

3) I currently have everything (forms, tables, reports,
queries, modules) in a single file. I've read a few posts
and a few articles on splitting databases into "front-end"
and "back-end". However, I've not really read anything
that explains it fully. What is the advantage
of "splitting"? How is it accomplished? What if I don't
split, are there problems I may encounter?

Thanks in advance for any help.

 

[Scott McDaniel]

1) The compact and repair won't lose any data, assuming everything else in
your db is in good condition. It removes deleted records (deleted records
aren't actually deleted until you perform a compact).
2) Compacting will decrease the size of your data files; other than that,
nothing will really decrease the size of the file. The maximum size is
limited to 2 gig on A2K and above, but the performance isn't really
dependant on size ... I've seen database with as little at 5 meg of data be
so slow as to be almost unusable, and I've seen databases with waaay over
100 meg be very peppy and responsive. Good performance depends on so many
different issues that to summaraize it in a newsgroup post is almost
impossible. Instead, read the groups and forums, try things out yourself,
and determine what is the best solution for your needs.
3) Splitting a db involves moving the tables to one database and the forms,
reports etc to another, then "linking" the tables together. If you have 15
users all trying to use the same database, corruption is not a matter of if
but when ... it will happen. Instead, each user should have their own copy
of the frontend (the forms, reports, etc) and each frontend should link back
to the common tables. There is a splitter utility in Access IIRC, or you can
easily split it yourself by building a new, blank database, importing ALL
tables from your currentdb, then deleting them from the original db. From
there, you would right-click in the database window and run the Link Tables
command. Follow the prompts from there.

 

[Todd]

Thanks for the info.
Couple more questions on splitting.

Do I understand you right in that the front-end should be
put on the users local hard drive, with just the tables in
the back-end on the server?

If I do this, doesn't this mean that anytime I make a
change to the forms/queries/reports that I need to copy
that change to everyones hard drive?

By the way, we are a shift operation, so although there
are about 15 users, there will never be more than 2 users
doing data entry at any time, and maybe at the most 3
users in the database (the extra user would be a read-only
user running reports). Does this change your answer on
the necessity of splitting?

 

[Kevin3NF]

To make updating the FE easier, take a look at the AutoFE Updater tool
on Tony Toews' website:

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

Free utility I highly recommend.

IMO, any database being used by more than one person should be split.

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001

 

[Scott McDaniel]

Yes, you'll have to update each user's copy of the frontend. If you let each
user use the >same< copy of the frontend you're just begging for corruption
problems. Kevin pointed you to Tony Toews frontend updater ... it will allow
you to "automatically" deploy your frontends when you need to make changes.