NAD Database Applications LLC

Things That Go "Bump" in the Database- -

There are so many little things to know, and to remember, and to consider, that's it sometimes hard to convey to a customer. You need an experienced developer, not just a power user or a "newby" developer.

Perhaps the best way to illustrate our point is to list a small sampling of things that "newby" developers and power users don't typically know. There are literally hundreds of things just like these.

When you build an MS Access database:

Never Use "Compact On Close"- - -
Not only does this sometimes delete the entire file, it does not compact the back end data file in a split (front-end/back-end) design.

Always Turn off "Name Change Track/Auto-Correct"- - -
This can cause all sorts of database corruption - PERIOD! Serious developers never use this!

Use Auto-Number Key as VERY LAST RESORT!
A Primary Key should uniquely describe the records in the table. Long Integer numbers such as 1, 2, 3 do not describe anything you are trying to track, now do they?

Front-End - Back-End is The Way to Go!
Splitting the design into a front-end user interface file with all of the screens and queries, etc. and a back-end data file with tables only has tremendous advantages. Users are no longer vying for the attention of the user interface elements, as they each have their own (local) copy. But, you will need a method to update the front-end file on each machine when changes are made. (eh-oh...) We know how to do that!

That Front-End File Goes On the Hard Drive!
There's no good reason to split the design into a front-end user interface file, and a back-end data file, if you put the front-end file out on the LAN...! That defeats the purpose. Placing the front-end on the LAN means that all users are still sharing it, and it has to divide it's time among all of them. Unfortunately, - some folks do it anyway.

Export Memo Text - - -
If you try to use a query to export the text in a memo field, the text will be cut off after the first 255 (approximate) characters. Another small MS Access bug. We know how to export the entire text.

Strange (Perhaps Chinese) Characters in Query- - -
If your query contains a join on an unindexed field, strange characters can result. Likewise, if you use a group-by clause on a memo field, strange text characters can appear. (We've seen a single Chinese character appear in the place of normal sentences.) Another little MS Access "gotcha". There are hundreds...

This is just a tiny sampling. There are literally hundreds of other things to be aware of. If you don't have a lot of experience, trying to make MS Access do your bidding is not only frustrating and overly time consuming, it can end very badly in the long run.

And, each version of MS Access has it's own new set up bugs and pitfalls to know about. From MS Access version 2.0 (1993) to MS Access 1995, 1997, 2000, XP (2002), 2003, 2007, 2010, 2013, 2016, 2019, 2021, etc. we have the experience!

Hire a Professional, and Avoid So Many Pitfalls...!