Poplar ProductivityWare Articles:
Microsoft Access Programming Tips
by Jennifer Hodgdon

This article is a growing, random assortment of suggestions related to creating database applications in Microsoft Access. It's aimed at developers with an intermediate to advanced knowledge of Access programming, and is not meant to be systematic or a complete tutorial. In fact, the tips here are generally only starting points, and are rather lacking in detail, with the hope that there is enough detail provided that you can figure out the rest by looking in the Access help files.

For a more complete treatment of Access database programming, I recommend the book Access Database Design & Programming, by Steven Roman, published by O'Reilly. It's intended for people who are familiar with the Access user interface, who are ready to branch out into database design and programming, and it is also an excellent reference for a senior-level developer.

If you have any suggestions, comments, questions (including requests for more details), or corrections related to this article please contact Poplar ProductivityWare.

Table of Contents:

• Introduction (above)
• Error Messages with Forms and Linked Tables
• Error Messages on Forms (without linked tables)
• Linking to Outlook/Exchange
• Exporting Reports
• Speeding up Reports and Queries
• Putting Information into Word or Excel
• Copying Information to the Clipboard
• You may also wish to read our companion article on how to distribute databases with the Access Runtime edition

---

Error Messages with Forms and Linked Tables

It is often useful to set up Access databases with a front-end/back-end architecture, which means that you have a "back-end" database that contains the data tables, and a "front-end" database that links to the back-end database's tables, and contains the queries, forms, reports, macros, and modules of the application. This is particularly useful in multi-user environments, because you can then have everyone share the back-end database, which will be located on a network server, while each user runs a private copy of the front-end database from his/her own hard drive.

I recently ran into several strange errors in a rather complex front-end/back-end database:

• "Enter Parameter Value" dialog boxes appeared on the screen during form opening and navigation operations, asking for values that should have been part of the form's controls or data.
• Run-time errors occurred in Visual Basic subroutines connected to a form; the error messages said that fields didn't exist that should have been part of the form's data, and debugging confirmed that the Visual Basic interpreter didn't recognize the form's data fields.
• When clicking buttons or navigating records in forms, there were errors such as "The expression On Click you entered as the event property setting produced the following error: Sub or function not defined", indicating a corrupted event property on the form. I saw this error for On Click, On Current, and On Load events for one particular form, but there wasn't anything obviously wrong with the event properties.
• There were also compilation errors in the Visual Basic code related to non-existent fields that should have been recognized.

The corrupted event property error turned out to be a red herring [for non-native speakers of American English: "red herring" means a misleading piece of evidence that leads you towards the wrong conclusion]. The problem was that (I believe due to an Access bug) certain forms in the database were not recognizing their own data fields. Although I never really figured out why, and obviously can't fix Access bugs, I did eventually find a work-around. It turned out that the Data Source property of the malfunctioning forms, in all cases, was set directly to a linked table. My hypothesis is that Access was not reading the table structures from the other database, and therefore didn't recognize their data fields. So, for each form, I created a Select query based on the linked table, with all of the table's fields included explicitly (not using the "*" wildcard), and set the form's Data Source to the query rather than the table. After leaving Access and restarting the database, all of the problems went away, and Access now recognizes the data fields for the forms. It seems a bit inefficient and silly, but it does seem to work.

---

Error Messages on Forms (without linked tables)

Sometimes you can get a similar error message to the one reported in the previous section, when you cause some event to occur on a form (clicking a button, opening the form, tabbing from field to field, changing data, etc.) and have an event handling subroutine defined in Visual Basic for that event. The error message says: "The expression On Click you entered as the event property setting produced the following error: Method or data member not found". Like the previous example, this error message is a red herring, and it actually indciates that somewhere in the Visual Basic source code for the database, there is an error in syntax or spelling. The easiest way to find the error is to open the Visual Basic editor, and choose "Compile" from the Debug menu. The VB environment should show you where the errors are, and when you can compile with no errors, the problem should go away.

Back to table of contents

---

Linking to Outlook/Exchange

A good database designer knows that the same data should never be included in more than one database table, because it's nearly certain that the different versions of the data will eventually become inconsistent. For this reason, you can easily (using the Link Table wizard from the File / Get External Data menu) create links between Access and many other data repositories, such as other databases (Access, ODBC, etc.), Excel spreadsheets, and Microsoft Outlook or Exchange server. Once you have created the linked table, you can, in principle, use the data pretty much as you would data that was actually located in your Access database (see the tip on error messages with forms and linked tables, however!).

One problem I ran into, in data linking, is that the link between Access and a public contacts folder on an Exchange server was very slow. I had connected the linked table (via a query) to a Combo Box's data source, to create a drop-down list of company contacts, and every time someone clicked on the drop-down list, it took about 30 seconds to display. Obviously, that was not usable.

As a work-around to the slowness issue, what I ended up doing was basically this:

1. Link the Exchange folder to Access, as a linked table.
2. Set up a "snapshot" table in the Access database, with the contents of the linked table (but no live link).
3. Provide a button so that at any time, users can update the snapshot to make sure they have the current contacts. The button basically just runs a Make Table query, which copies the data from the linked table to the snapshot table; the fact that it's a Make Table query rather than an Append or Update query means that the entire table is created from scratch each time the query is run.

On an unrelated note, I also wanted use the Categories field from the linked public Exchange contacts list, but it turns out, according to both my experience and the Microsoft Technical Support person I eventually spoke to about it, that the Categories field is simply not included when Access links to Exchange or Outlook. Why that should be, I don't know, but there's no way around it as far as I know.

Back to table of contents

---

Exporting Reports

The report functionality inside of Access provides a fairly good and powerful way to format data and calculations, as long as all you want to do is view the report on your monitor and print it (including printing to PDF generators, fax modems, and other special printer drivers). However, if you want to share your report with someone else in an electronic format, you will probably want to export it. (See also: this article's tip on how to put data directly into Word or Excel) Here are your basic options (I've omitted options for exporting query and table data, as opposed to formatted reports, since those are fairly straightforward):

Format	Extension	Advantages	Disadvantages
Snapshot	SNP	Captures report just as you see it.	Recipient needs to have Access or the free Access Snapshot Viewer installed on their computer to view -- this is an Access-specific format (you can download the Snapshot Viewer on the Microsoft web site).
Word	RTF	Most Windows computers have software that can read RTF files.	Some formatting is lost, and some information may be truncated (see below for information on how to overcome these issues).
Text	TXT	Every computer should be able to read the file.	Hardly any formatting, so it won't look pretty.
Excel	XLS	None	As far as I can tell, this doesn't work at all -- I have never seen an exported report that came out as a usable spreadsheet.
HTML	HTML	Every computer should be able to read the file.	As with other Microsoft products, the HTML it creates is not very good. Some formatting is lost, and some information may be truncated. Access creates a separate file for each page of the report.

As you can see, none of the options in the table is very satisfactory. Ideally, what you'd probably really want to do is to export the report as a Word or RTF file, and have all the formatting and information come through correctly. Luckily, there are some things you can do to your report design, in order to be able to export it to RTF, and have the exported file look pretty much the same as the Access report:

• Don't use boxes, lines, or graphics. Neither boxes nor lines are exported, so if they are an important part of your report, the RTF version won't look very good; graphics are also omitted. I do not know of a good replacement for boxes; for lines, you can create a long line of underscore (___) characters instead of drawing a line, and this will be exported.
• Make text fields extra-wide. For some reason, text exported to an RTF file often gets truncated, even when it looks fine in the Access report. If you make your fields wider, you can prevent this.
• Be careful about vertical alignment. Access allows you to create reports with components at arbitrary positions on the page. However, an RTF file cannot have text at arbitrary positions -- it must instead be composed of non-overlapping lines of text. So, when Access is trying to export a report to RTF, it has to try to fit the arbitrary locations into strict lines, and sometimes it makes bad choices. To prevent this, make sure that everything at the same vertical position in your report is aligned exactly, with nothing vertically overlapping, and that all text fields are the same height, and using the same font, if possible.

Back to table of contents

---

Speeding up Reports and Queries

One of my clients had an Access database that another developer had created, which (among other issues) had reports that were taking four to eight hours to run, and similarly long-running queries. I was able to reduce all of them to the more normal range of a few seconds to a few minutes, and I thought I would share the other developer's mistakes, so you could learn from them.

• The developer had written several Visual Basic (VB) functions, and used those functions to perform calculations within queries. This may be OK if your query will only call the function once or twice (i.e. if the table the query is based on only has a few records), but with anything more than about 10-20 records, it really starts to take a long time. Instead, build complex expressions using the built-in Access query expression functions.
• The developer had written several VB functions that were formatting fields for the report. Rather than doing that, it is much more efficient to pre-format the fields in the query that the report is based on, or apply formatting properties to the report fields.
• The developer had set up the calculations for some reports using Recordsets and For loops in VB to calculate sums. It is much more efficient to use queries for this purpose.

In summary: Access Visual Basic is quite useful for programming the functionality of button clicks and other events on forms, but don't be tempted to use it to do calculations -- use Access's queries and built-in query expression functions.

Back to table of contents

---

Putting Information into Word or Excel

There are sometimes situations when Access reports are not really what you want, and instead it would be best to create a Word document or Excel spreadsheet that contains your Access data. Access has some built-in functionality to do this:

• You can export the entire data set in a table or query as a Word document table, or as an Excel spreadsheet, using the Office Links toolbar button.
• You can use Word's Mail Merge functionality to create a form letter, mailing labels, or other documents, where fields from an Access table or query are used to fill in the document.

But what if you want to, for instance, take data from Access, and put it into specific cells in an Excel spreadsheet, or specific locations in a Word document, at the click of a button on a form? To do this, you'll need to do some Visual Basic programming. Here are some hints to get you going:

• I suggest creating a template Excel/Word document, rather than trying to create the complete document from scratch each time the button is clicked.
• Use the Visual Basic GetObject function to open your template document -- this works for either Word or Excel:

  Dim obj as Object
  Set obj = GetObject(FullPathToFile)

  My experience has been that to do anything with an Excel document in Access VBA, you need the following additional commands:

  obj.Application.Visible = True
  obj.Windows(1).Visible = True

  It's a bit annoying, since it might be preferable to have Excel run in invisible mode, but I was never able to get it to work. You can turn off screen updating, at least:

  obj.Application.ScreenUpdating = False
  

• In an Excel document, you can name cells using various Insert Names commands. Then, in your Access VBA code, you can move to the cell named MyName, and set its value to MyValue as follows:

  obj.Application.Goto MyName
  obj.Application.ActiveCell.FormulaR1C1 = MyValue

  Presumably, MyValue will come from your Access data.
• In a Word document, you can do something similar by typing in some dummy text where you want your Access data to go, highlighting the text, and naming it using the Insert Bookmark command. Then, in your Access VBA code, you can replace the text as follows:

  obj.Bookmarks(MyName).Select
  obj.Application.Selection.Text=MyValue

• Other Excel and Word VBA commands can also be used -- use obj.Application as a prefix to most commands, since most of them operate on the Application object.
• Be sure to save the document, with a new name (to avoid overwriting the template) when you are done:

  obj.SaveAs PathAndNewName ' Excel or Word
  obj.Application.Quit 'exit Excel
  obj.Close 'exit Word.

Back to table of contents

---

Copying Information to the Clipboard

Access forms provide end users with the ability to copy the information in a particular field to the Windows clipboard, so that it can then be pasted into another application. By selecting one or more entire records from a form or table, users can also copy and paste that information, though the format is not necessarily useful for all applications. Instead, in many applications, it's useful to copy custom text based on various fields of or more records, and Access doesn't readily supply this functionality.

The method I have used to copy custom information to the clipboard is as follows:

1. Create a "dummy" form (which I'll call "frmDummy" here) for use in copy and paste operations, and put a Text Box on the form (which I'll call "txtCopy").
2. Use Visual Basic to gather the information needed from the record or records into a single String variable (which I'll call "strToCopy"), using DAO Dataset methods, or simple Form access if only the current record's information is needed.
3. From Visual Basic, open the dummy form (DoCmd.OpenForm "frmDummy"), and set the Text property of the Text Box to the String variable's contents (Screen.ActiveForm.txtCopy = strToCopy).
4. Call the Text Box's SetFocus method to select the text in the Text Box (Screen.ActiveForm.txtCopy.SetFocus).
5. Use Access's global Copy command to copy the selected text to the clipboard (DoCmd.RunCommand acCmdCopy).

Back to table of contents

---