Can someone please explain to me proper modularization/frontending/backending?

[Access9001]

Is this a good way to handle things:

-Database that strictly makes tables and inserts them into storage db's
-The storage db's that hold the data
-A linker-db that link-tables everything in the storage db's that are required for present-day processes and has all relevant queries for manipulating/processing the table data

-A "common" db that has essential VBA functions like exporting things in certain formats/merging pdfs/emailing queries and files/other processing/etc, where any other frontend DB automatically imports these common VBA functions so that they can be used on the linker/query DB.

-Frontend DB's that access the linker db for viewing/editing data

 

[Banana]

That's a fairly complicated set-up but if that is truly what the business requires, then i say go for it.

Few points:

1) I suspect for large majority of Access applications out there, they're just frontend/backend.

2) The next level would be an additional backend (some may call it "sideend") which is stored side by side with front-end (not with the backend in a central share folder) to host the temporary data/tables for the end users' functions.

3) I've seen a Loader Application exactly once - it was to facilitate users who would be using Citrix to always open most current database (with options to go back to a specific version if needed). I suspect it's more common to use a free front-end update provided by Bob Larson or Tony Toews.

4) Library database (what you call "common Db) has been mentioned in any serious Access book but rarely occur in practice. I believe this is to two reasons: a) It's simple to just copy'n'paste the required functionality into an interior module anyway, b) adding a library database means more references dependency, not a strong point of Access. There was a recentish thread at UtterAccess by a guy experimenting with this and he found that it was more trouble than help, unfortunately. I do love the concept but the cost/benefits isn't there to back it up.

This is all my subjective perspective and I may be mistaken. I do hope it helps though.

 

[Access9001]

What I am doing is calling these functions in every new db I make:

Option Compare Database
Option Explicit

Public Sub importRemoteModules()
Dim varFileRep As String
Dim dbRep As DAO.Database
Dim doc As DAO.Document


varFileRep = "D:\Documents and Settings\myname\Desktop\CommonFunctions.mdb"
Call removeForeignModules
Set dbRep = OpenDatabase(varFileRep, False)

DoCmd.Hourglass True

DoCmd.Echo False

For Each doc In dbRep.Containers("Modules").Documents
On Error Resume Next
DoCmd.TransferDatabase acImport, "Microsoft Access", _
varFileRep, acModule, doc.Name, doc.Name
Next doc

On Error Resume Next
DoCmd.Hourglass False
dbRep.Close
Set dbRep = Nothing
Set doc = Nothing
DoCmd.Echo True
End Sub

Public Sub removeForeignModules()
Dim x As Integer, moduleCount As Integer
Dim moduleName As String

x = 0
moduleCount = CurrentDb.Containers("Modules").Documents.Count

While x < moduleCount
On Error Resume Next
moduleName = CurrentDb.Containers("Modules").Documents(x).Name

On Error Resume Next
If moduleName <> "__importModules" Then
Call DoCmd.DeleteObject(acModule, moduleName)
x = x - 1
moduleCount = moduleCount - 1
End If
x = x + 1
Wend

End Sub

 

[Access9001]

My problem is that sometimes common functionalities get updated and I don't want to have to sift through a billion other databases that use it to do the same edits