The Database Journal
03/10/2006
Adjusting Autonumber Increments

03/10/2006
Moving Access Data to Microsoft Excel

03/10/2006
Exporting Access Data with TransferSpreadsheet

03/10/2006
Export Access Data with VBA

03/13/2006
Data Layout in Microsoft Excel

03/17/2006
Excel Text to Number Conversion

03/21/2006
Transposing Excel Ranges

03/25/2006
Access VBA Timer Event

03/29/2006
What is MySQL?

04/02/2006
Microsoft Access Parameter Query Tutorial Video

04/06/2006
How does PHP Work?

04/10/2006
Microsoft Access VBA Tools - References

Click here to view all articles.

Database Design
Website Design
Search Engine Marketing
Home
Contact Us
About Us
Database Journal
Newsletters
Database Newsletter

Microsoft Access VBA Tools - References

Microsoft Access Databases often get shuffled around from one computer to another. It's all a part of the work environment and how databases get used - one person develops it, then moves it around so other people can use it. Sounds easy!

Well, not always ... there's this little bit about "references" to deal with!

Think of a reference as a small map. Basically, when you use certain types of VBA Code, you need to references certain types of files. So you have to tell Microsoft Access two things: What file you're using, and where it's located.

Lucky for you, there's an easy way to do this, with the references window in a Microsoft Access module (see Figure 1)!

Figure 1

First, let's cover why you would actually need a reference! Let's say you're writing a little bit of code to allow Microsoft Access to send email through Microsoft Outlook. Well, in order to do that, you need to put Microsoft Outlook code in a VBA module. Essentially, you'll need for Access to "speak Outlook" - almost as if it's a slightly different dialect of the VBA language.

Think of the reference as a handy translation book that you can give Microsoft Access so it understands what you're trying to accomplish. You need to point it to a file that handles all of the Microsoft Outlook code - without pointing it there, Microsoft Access won't know what to do, because it doesn't automatically recognize the code you'd be using. This is why you get general errors in your code (ie: Function not defined).

So in this case, you would open the References window by opening up a new, blank module (or open an existing module) and clicking on Tools, then References. Inside of this window, you'd enable Microsoft Outlook Automation in that list (see Figure 2).

Figure 2

Now, your Microsoft Access database will recognize the code that manipulates Microsoft Outlook. You've told Access where to look to understand the code you've written, and since Access knows where to find that file, it will now how to understand your code!

Something to watch out for, though - sometimes you'll distribute a database and the file that Access needs to look at will be in a different location on the destination computer. In this case, you've told Access to look at a specific directory, but that directory may not exist, or the file may not be present there. When this happens, you'll get an error to the effect of, "MISSING REFERENCE: blah blah blah". This indicates that there's a reference your project is using that cannot be found. To fix it, just go into Tools and References, then uncheck the missing reference (it will be labeled "MISSING" - you won't be able to um "miss" it!). Now you can either check the appropriate reference, or diagnose the problem further!

So what's the big deal about references? Well, I'll tell you how I use them every day. Using code that exports a Microsoft Access Query into a formatted Microsoft Word document means I need to include the Microsoft Word reference. That allows me to spit out a completely formatted Word document at the press of a button! Using the Microsoft Outlook reference allows me to manipulate Outlook objects from Microsoft Access. By including a reference to CDO, I can actually extract the text inside of specific Outlook messages in specific folders and dump their results into Microsoft Access. References pretty much give you a free ticket to do anything!

Enjoy!


©2014 Blue Moose Technology, LLC

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Microsoft Access VBA Tools - References