1

I have multiple MS-Access 2003 project with some VBA code and there are some redundant functions that I would like to have in the same file. I thought about saving it in an external .bas module and loading it into all my projects. Is it possible? If so, how can we implement this and will I have to change the actual (simple) calls to my functions?

Kate Gregory
  • 18,808
  • 8
  • 56
  • 85
dan
  • 3,439
  • 17
  • 54
  • 82

1 Answers1

3

You can use the undocumented:

Application.SaveAsText acModule, "module1", "module1x.bas"
Application.LoadFromText acModule, "module1y", "module1x.bas"

Or you can simply save and load manually with right-click on the module name in the code window.

Finally, you can reference an Access application in the same way as any other reference, and then use the code. The file type *.mda was the library type. As far as I know, from 2007, you can create an *.accda library type.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Not sure about the link provided. Didn't see anything about *.mda. The code does work however, the module is added to the project. But in fact, if I call for a function into the module, the code won't run at all, giving me an error message saying the function can't be found (even before the Application.LoadFromText can load the module). What I want is adding a *.bas file as reference. I have a ton of projects that make use of the same functions and I don't want to edit those functions in 10 different files. – dan Nov 09 '12 at 21:41
  • Create a library reference, you can use the mda extension if you want http://office.microsoft.com/en-us/access-help/create-your-own-wizards-builders-and-add-ins-HP005188987.aspx?CTT=1 – Fionnuala Nov 09 '12 at 21:49
  • The 4 links in the page you provided are dead. Here is what I found on the MSDN: http://msdn.microsoft.com/en-us/library/office/aa156981%28v=office.10%29.aspx. Thanks for letting me know about MDA, it should get the job done. – dan Nov 12 '12 at 15:08
  • 1
    It works like a charm. For some reason the FileSystemObject variable type isn't recognized anymore, I have to declare them in the module as `Variant` and then set 'em with `CreateObject("Scripting.FileSystemObject")`. But it works perfectly fine. – dan Nov 12 '12 at 15:18
  • If the filesystemobject is not recognised, it is because you do not have a reference to the Windows Script Host Object Model - it is not a standard reference and must be added for each project. However, for the most part, it is better to use late binding with external libraries. – Fionnuala Nov 12 '12 at 15:21
  • I guess it is not possible to add a reference within the code. I'm back to my problem of relative paths, the database will be copied on multiple servers with UNC paths, which means I will have to set the reference one by one on each copy. – dan Nov 12 '12 at 15:26
  • The answer is there: http://stackoverflow.com/questions/1389705/access-vba-relative-file-references. Thanks again. – dan Nov 12 '12 at 15:37