12

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!

Improve this question
0

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, it looks like that might work perfectly! Simple indeed.
It seems like a great idea, but it can get to be a nuisance.
I was about to ask what kind of nuisance, but then noticed Tony's follow-up. I appreciate the warning Remou :)
You can also have speed problems, if you are not careful.
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.

Improve this answer

1 Comment

Yep. Gotcha #3 is a real em, nuisance :)
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).

Improve this answer

2 Comments

Good article. (Ahhh, now I see why I couldn't post my comment yesterday. It was too short. Grrrr)
The article cited above ("Using Database Library Files ...") is not available at the link given, and I was also unable to find it in a search.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.