How do you add add-ins to Access 2007?

Question

I have Access 2007 and I downloaded the Developer tools and installed them. At the same time I also installed Access Runtime. The Developer tools were fine and working for a while.

One day, I had created an ACCDE file for which I wanted to package up as an application. Once I had made the file I went to open the Developer tools but the icon wasn't in the menu. I thought something had gone wrong so I uninstalled the tools and reinstalled them a few times but the icon didn't come back. I did some research to find out the problem and it turned out that switching between versions had turned them off. So I uninstalled runtime, opened up full version of access and tired to turn the add in back on but Access won't save that I had clicked the box.

I removed the add in from access and tired to re-add to see if that worked. However I don't how to actually add the add-ins from the access options menu.

I hope I have explained the problem clearly.

Answer 1

It's five years later, but this is the only thing that came up on all of Stack Exchange when I Googled "Access 2007 custom add-in", so maybe someone else searching for that would like to know what I found out.

I don't know if this would suit the needs of the original questioner, but what I wanted was to have a VBA module with code for functions to be called from queries or modules in multiple other Access files.

I found two useful resources:

• A StackOverflow post from eight years ago!
• An Experts Exchange post from five years ago.

The SO post offers a simple solution, but then there are warnings of potential problems, with links to two fairly technical and complicated resources. The EE post suggests the same simple solution without the warnings.

The simple solution is just to:

Create an Access file that contains only the desired module(s) and then create a reference to it in any other Access file intended to use its code (via VBA: "Tools > References > Browse").

That's it! Albert Kallal points out in his comments below that technically this is a code library, not an add-in, but as far as I can see, it accomplishes the same thing.

File extension. The SO post talks about naming the new file with the extension mdb, mde, accdb, or accde. The EE post, on the other hand, says it can have the extension mdb, mda, accdb, or accda. I'm not familiar with "...e" files and didn't get into that. The EE post says the "...b" and "...a" extensions are interchangeable and recommends using the latter to pretend that the code library is an add-in (because it acts like one), and that's what I've done.

It works from accdb, not mdb files. Since both posts talked about mdb and accdb files, I figured the functions could be called from both types of files, but I found that wasn't the case. I found that if I attempted to set a reference from an mdb file, Access said it couldn't do it. I tried this with the library created as an Access 2002-2003 file with extension mdb or mda and with an it created as an Access 2007 file with extension accda. None of them were able to be referenced from an mdb file. However, from an accdb file, the technique has worked fine for me, and I'm now able to call functions defined in that accda file from multiple accdb files.

It can be in any folder. One of the references linked in the SO post says the technique has problems if the Access file containing the code and the one referencing it are in different folders. It doesn't say what those problems are and I haven't experienced any with my files in different folders.

Same module for Access and Excel. And by the way, I'm using one module in both my Access library created by the above technique and in an Excel add-in. So I use that module to define functions that can be called from either other Access or Excel modules or from Access queries or Excel cells. (The Access library and the Excel add-in both have a second module with code that is not portable between the two applications.) But I have to be careful to maintain the versatile module so that changes in either Access or Excel are copied to the other, since I'm not yet ready to tackle using a single file as an add-in for multiple applications.

Save warning. One caution: Be careful about saving any changes to your modules. The SO post warns that changes made to a library module from the VBA window of an Access file referencing it cannot be saved, so changes must be made in the VBA window of the library itself. On the other hand, I have discovered by my own painful experience that changes made to any Excel module are not saved when the Excel file containing it is saved from the Excel window. The module must be saved from the VBA window or any changes will be lost without warning when the file is closed!

What about the Add-in Manager? After doing the above, I was curious about what would happen if I tried to install the code library using Access's Add-in Manager (available via either Office button > Access Options > Add-Ins > Manage > Access Add-Ins > Go or Database Tools > Developer Tools > Add-ins > Add-In Manager). I clicked "Add New", browsed to my file, and clicked "Open". I got the message, "There was an error copying the add-in to the Microsoft Office Access directory. The add-in was not added to the Microsoft Office Access directory." So I'm lucky to have found the above code library technique as an alternative to using an actual add-in.

Answer 2

On the Access Ribbon: Select Database Tools->Add-ins->Add-ins Manager If the required add-in does not show in the list, press the Add New button and locate it on the file system