Web Application Development

For the more seasoned and sophisticated Microsoft Access database user, errors are a real pain but can be avoided or at least reduced at best to help handle the smooth running of an Access database.

There have been many instances for me personally (more so when I started using Microsoft Access) discovering different types of errors in my designs and yet I took great care in planning and building functionality that I knew was logical and followed protocol but still they were errors being triggered.

There are different types of errors you can have and I think it should be clearly stated here which one’s I want to discuss here. I’m not talking about coding (VBA) errors but errors that Microsoft Access functionality which is built in and users have to learn to work around the rules (or bugs if you want to be harsh!).

The following 5 errors in Microsoft Access which are built in are useful to note and will help explain why they happen and how you handle them as a work-around:

Some times when you try to save an existing table due to having to added, deleted or modified fields and their data types, you may get an error that says “Too many fields defined”. Why? Microsoft Access tables can hold up to 255 fields (though I wouldn’t recommend it) and even though your table may have for example only 50 fields (which is still quite a large number) you still see this error. What happens in Access is that it keeps a count of by incrementing by 1 each time you carry out any change in a table even if you had deleted a field. Solution? Simply carry out a Compact & Repair command.

When you run a form with some VBA code in it, the Numlock key on the keyboard switches itself off. Why? The quick answer is it’s a Microsoft Access (real) bug. It mainly happens when using a VBA keyword ‘SendKeys’ which simulates keystrokes from the keyboard. Solution? Don’t use it. There are better ways to code keystrokes in Access when coding with VBA.

Error message “Can’t Open Any More Tables” is due to having exceeded 1024 table index references in your Access database. A table reference is counted for any reference direct or indirect to a table source which means forms, sub-forms, reports, sub-report, controls that have a record source and of course queries. It all adds up quickly so the number of around 1000 thousand tables will accumulate before you realise it. This obviously applies to the larger database and the solution is to upgrade the database engine (known as Jet) which can be found searching for the latest service pack. For example, earlier versions of Access that used Jet 3.5; it had an upgrade pack extending 1024 tables to 2048.

When setting SQL statements or changes to existing SQL statements via a form or a control which depends on a source when using the internal query design view, you are prompted to save changes when returning back to the form or control. Naturally, you chose the ‘yes’ option but when you close and save the form the changes made (and saved) it had reverted back to the previous state. Why? in Microsoft Access the controls and interface tools were not as that user-friendly as one would like and not all features were refined. To work around this problem, just carry out one extra action. Before closing and saving the form, move the insertion cursor to a different property and away from the RecordSource property which applied the saved SQL statement.

This is the biggest error and in fact I’ve not found a solution in trying to fix it. The database simply will not open a form though other forms, reports and other objects are all working well. I’ve even tried Compact & Repair, renamed it and even rebuilt the form again (using the same name) and it still failed. It’s an educated guess here but I think it has something to do with a corrupted record in one of the system (hidden) tables and to avoid trying to repair it. I simply built a new database and imported all but the offending form into it and rebuilt the form in the database. That worked!

There are more and these are just some of the quirks of the application which Microsoft Access has slowly dealt with either a service pack release or a newer version. Just remember, there are always going to new types of errors for the later versions – that’s just software!