2

Looked around and found a variety of answers, but nothing recent that really compares these options pro and con. So I thought I'd ask the community to weigh in on which route you prefer and why.

Background

This is what we have:

  • Common set of Access modules & classes used in numerous protocol databases (Access 2010 *.accdb split front/back-ends)
  • Front-ends link to back-end database tables & code (linked dbs)
  • Back-ends contain protocol specific data & code
  • Common module/class database shouldn't be directly edited by users

Knowns

Add-ins & db reference databases:

  • Require re-distribution each time they are changed (even if no code is changed within them).
  • Must be edited within their IDE vs. the IDE of the protocol database (or you'll lose your edits since that db isn't the common code's)

Questions

How should the common module/class database be connected to the protocol databases?

  • linked database just like back-ends are
  • attach it as a reference in the IDE (Tools > Reference)
  • create an add-in and add it as a reference

How would you do it and why?

What are the pros/cons?

Which option would maximize performance?

NWdev
  • 483
  • 1
  • 6
  • 19
  • 3
    This question will certainly invite opinionated answers, since there isn't a "gold standard", and it heavily depends on what you're doing. I've got a linked database setup myself, and I'm using automation to push modules to the front-ends. If you want to prevent users from editing your class modules, you can just lock them for viewing and protect them with a password after you're done (Note: not truly secure, but nor is linking it and adding it as a reference). – Erik A Sep 05 '17 at 19:43
  • Erik, thanks for your comment - actually I'm fine with having opinionated answers - we all have our tendencies w/r/t development ;) Can you amplify or add a link to how you're doing the automation push? – NWdev Sep 05 '17 at 20:15
  • 1
    Such questions are off-topic on StackOverflow. Some of the code used can be found in [this answer](https://stackoverflow.com/questions/45718163/can-an-ms-access-database-create-a-backup-of-itself-while-its-open-using-vba/45718616#45718616) (remove the code to create a new file, and the code for any objects you don't want to push). In my setup, I'm actually using a form that lists all frontends in a folder and all tables, queries, forms, modules, etc. and it allows me to select what to push. If this question is still here tomorrow, I might share the full code (have more time then). – Erik A Sep 05 '17 at 20:21
  • Thanks Erik. Looking at the code mentioned, the "push" referenced is more placing the modules/classes into the front-end which isn't quite what I was looking for, but I'm open to seeing how you use it. I may find it actually is better than I think - the underlying issue I'm trying to combat is proliferation of common code hence the common database. – NWdev Sep 05 '17 at 21:50
  • Not enough room last note: I disagree with thought that this question falls into "off-topic" (https://stackoverflow.com/help/on-topic). Though subjective, hopefully responses are detailed enough from those w/ real experience for question to be of value ala the "good subjective" thread here: https://stackoverflow.blog/2010/09/29/good-subjective-bad-subjective/ In reality, there are different solutions for many development problems. In this case, simply trying to determine which is best given constraints & other's experience. I can do any of them, but some will be better options. Cheers! – NWdev Sep 05 '17 at 21:56
  • You can "short-circuit" your worries about distribution by letting the user pull a fresh copy of the frontend(s) and libraries via a shortcut on the desktop running a script. It is described here: [Deploy and update a Microsoft Access application in a Citrix environment](https://www.experts-exchange.com/articles/23959/Deploy-and-update-a-Microsoft-Access-application-in-a-Citrix-environment.html) and is indeed applicable in larger organisations. – Gustav Sep 06 '17 at 08:27

2 Answers2

1

I have several variations of the following code lying around, I think this one is most applicable to your situation (it pulls modules and forms from a database, overwriting existing ones, as soon as the database is started).

Public Sub ImportModules()
    Dim ImportDbLocation As String: ImportDbLocation = CurrentProject.path & "\ModuleDb.accdb"
    Dim ObjectsToImport As Recordset
    Set ObjectsToImport = CurrentDb.OpenRecordset("SELECT * FROM Objects IN """ & ImportDbLocation & """")
    Do While Not ObjectsToImport.EOF
        On Error Resume Next
        DoCmd.DeleteObject ObjectsToImport!ObjectType, ObjectsToImport!ObjectName
        On Error GoTo 0
        DoCmd.TransferDatabase acImport, "Microsoft Access", ImportDbLocation, ObjectsToImport!ObjectType, ObjectsToImport!ObjectName, ObjectsToImport!ObjectName
        ObjectsToImport.MoveNext
    Loop
End Sub

This code is triggered from the AutoExec macro in the front-end databases. The database referred to as ImportDbLocation is the database containing all modules and forms I want to import. It contains a single table named Objects. This table has two columns, one named ObjectName containing the names of all objects that should be pulled, and one named ObjectType, which is a lookup field that corresponds with the acObjectType enum (some irrelevant objects removed).

Advantages:

  • You get a fresh copy of all modules in the database, making sure any overwrites are irrelevant.
  • You can add a third column to the Objects table to filter out objects for a specific database, thus selectively pushing some objects to some front-ends, and others to others (and a fourth one to specify the name of the objects in the ModulesDb file, to use multiple variants of the same form for different front-ends).
  • You have all code in the front-end, so no weirdness with external files and references
  • You can also use this code to pull any other type of objects you want (in my case mainly queries and forms)

Disadvantages:

  • Load time increases (normally by a tiny bit, but dependent on how many objects are imported, it might be long)
  • Modules are visible and readable for end-users (technically also modifiable, but any changes are undone on database load)
  • You can't modify the module doing the import this way
  • You NEED proper security settings, else users will get spammed with security popups on every database open

I also have a variant lying around that asynchronously pushes the modules from a separate thread to the database when opened, and I have another implementation that allows me to selectively move modules to front-ends after doing modifications.

I can share them if needed, but haven't yet implemented the asynchronous one in a production environment (still a work in progress, it should save on load time and can push all modules)

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Another disadvantage will be, that the code is left uncompiled. It may run (you will know; I haven't tried this method), but still. – Gustav Sep 06 '17 at 08:29
  • @Gustav You're right, but I have never encountered errors caused by it in real use. You should, however, not run functions from modules that get imported right away (in the same sub), since the `DoCmd.DeleteObject` somehow will get delayed, a duplicate of the module will get created, and then the module will still get deleted, but a duplicate with `1` appended to it's name will exist, and on the next import, you will have 2 modules and compile errors because of ambiguous function/class/sub names. But since you're calling the sub directly from the autoexec macro, this is purely hypothetical. – Erik A Sep 06 '17 at 09:32
  • Thanks Erik and Gustav. It's an intriguing option. Is the implementation in a distributed system with many users on a network (common db on the network with users pulling the code to locally housed db)? Would the dbs work when the network is down or the laptop (e.g.) is offline? – NWdev Sep 07 '17 at 15:04
  • @NWdev I'm using it in a network with about 5-10 users on the database simultaneously, but over a thousand users on the network doing different things (a large company network). However, the linked tables will be your problem, not the linked code. The code gets pulled when starting the database, and will be available offline after that. The linked tables won't (and linked tables on WiFi can cause errors). You can, however, pull non-linked tables via the same method, and then you can use the database offline, but it's a one-way stream, so you can't commit changes to those tables. – Erik A Sep 07 '17 at 15:10
  • Also, the code currently throws a run-time error if the source database is unavailable, but you could easily suppress that error by adding `On Error Resume Next` before the `.OpenRecordset`, and then the database would just work offline with the previously imported modules (and pull in new ones as soon as it's started with a network connection) – Erik A Sep 07 '17 at 15:13
1

First a big "thank you" to @Erik and @Gustav for your inputs.

Solution

The solution opted for in my use case was to use a common reference library database that houses modules and classes.

While I considered @Erik's solution, in the end copying over the modules and classes into each protocol database was a bit too much like sending out copies of them which could then devolve from the master modules/classes. This in part was one reason to shift to the single reference database - to avoid propagation into the other databases so there would be a single code source for them.

Caveat: If there are performance issues with the reference library solution, classes & modules may have to be pulled from the common library database ala @Erik's solution.

Database as Reference Library

The common modules and classes are contained within a database (*.accdb). Whenever the code is desired in another database, a reference is added like referencing any other code library (VB IDE - Tools > References). The only difference is that you Browse... to the library and make sure you select Microsoft Access Databases (*.accdb) in the search filter.

If the reference library database is kept in a common location, there shouldn't be issues with re-referencing, although re-connecting the reference is easily done (same as connecting in the first place).

I've also separated out common version control and development modules into similar separate databases that can be referenced when desired during development and de-referenced when a given database goes to production.

Development "Gotchas"

Actually these are more considerations/things to remember than true "gotchas":

- Edit library classes & modules in the library database

If you edit a class or module from the database that references the library database you will lose the edits as soon as you close the database you were working in.

You can try out edits from the database you're working in - the code will run, but it doesn't save.

I often have Notepad++ or OneNote open to copy & paste over code I'm testing to the library database. That way I can continue working on other areas and have a set of changes to update the library database.

- Compile the library database before using its components in the referencing database

This is more a workflow issue - make sure you save & compile in the library database, then open your referencing database after you've completed your changes and closed the library database first.

Resources

Here are some links which proved helpful:

The 6th reference (available only via the internet archive) has been particularly helpful in sorting out how to properly instantiate classes. In the end I opted to create a single Factory class (static class) with multiple functions (one per class - e.g. NewClassABC()) that instantiates the class within the common reference library database.

So far, so good.

The reference library database has the code in one place and other protocol databases can be updated to it when desired. No dealing with multiple copies and versions of the same module or class.

Hope this helps others looking for a "common" library type solution.

Community
  • 1
  • 1
NWdev
  • 483
  • 1
  • 6
  • 19