Maintaining modules/macros in Access.

Question

Hey guys I've written a little module/macro that helps our inventory department, they will need to run the module/macro a few times every month. They receive the databases from out in the field these are exported from some 3rd party inventory tracking system we have.

My question, is there any way to install this module/macro outside of the db file but still within access so that the inventory management team does not have to open vb editor, import the module, create a new macro, name it, set it up properly to execute the function?

The module/macro does not need to be changed for any new database that comes in.

Basically they need to be able to open any database received by the field and have this functionality in the module/macro available to them without having to set this. Is this possible?

Answer 1

Perhaps you are thinking of VBScript or a back-end, front-end set up?

You can use VBScript to perform actions on an Access database, through the Access object, with ADO and so on. The script can either accept command line input, request information, or run against any database in the current directory.

With back-end front-end, the new database becomes the back-end and the Access file with your macro is the front-end. Your macro should ask the user for the name of the back-end file and either use that with the Access object or link the tables, according to what is needed to be done.

Answer 2

You could look at my article for vb123.com:

Using Database Library Files in Your Access Application

The thinking there is that you can put access objects in a library file (still an mdb or mde file, or any of the new access file types) and then by just adding a reference to the file in your VBE project, you get that functionality made available to you.

It works with forms, reports, queries, classes, etc. To be honest, I haven't tried it with macros, but don't see a reason why it wouldn't work.

The beauty is that reusable functionality is packaged into one file, that is still just a plain old access file.

If you had the inclination, you could also write an access add in that basically calls your macro in the library.

We did this with a product for making dealing with SQL much easier in Access, and it has worked for years with Access installations all over the world. You can install the add in using a professional installation package such as wise installation, making it a pretty seamless experience for the end user of your macro. However, there is a considerable overhead in writing this kind of setup in a professional way. Depends on what your users need, I suppose.