Table capacity versus performance/errors...

Originally Posted by zepphead80

Hi all -

This is really more of a strategic question rather than a specific debugging question, though I guess you could say it's a little of both.

I've got a database which I completely designed myself and distributed about two years ago for people in my division to track checks with. It's a FE/BE setup, and I use ADO commands with parameterized SQL queries to interact with the BE (pull data, update records, etc.). Since it was distributed, it has worked nicely with minimum attention from me, except to back the data up periodically, fix small bugs as users discovered them, etc.

A couple of problems have arisen over the past couple of months though. The first is that sometimes when users attempt to enter checks into the database, they get a 6 - Overflow error. Most of the time, when I get that, it means that too much data is trying to go into too small of a field. And yet, when one tries to enter the same check a short while later, it goes in. So the seemingly random nature of the error makes it difficult for me to pinpoint a cause - because if the user gives me the check to enter after they have encountered the error, it will go in just fine for me, and I can't even breakpoint the code to see what's happening.

The other issue is, the table which holds the checks has grown to over 16,000 records. This is the first database I built which has been so heavily used. So I guess my questions are: can the size of the table somehow be causing the first problem? What would be the ideal limit for the number of records in an Access table which resides in a BE on a network drive (non SQL server)? Finally, is there an ideal strategy for what do when a table does become too large (i.e archive older records somewhere else, etc.)?

The table schema is attached here in .txt format. Any insight would be greatly appreciated. Thank you!

Originally Posted by zepphead80

Thanks ADezii for your reply. I've been away from a computer most of the weekend and am just reading it...

I have attached a text file with the code. A sub "SaveEmployee()" for saving/updating the employee data, and a sub "SaveCheck()" for saving/updating check data. These subs are called from the On Click event for a save button in the form where the users enter all the information in various text fields (denoted by "txt..." in the code).

The boolean variables "booEditCheck" and "booEmployeeExists" are globals that are set elsewhere in the code telling us whether or not these are brand new entries or edits to information already in the tables.

Again, I emphasize that things have generally worked well up until recently. At the time that I made this database, it was my first time using ADO to accomplish the task at hand, and it may be subject to lots of improvement - but it has functioned up to this point.

Originally Posted by zepphead80

OK, I'll try that.

Another thing that occurred to me is that I have my text field sizes on the back end set to the Access default, 255. Doesn't this mean that Access will allocate that much space even if I'm using a much smaller amount?

Because in reality, for instance, the ERN is always just a seven character string, first and last names are no more than 50, etc. So maybe resizing those fields will make a difference also, combined with your suggestion...

I'd still like to know though if there is a strategy to use for archiving old data, and just how big is too big for an Access table. I'm not sure if there's any objective way to answer that. Just looking for a guideline. Thanks!

Originally Posted by zepphead80

Hi ADezii -

I'm completely with you on #1. As for #2, the answer is yes - ERN is always seven digits long (but not a number necessarily - for instance '0053491') and check number is a fixed length also. I enforce both of these things.

For #3...the reason I set it up that way is because, in my view, there isn't a whole lot of difference between adding a new record and editing an existing one except for the SQL that is used. So I use the if - then to pick out which case it is, and then the rest of the ADO code stays the same.

For #4...what do you mean by 'prepared state'? I used the '?' notation instead of '@Parameter Name' to shorten the SQL statement, even though the '?' requires the parameters to be defined in a specific order. I didn't have any particular functional reason for using '?' as opposed to '@'.