Programatically Installing Add-In's within Access

[sixHat]

Finding information on Add-In development for Microsoft Access is like getting all of your teeth pulled! Yes I've found the couple Managed Add-In Articles written... but could find next to nothing for Un-Managed Add-Ins. I did find one great article which is very old in creating basically an unmanaged .mda project... which I've followed and created a add-in. Now I would like an automated way to deploy this add-in.

I've seen it done from VBA with such tools as the Find and Replace add-in tool... but can not find a way to do this programatically in Access. I have found lot's and lot's of articles on Excel Add-In's and even Excel Add-In Installation. One such method uses VBA like so:

Sub InstallAddIn()
        Dim AI As Excel.AddIn
        Set AI = Application.AddIns.Add(Filename:="C:\MyAddIn.xla")
        AI.Installed = True
End Sub

Unfortunately Access does not seem to have this ability. If anyone could point me in the right direction I would greatly appreciate it. AND if anyone knows of any books or references that goes more in-depth to creating Add-Ins for Microsoft Access that would be great.

 

[MarkK]

What are you trying to do with your add-in? In most cases it is easier to set a reference to a library db than it is to write an add-in. When you set a reference to a database, all the Public code in your referenced db is available in the CurrentProject, and you can even expose custom classes from the library, with the provision that the library also expose a function to deliver new instances. My belief is that given how easy it is in Access to set a reference to another database file--something you can't do with Excel VBA at all--making an add-in becomes relatively cumbersome with so little reward, that almost nobody does it.

But it depends too what you want to do. I think if you want to create integrated wizards for users to create Access Objects like Reports and Forms and so on, then an Add-in makes sense. But usually developers want to maintain a library of commonly used routines, and in that case, you should reference a code library.

IMO. Hope this helps,

 

[sixHat]

@MarkK Thanks for the quick response. Really it's a simplified Linking Tool that creates DSN-Less and ADO connections to back end SQL Server... it isn't a code library as it has a front-end.

I can manually install it fine... but was hoping for a programatic approach besides using VSTO

 

[MarkK]

You can display forms in a referenced code library, if that is what you mean by "it has a front end." Imagine you reference a database that contains a form called Form1 and a standard module with this public method . . .

Public Sub OpenReferencedUI()
   DoCmd.OpenForm "Form1"
End Sub

So now if you reference the file described above, you can run this code, even in the immediate pane . . .

OpenReferencedUI

. . . and Form1 in the referenced file will open, and appear to be fully integrated into the current database. It is even added to the Forms collection. Furthermore, any code references to CurrentDb and CurrentProject in the library database will refer to those objects in the current application (as opposed to CodeDb and CodeProject, which a library file can use to refer to itself.)

That makes a referenced library database file very powerful, and easy to use and distribute.

 

[sixHat]

@MarkK Right I've tried adding the app as a reference before... but how to make this available as a selectable Add-In from the Add-Ins Menu on the Database Tools Tab?

 

[MarkK]

Yes, I am trying to persuade you that a referenced library file is much simpler in Access than a VBA add-in, and probably offers you all the features you need, but it's obviously your decision to make.

 

[sixHat]

@MarkK... Yes, thank you. I am trying to make this into a publicly distributed Add-In however.

 

[MarkK]

Best of luck.

 

[HiTechCoach]

sixHat,

Sounds like a very useful tool. would be interest in using it.

How are you planing on deploying this add-in? Are you going to using an installer package?

@MarkK,

Yes, I am trying to persuade you that a referenced library file is much simpler in Access than a VBA add-in, and probably offers you all the features you need, but it's obviously your decision to make.

A referenced library file if normally used for a specific database and easy to deploy with your application. Add-ins are used to extend the functionality of Access and not deployed with database.

 

[sixHat]

@HiTechCoach Thanks... I myself have found it very useful and fast as I am always moving from development to production between different customer servers. When I used to work for employers years ago they had different linking and connection scripts they used but they all seemed problematic and unreliable... and overly complicated.

I created this as a simplified and easy to use tool that quickly links your SQL Server tables directly to your Access Database... it also provides a function for ADO connections.

Right now I am manually deploying it... i.e. adding the .mda file to the Office Add-In folder then using the Access Add-In Manager to install it... this step isn't that difficult... but all the add-in tools I use like the Find and Replace tool and M-Z Tools have their own Installers. Which is what I am trying to replicate thus the reason for the question.

I believe M-Z Tools is mostly a managed Add-In so it uses Visual Studio for Installation. My tool is Unmanaged so I am trying to perform the installation through VBA. I've seen this done before as previously mentioned with Rick Fisher's Find and Replace tool He uses a simple .mde... or .accde for deployment which is what I am also trying to accomplish.

I've received a lot from the Access community over the year and wanted to give something back thus I would like to make this tool freely available and donate it to Access MVP.com but before I do that the last step is the installer

 

[HiTechCoach]

sixHat,

That is great. I will help in any way I can.

I think you need to create an installer package that will add registry keys.

I will do some research to find out what keys are needed..

FYI: I have purchased some really good software for creating installer packages.

 

[sixHat]

@HiTechCoach I've also tried creating a VSTO Managed project following the one tutorial that I've found that walks you thorugh this: https://msdn.microsoft.com/en-us/library/Aa902693(v=office.12).aspx?f=255&MSPPError=-2147217396 for the purpose of creating an installer...

The problem I am having is relating how the finished product gets deployed to the AddIn folder as the tutorial just leaves you at deploying it in debug mode... but this does not actually "Install" the Add-In to make it available from the Access Add-Ins menu.

 

[sixHat]

@HiTechCoach... Thank you... I greatly appreciate your help. Just an F.Y.I. I am an MSDN subscriber so have all these tools available as well as InstallShield LE. I've also just recently installed VS 2010 and VSTO 2010 for the purpose of trying to create an installer for this tool as what I could find seemed to reference that.

 

[HiTechCoach]

Unfortunately InstallShield LE is limited to deploying a VS project. Not really what you have.

That is why I purchased a full blown installer that will install anything.

 

[HiTechCoach]

You mentioned MZ-Tools which is a VBE add-in. There is anothr one called V-Tools. It is open source and will install from an access database.

See: http://www.skrol29.com/us/sourcecode.php

You can see how it writes to the system registry to install the add-in.

Since you want to post it on Access MVP.com I would expect that this will be open source. I am sure htis will be a great example for teaching.

 

[sixHat]

@HiTechCoach... Thank you very much!! I believe this is exactly what I need... I am going over the code now and it looks to be it. I'll let you know when it is all ready for deployment... hopefully this week with my current schedule. (I am also creating video demonstrations for the project)

 

[HiTechCoach]

That is great news!

I am so thankful so many people post there source for other to learn. I have started doing the same on my own site ( http://www.hitechcoach.com ) to give back to the community.

 

[Skrol29]

Now I would like an automated way to deploy this add-in.

Hello,

An Ms Access add-in is technically only an Ms Access database on your computer and some Windows Registry entries that
display items in the Add-in menu and some contextual menus that are linked to some Access objects of the add-in database.

Since you're not supposed to edit the Windows Registry by yourself, Ms Access provides a simple install feature for your add-in database : create and feed a system table [USysRegInfo] in your add-in database and then the add-in can be installed using the Ms Access Add-in Manager (menu Add-ins / Add-in Manager).

More detail about [USysRegInfo] : simply search in Google : « Creating a Simple Microsoft Access Add-In ».
Sorry, I'm not allowed to post links yet.

But such an installation needs that the user do open Ms Access, then go to the Add-ins menu, select the add-in database.
If you want an installation using an EXE or an MSI package, then you can use an install tool such as InstallShied in order to unpack the add-in database file in a directory and then edit the Windows Registry.
I don't have a documentation about Registry entries for the Ms Access Add-ins, but you can find some examples in the free code source of V-Tools.

Link : Google + « V-Tools "Source Code" Skrol29 ».
Sorry, I'm not allowed to post links yet.

V-Tools is an Ms Access add-in that does not have the [USysRegInfo] table. Therefore it cannot be installed with the Ms Access Add-in Manager. It uses another technical: if you open the add-in database as a normal database then an automatic Access Form starts up. This form drives you for the installation and have some VBA code for doing the same as install tools. That is : copying the file in a directly (it copies itself) and write into the Windows Registry.

Why using this technical instead of the [USysRegInfo] table? The goal was to be able the change the language in the items menus. V-Tools is a full multi-language tool.
But since Windows Vista, you need to start Ms Access as an administrator otherwise the VBA code of the add-in database won't be able to write to the Windows Registry.

I hope those technical information could help you for your problem.
Regards,

 

[sixHat]

Thanks @Skroll29... much appreciation in the response. Sorry it took a while to get back to you! I've just begun freelancing full time almost two years ago and it seems I never have much time anymore.

I have stepped threw your code and it makes sense... especially when I discovered the templated architecture of your Add-In your suite. After about what seemed like 30 or 40 steps and 5 or 6 modules of... it was just too many parts for me to piece together in a timely fashion which is why I shot you the email.

I was hoping for a base code example on just the Add-In to the registry portion... but that's ok I know I am not the only one short on time.

When I do get a few more minutes I will continue stepping through the rest of it to pull this out... but in the mean time I wanted to donate my add-in which can be viewed here: Link Master

Question @HiTechCoach... I could have sworn I saw a resources page on Access MVP's where different ones contributed different resources. But in going back there I can not find it now? I know that the MVP's themselves have a resources page on there own... but do you have any recommendation as to where I can donate this Add-In?

In the past I've tried to host my own applications for download but browsers seem to flag unknown exe's... In my research on this issue I discovered you had to purchase a certificaton through a Certification Authority, like Verisign.

Is there anyway around this so I can host the download myself?

 

[HiTechCoach]

Question @HiTechCoach... I could have sworn I saw a resources page on Access MVP's where different ones contributed different resources. But in going back there I can not find it now? I know that the MVP's themselves have a resources page on there own... but do you have any recommendation as to where I can donate this Add-In?

In the past I've tried to host my own applications for download but browsers seem to flag unknown exe's... In my research on this issue I discovered you had to purchase a certificaton through a Certification Authority, like Verisign.

Is there anyway around this so I can host the download myself?

I have not have that issue with the stuff I hoist on my own site. I will be glad to host it on my site at http://www.hitechcoach.com

I say the youtube video but never found a download link. Where can you download the add-in?