11

I've been put in charge of 50+ Access databases (.mdb's and .accdb's of varied versions) spread around an intranet, and I need to add a few VBA functions (the exact same code) to almost all of them. I'm hoping it can be accomplished by creating a reference from each database to a centralized VBA module, as I'd really like the ability to make changes down the road that are automatically replicated in every database. It would also be a bonus if any additional references in the central db could be kept intact so I wouldn't have to manage them individually as well.

I'm pretty new to VBA programming (although I've been a PHP junky for years), and have been having a hard time coming up with the right words to search for this particular issue. I think (from what I've read so far) that I might need Visual Studio to compile a DLL to reference, but I'd like to keep everything simple and contained within Access if possible, especially since we don't have a VS license.

Thanks, any pointers would be much appreciated!

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
spudsy
  • 127
  • 1
  • 1
  • 5

3 Answers3

14

Don't worry, it's quite simple.

  1. Make your "helper" database. Put a few procedures in there.
  2. In your 'client' databases, open the VBA Editor
  3. Go to Tools->References. Browse and select your helper database.
  4. Done! You can now use all the functions in your helper database throughout your client databases.

So... no .dll files to worry about. No Visual Studio needed.

PowerUser
  • 11,583
  • 20
  • 64
  • 98
8

The search term is add-ins. (Ahh, I see Google has improved the search experience with words with embedded hyphnes since the last time I tried.) the approach mentioned by PowerUser will work but there are a number of issues.

  • You will want to distrubte the add-in along with your FE to the users PCs.

  • An MDE/ACCDE cannot reference an MDB/ACCDB. But if you change the add-in MDE/ACCDE you must redistribute the FE MDE/ACCDE as well. Even though you didn't make any changes to the parameters or subroutine/function names.

  • While you are debugging the code in the add-in or dealing with an error message VBA will open the add-in VBA code editor. Do NOT make changes there. They will be lost as soon as you close the add-in code window.

See my Add-in Tips, Hints and Gotchas page for more details.

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
5

You could also take it a step further. I wrote an article on this for vb123.com:

Using Database Library Files in your Access Application

You can use forms, reports, queries, modules, classes... Pretty much everything except subforms. And all in a plain old mdb or accdb file (or mde, for that matter).

awrigley
  • 13,481
  • 10
  • 83
  • 129