How to organize .accdb and tables? Best practices

[twgonder]

I'm getting back up to speed, after 20 years of avoiding Access.
I have some questions on how best to organize tables.
I have read a bit on splitting, but I think that will come much later, as I don't need it now.
I also searched for these questions below, but an hour of looking at the search results didn't yield (or I just missed it with a heading I didn't understand).

Is it best to create a large, robust suite of applications in one .accdb file, or to split up the different applications?

For example, I might have accounting, HR, Sales and PR applications.
Do I, while designing, put them all in one .accdb file?
What practical limits will I hit for the number of tables, queries and other objects?
Is a large .accdb more susceptible to corruption when put to actual use?
(I found one limitations spec sheet, but it was for really old versions of Access)

If I do split up the applications, what is the best way to handle a table that is used by multiple applications?
For example, a customer table could be shared between accounting and sales applications.
How do you decide where the tblCustomers should reside?
If it's in an accounting .accdb will the sales .accdb be able to get to it efficiently?
(Of if I have country, zip, state and city tables, they could be used in all the apps.)
Will the sales guys use the accounting customer form or have a copy in their own .accdb file?


How to deal with copying forms and programming from one to the other .accdb?
Do we even have to copy a form or other objects to the .accdb, or can one .accdb use another's forms and objects (and all behind it)?
The sales people might have to deal with prospects and actual customers.
A lot of the data is duplicated but there will be different forms based on what the each does with the customer table.
If there are separate prospect and customer tables, how does a contact manager app deal with two different source tables?
I've only created relationships for now in one .accdb, and I read that if a table is copied to another .accdb, it looses all the relationships.
Can relationships between tables be created/maintained when they are in two different .accdb files?

These are just of the few questions I've come up with (to jog the memory of those who've gone before me), I'm sure there are hundreds more.

What best practices have you found for .accdb files, during development and when going live to eliminate headaches?

Thanks

[Minty]

An Access file has a physical limit of 2Gb in size (Although I understand this might be being increased in a later version?), so some idea of the number of records you are likely to encounter would be a useful starting point.

You should always split your database - data tables in one Back End database and everything else forms, reports etc. in a separate front end file.
The FE file is what you distribute to end users, each user has their own local copy, connected to a network shared BE.

Most developers tend to keep a single FE but employ user management to restrict who can get to what.
There is nothing to stop you developing separate bespoke FE databases operating on a common backend, especially if a large amount of data is shared and you want to departmentalise the functions.

Separating the BE databases can lead to problems, although there is nothing to stop you doing so.
A single BE file also makes it much easier to migrate to a different BE system such as SQL server, Azure or MySQL, whilst retaining your Access FE.

I'm sure others will chime in.

[CJ_London]

Since you are linking to a be, you can link to multiple be’s. What you will lose is the ability to maintain referential integrity between different be’s.

With regards where to put the customer table - accounts or sales, consider who owns the data, who is responsible for data entry, creating new customer records etc. and when you can call a customer a customer and not a suspect or prospect. It may be once credit checks have been made which is probably an accounting function or who raises invoices

[twgonder]

I finally found this for Access limitations:

Access specifications (microsoft.com)

2 GB limitation, ha ha ha...

I started coding on a super-mini system that had 10MB of hard disk and 64k of core memory (you could actually toggle a panel switch which halted the processor, shut off the computer and in the morning restore power, toggle the halt switch and resume work without reading anything from the hard disk). We had 16 dumb terminals attached, all being used throughout the day by programmers and support staff. You could reheat your pizza on top of the 1.7 meter high enclosure. Ahh the '70s.

And to think, my big commercial banking app that handled dozens of clients, millions of customers, billions of dollars was developed on this system. Later, the app used to backup to 9 diskettes (1.2 MB) on an original IBM PC (emulating the super-mini) with all the code and demo data. The OS took another 6 diskettes.

Yet something as simple as just the menu portion in that app had:
All hierarchical menus from a single definition table. On every menu were 15 options for a sub menu or a "job". (No graphics or mouse to distract from getting work done.)
User/password logon control
User access control to menu and sub menus and jobs (anything like a form, report or update program) by user
In addition to user job access control, additional +/- control by employee/supervisor level, workstation, hour, day and even system wide lockout and logoff if needed.
User control to the command OS prompt and development and control environments (with two keystrokes an admin or programmer could see the underlying control record, menu or job template, logged of course)
Two step job creation from a template that the menu program used to create a PROC (kind of like a sophisticated batch file that could link several PROCedural steps together)
Complete logging (including user, workstation, date, time) of all menu jobs that ran
A full PROC that executed for any menu job (so that if the job aborted, it could be easily analyzed and restarted)
A historical copy of the PROC linked to the logging record
And a method to use different job templates so that the menu or job could be customized for a particular client if the default job didn't serve them.

I think I'll be busy for awhile just trying to emulate that menu environment in Access. Maybe I'm getting to old for this. Ugggh.

Sorry for the memory lane digression, but...
Has anyone seen a good menu type application for Access that offers some of this kind of control?
I don't see any reason to not have a form fire up and then control all the menus.
Hell, I might even want to support multi-language this time around.

These photos can't be any more obnoxious than all the ads sprinkled into these pages.

[orange]

"Is it best to create a large, robust suite of applications in one .accdb file, or to split up the different applications?"

You have gotten great comments from Minty and Ajax.
I would add that it is unlikely that the physical limitations of Access would be the major factor in your decision. It could be, but is this to be a "corporate" type database that spans all/most aspects of the business. Are the various business units co-located or are they geographically disparate ?

You could model the proposed database as a single conceptual/logical model then one or more databases, this would be a great facility for understanding the "business and how the various functions/units fit within the big picture". Then, depending on issues encountered or items discovered, you could separate into more detailed logical and physical models and one or more physical databases.

You could have several physical databases linked to a backend consisting mainly of reference tables. However, as mentioned, you lose referential integrity across multiple back ends.

I would suggest it is better to model the business "issue/opportunity" and vet that model before getting too involved with physical database. see stump the model.

Sounds like an interesting project.

[NoellaG]

If you are concerned about having a large amount of data you can, as Minty pointed out, choose for a BE database system such as SQL server, Azure or MySQL to keep the data tables + views (queries) + procedures and link your Access front end(s) to the database. So the access file(s) only need to contains your forms, reports and macro's/modules .

[twgonder]

You should always split your database - data tables in one Back End database and everything else forms, reports etc. in a separate front end file.
The FE file is what you distribute to end users, each user has their own local copy, connected to a network shared BE.

Thanks, that's a start. So, on a laptop, can I create two .accdb files in the same directory, one for FE and one for BE?
Can I safely have both open at the same time on the same laptop?

The training classes I had 19 years ago in Access never touched on these issues (because they probably didn't exist yet).

So far, I've just been creating small Access tests for features and how to(s).
Slowly I'm starting to create tables for data like country, postal codes, etc. that I would like to save in one BE .accdb file for preservation and to avoid multiple copies in testFE.accdb.

Can I have a testFE.accdb that has tables for testing and at the same time "links" to the BE.accdb?
So, I can't have referential integrity in that situation? How big a problem is this?
I assume I can have primary keys in the FE and foreign keys in the BE (or vice versa), but what won't I be able to do for forms without referential integrity?
If we have multiple FE files for each user, is their anything in Access for version control, so that an old FE.accdb doesn't contaminate or break a BE.accdb?

Any other concerns to consider with FE & BE?

I did a search for "best practice" but get lost in all the results. Is there a good thread that anyone remembers on these types of questions?

Thanks again.

[CJ_London]

Yes - sometimes the best thing to do is try it and see

[NoellaG]

Referential integrity is a property of the relationships between tables and has nothing to do with forms. So if all your tables are in one BE file and the forms in the FE, there are no issues to create relationships with referential integrity.

[twgonder]

Referential integrity is a property of the relationships between tables and has nothing to do with forms. So if all your tables are in one BE file and the forms in the FE, there are no issues to create relationships with referential integrity.

I guess I didn't make myself clear enough. Suppose I have a production BE. And I'm developing a new DB that uses tables from the BE (like employees that exists and is used in another app).
While developing, do I want to use a .accdb locally with tables as well as queries, forms, etc.?
and then "link" to the BE for all the established tables.
Obviously, I don't want to start developing in the BE and risk messing it up.
Or do I take the time to create a small copy of the BE table in my new development .accdb file (so that I can have referential integrity) and then remove the local copy later and attach the live BE table when it becomes a true FE version?

[NoellaG]

Obviously, you never ever do any development in the production environment. Always have a development BE and FE were you can do the development, a test BE and FE where you do the testing. For small businesses the test and development environment can be the same. You do the development in the development environment where you can add objects to the BE and FE. Once it works you copy the objects and object changes in FE and BE to the test(if seperate) and if all is well you can deploy into production. If other applications use the same back-end as the one you changed, those applications should be tested to before deploying in production.
But I would keep all data objects like tables and views in one back-end . You can link several apps to that one back-end.