Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Can you move VBA code out into a separate container?

Posted on 2011-10-05
11
Medium Priority
?
326 Views
Last Modified: 2012-05-12
I am currently developing and/or supporting about 6 different app's, and going crazy with slightly different versions of VBA routines. Can you pull the VBA out of an mdb (2003) and put it in another ?container?, and link to it with a Reference?

If so, any pitfalls? Other than having to completely re-test EVERYTHING to find & fix all the spots that will be broken, due to the coding differences?

I seem to remember seeing an article about this in the past, but can't find it now.

Thanks
0
Comment
Question by:mlagrange
  • 5
  • 4
  • 2
11 Comments
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 36918169
Yes, you certainly can do that.  It's called a library MDB and by convention has a MDA extention.

  This is how the wizards and add-ins work.  

  The only thing you need to be carefull of is at times it's important to know where the code is running.  You use CodeDB(), which is like CurrentDB() except that it points to your MDA and not the current DB.

Jim.
0
 
LVL 75
ID: 36918614
Actually, if CodeDB() is used within the 'currentdb', then it returns the (object) Name of the CurrentDB, which in this case is the same as the CurrentDB Method.

mx

0
 
LVL 59
ID: 36919160
Yes, I didn't say that right; it points to the DB that the code is currently executing in.

 I only use that in MDA's, which is why I goofed myself up.

Thanks for catching that!
Jim.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mlagrange
ID: 36919226
Do mda's still work in Access 2010?  Any problems, documented or otherwise?


0
 
LVL 75
ID: 36919323
"Do mda's still work in Access 2010? "
Definitely. For Sure. I have several utilities that are MDA's ... and all work fine.
No specific issues at all.  It's one of the easiest things to do in Access.  An MDA is literally an MDB with an MDA extension.

mx
0
 

Author Comment

by:mlagrange
ID: 36919333
Does this cause any problems during development?
I guess you would have your app front end open, and possibly the VBA Editor, and then the mda with your code, and then THAT VBA editor?  
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 36919381
Not quite.  Lets say you have an MDB that has a Reference to your MDA - that contains all your cool special commonly used slick code.

When you have your MDB open, you can ... get to the code in the MDA ... and you can even make **Temporary** changes to test stuff.  BUT ... IMPORTANT ... when you close your MDB, any changes made to the MDA will be **Lost*.  

So ... to actually modify code or other objects in your MDA, you must open it by itself to do so.  Back circa A97 and prior, this was not the case. You could actually modify and save code in the MDA 'live'.  But, that got changed in a later version ... and remains in effect today.

mx
0
 

Author Comment

by:mlagrange
ID: 36919415
Ok, I'm going to give a shot!

Thanks, Guys
0
 
LVL 75
ID: 36919770
ok ... I found what I was looking for.  Now, these are mainly about creating MDA's to use as an Add In - you will show up on the (A2003) Add-Ins menu - NOT to be confused with the Add In Ribbon in A2010 - I CAN'T BELIEVE THEY NAMED IT THE SAME (Yes I am shouting, lol).


How To Create:
http://www.databasedev.co.uk/access-add-ins.html



Must Have AddIn:
http://mztools.com/v3/mztools3.aspx

Good list of Add-Ins:
http://www.team-moeller.de/en/?Add-Ins

All time #1 must have AddIn (almost free - pays for itself in first use):
http://www.rickworld.com/products.html#Find%20and%20Replace%209.0

Very cool and free AddIn:
http://www.oaltd.co.uk/Indenter/Default.htm

Not specifically AddIn's per se, but all free and off the chart:

http://www.lebans.com

http://www.everythingaccess.com/tutorials.asp?ID=A-new-method-for-disabling-the-Mouse-Scroll-Wheel-in-Access-forms

And as rocki noted, PetersSoftware has several free tools ... scroll down on the home page to 'Free Downloads:'

More references:

http://www.fmsinc.com/MicrosoftAccess/help.html

http://allenbrowne.com/Access2007.html

http://www.fmsinc.com/TPapers/#CurrentAccess

http://www.mvps.org/access/

http://www.vb123.com/

http://www.fontstuff.com/access/
Capture1.gif
0
 

Author Comment

by:mlagrange
ID: 36919802
WOW! A veritable GOLD MINE of links!

Thanks, Thanks, Thanks!

0
 
LVL 75
ID: 36919842
Seriously ... some GREAT stuff.

mx
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question