general question (1 Viewer)

[rainbows]

my database covers 7 areas of manufacture, sales, engineering , purchasing, stores, quality , finance
each person can log onto their own area with a password
the backend is split away from the FE .
there are about 60 tables, 100 forms, 120 quires , 20 reports of which i am trying to tide up a bit . say 10 % of each will/could be removed

my question is

is it beneficial for me to create a FE for each of them areas instead of one so there is less tables, forms etc in each and why

thanks steve

 

[Gasman]

Tables are in the BE?
Do you have common functions/subs that each segment uses?
Are you getting near the limit for your FE?

 

[rainbows]

Yes tables are in the BE
i did not know you had limits for the FE thats one reason i asked the question and does it slow the database down
"Do you have common functions/subs that each segment uses?" lets say 30 % of each area use the forms,/ tables etc from other areas

steve

 

[Minty]

Separate FE's might have some benefits, but might also become a PITA, as if you change something that is common to all of them, then you have to update them all, so 7 updates instead of 1.

Any Access file has a size limit of 2Gb, however, a front-end file would be extremely unlikely to ever get anywhere near that, as forms and code alone don't take up much storage space.

Compile save then compact and repair, and see how big it is.

 

[CJ_London]

does it slow the database down

Multiple objects such as forms/reports will not slow the app down just by existing. What potentially might slow it down is having multiple forms/reports open at the same time but that would be more a factor of number of connections required

you can have 32,768 objects in an access FE - see this link for a full list

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

 

[Gasman]

Any Access file has a size limit of 2Gb, however, a front-end file would be extremely unlikely to ever get anywhere near that, as forms and code alone don't take up much storage space.

I can think of one member here, where that might not apply to them (if they ever split it)

 

[The_Doc_Man]

There is a performance and stability advantage to splitting a database into FE and BE files. Once you do that, unless you have something like a table that resides in the FE for some obscure reason, the FE won't be growing much if any after the split. Only the BE will likely grow very much.

Access is a "partial file" app in that it uses the Server Message Block protocol to pick out the pieces of a file that it wants on network files, and if you have individual (but identical) local copies of the FE file for each user, the file system will pick out the pieces of the local copy. That means that having everything from each division in the same FE file is still essentially an equal data load for the file system.

Stated another way, the size of the FE is immaterial to the load as long as users have private copies of that common FE. There would be no file lock collisions within the FE if everything is in a private copy. As long as you don't use pessimistic locking on direct or implied queries to the BE tables, you are unlikely to have too many collisions in the BE either.

 

[Pat Hartman]

The FE and BE are split. That is an excellent start. The next question which is not clear with your explanation is - does each user have their own personal copy of the FE? If the answer to that is also yes, then you are in good shape. If all users are all opening the same physical copy of the FE from a shared server location, that should be changed. There are two common ways to distribute copies of the FE to individual users.
1. a batch file. Simplest solution.
2. a custom database. Several different options of varying complexity are available for downloads.

We can help with either/both.

As to the specific question of splitting the app into functional areas. -- Probably not. Unless the app is huge or you don't want people from one department to ever use or even to see forms used by a different group, splitting by functional area is rarely necessary. Splitting the FE by functional area still usually requires you to keep a single BE with all the tables because some percentage of tables will be used by multiple functional areas, starting with the user table. If you split the BE, you may cause other issues because you cannot enforce Referential Integrity except WITHIN a single database. So, if three areas use the customer table and sales, purchasing, and finance all have tables that are related to customer, you created a problem for yourself because if you split the BE, you will have separate BE's for the three areas but you can't duplicate the customer table and you can't enforce RI if the customer table is not in the same physical BE as another table it is related to.