Class Module in a heavy use environment

[JoeReed]

Greetings...

I work in an organization that is using Access to generate both PDF and Excel reports. We have 3 report servers running and a total of 108 Access databases creating all of the reports. Most are pulling data from either Oracle or SQL server and manipulating the data and then reporting on that data.

I would like to move some of the functions (Email, PDF and Excel Spreadsheet generations, etc) into class modules. My question is with the heavy use of all the databases running is there a downside to using class modules?

Each time one Access database opens with a link to the Class Library it opens another instance of Access so for every database run we will have 2 Access instances running.

Environment: 2008 R2 Data Center Server, Office 2010

Any insight would be greatly appreciated.

...and yes we are planning on moving away from Access to SSRS later in the year.

 

[vbajock]

I have spent the last twenty years managing Access apps is large corporations. IMO, the proper way to do it is to create a seperate database "front end" for each user, and then keep a master "install" db for yourself.Place shared tables in a SQL server back end or, if you do not have SQL server, a central Access mdb, and link the tables to your frontend. Place the user frontend in a folder that only they and you have access to. Make your changes and updates to your master, and then copy the master into each user's folder on a timely basis. If you allow a whole bunch of users to attempt to share a single Access database on large network, you will have no end of headaches.

As far as the class modules specifically goes, it is the same principle. I have a folder called Shared Code which contains a single MDA file where all my commonly used routines reside, and this MDA is referenced in all my frontends.

 

[JoeReed]

vbajock - thanks for the reply. These 108 databases are not used by end users instead they are just kicked off at various times by either a TechSheduler or another Access database (Report Runner) that may run up to 50 Access databases synchronously.

My concern was how would the Shared Code (Class Library) behave with many calls hitting it over and over.

 

[vbajock]

Should not matter at all, the client just reads the modules, nothing is written to them, the code gets read into the local client memory when needed.