Using Access 2000, I have designed & deployed a server-based database table (Be) in the server and distributed the (Fe) to about 10 12 users machine. They start using the database since March and so far entered about 5000 records and there was no problem. One day they entered a lot of records but, when they run the report they didnt get the numbers right (they got only few records). I took a look at the table (audit trial) for that given day, they entered 250 records. Although I have a Save Record button, they really do not need to use it (when they click Next Record button it usually saves it for them)
1. What can be the cause and where would be the first thing to look at?
2. How often do you have to Compact/Repair the database
3. The database is growing very fast when do I need to make a decision to upsize it to Sql server?
1) Sorry, no clue. Start searching with your record locking logic. Perhaps have the users create a row on their FE and append to db on BE using a query. Create a test scenario to simulate 10 users banging away all day.
2) I use FMS Visual Agent and Compact every day, but my app shuts down 12 hours a day. Not all apps are this lucky.
3) 5000 rows is a small number. No need for SQL because of record count (although the user count is a factor).