Load User Defined Function at Startup

[xanksx]

Hello All,

I have tried to search this all over internet without much success. I have quite a few user defined / custom functions which I would like to be available in any new database that I create without have to copy the VBA codes in a module each time. Basically, some sort of add-in that loads on MS Access startup may be (but I am not too sure - never created one). So something that will start with the MS Access Application and not just a particular database. I know excel addins can be copied to the XLStartup folder to achieve somthing like this in excel, but not sure about Access. Please note that I do have admin rights to modify program files in the C: drive where I am trying to do this.

Any help / direction is highly appreciated.

Thanks
xanksx

 

[Mihail]

I don't know if what you are looking for exist.
But an idea: Export the module then import it in every database.
Again, far away to be sure that this is the "best" approach.

 

[xanksx]

Hi,

Thanks for your reply.
But importing the functions each time is what I am trying to avoid.

 

[Mihail]

I think that THAT is what you are looking for.

 

[xanksx]

Thanks a lot!
Judging from the description on the link this is what I am after and think would work. But unfortunately I don't have enough rights to modify anything in the C:\Program Files.

But this looks like the solution. I will see if I can find a way around the access. Thank you for your time.

Thanks

 

[gemma-the-husky]

the easiest way

have a macro called AUTOEXEC. this is the first thing that happens when any database is opened.

in there, put run code, and set this to a function called "mystartup()" or something suitable

NOW, have a public function in a module. in that, put your standard startup stuff. just copy this idea from database to database.

public function startup()
....
....
startup procedures
....
....
end function

 

[xanksx]

Hi Husky,

I don't want to copy anything in every database. The problem is I need to work with hundreds of databases and every time I think of using a custom function, I need to get it from somewhere it is currently saved.

I am looking for something where these custom functions that I have created will be available in each and every database when I use it. (Its okay if it is user specific i.e only if I can use those functions.)

Is there any way I could create a custom library in VBA and add it as references and park these custom funtions in that library?

Thanks
xanksx

 

[Mihail]

In VB6 is a module (tool) to create .dll files.
I'm not enough skilled to work with (or I never have need to become ) but is there, in VB6 and, if I remember well it is even in VB5.

 

[spikepl]

You just need to google some more. Here is a link showing that you can create your own library and refer to it http://www.utteraccess.com/forum/Custom-Library-t719513.html but not how. I have never done this in Access. In programming languages it is normal procedure to make your own libraries

 

[nanscombe]

I believe you can add it as a Reference.

1) Press Ctrl + G to take you into the VBA editing screen
2) Choose the Tools -> References menu option
3) When the References form pops up, choose Browse
4) Use the "Files of Type" dropdown list to choose the correct file type
5) Navigate to, and choose, your Access file
6) Click Ok

It should now show up as an entry in the Navigation pane.

Alt + Tab to get back to your main Access screen.

 

[gemma-the-husky]

OP - your last reply to me makes it all a bit clearer.

i have tried to create a dedicated library of access functions

maybe i had the wrong idea but I tried to give each user their own copy of the library, and struggled as access does not seem to allow relative references to a library, and in practice every user used the same copy.

maybe it wasn't a problem and a single shared library is fine - but if so why do we tell everyone to give all users a separate copy of the database - and then not bother when it comes to a library?

you will certainly have some fun getting the library functions to get data from the correct place - ie the library database, or the active database - especially if the library routines use tables from the active database.

but note that if you have hundreds of databases, you will have to spend a lot of time converting all the databases to use the library code. it will be OK going forwards though.

 

[xanksx]

Hi,

Nanscombe's idea is quite close to what I require. I can add a database file which contains my functions as a library in VBA. No copying and pasting the modules. But again, will need to add the library each time that I create a new database.

I could combine this with the template feature (user template not system template since i don't have admin rights on C: ) wherein I can save a template with a reference added. That way, anything new that I create will already have a ticked reference to library database. The only drawback with this is right clicking and creating a new database in a folder doesn't replicate the template. Also, existing databases will not have a reference added.

Thanks
xanksx