Automation Problems and How to Avoid Them | Expert One-on-One Microsoft Access Application Development

When working with Access 2000 databases in higher versions of Access, you may encounter some problems. The next section will help you avoid these problems or deal with them if they occur.

References

You can work with an Access 2000 database (without converting it) in Access 2002 and Access 2003. This is a very handy feature, especially when users with different Office versions work with the same database. However, there are a few gotchas, especially when you work with other Office components. When you set a reference to the object model of another Office component, such as Word, you will see a specific Office version in the References dialog. For example, when you set a reference to Word from an Access 2000 database while working in Office 2000, you set a reference to Word 9.0. When you set a reference to Word from an Access 2000 database while working in Office XP, however, you set a reference to Word 10.

The Word 10 (XP) reference will work fine for Automation code so long as you are operating in Office XP, but if you open the same database in Office 2000, on opening the database you will get a mysterious error referencing the Format function (or some other perfectly legitimate function), and if you open the References dialog, you will see a reference to the Word 10.0 object library (as shown in Figure 11.24), marked “MISSING.”

click to expand
Figure 11.24

Note that the references to Access and Office (which were set to v. 10.0 when the database was opened in Office XP) were automatically downgraded to v. 9.0. In my experience, the Word and Outlook references aren’t downgraded automatically, while the Access, Office, and Excel references are usually downgraded automatically. To temporarily fix this problem, you can uncheck the missing v. 10.0 reference, and check the v. 9.0 reference. This will fix the problem so long as you don’t open the database in Access 2002 again. For a permanent fix (necessary when the database is shared among users with different Office versions), use the following steps:

Copy the Office 9.0 type libraries (or at least the Word and Outlook ones) to a separate folder, in a location that is accessible when you are working in higher Office versions.
Open the Access 2000 database in Access 2002 (Office XP), uncheck the Word 10.0 and/or Outlook 10.0 references if they are checked, and set references to the Word 9.0 and/or Outlook 9.0 object libraries.

After this, the references will remain set to the v. 9.0 object libraries regardless of which version of Office is in use, and there won’t be any reference errors.

The process is the same for Access 2003; for that Office version, the references are v. 11.0.

Getting the Documents Directory from Word

In my Automation code, I retrieve the User Templates folder from the Word Options dialog using the following line of code:

TemplateDir = pgappWord.Options.DefaultFilePath(wdUserTemplatesPath)

This line of code reliably retrieves the folder specified in the File Locations path of the user’s Word Options dialog (shown in Figure 11.25).

click to expand
Figure 11.25

There is another named constant that should retrieve the location of the Documents folder from the same dialog:

DocsDir = pgappWord.Options.DefaultFilePath(wdDocumentsPath)

In my experience, this line of code, when used in a VBA procedure, more often than not returns the current folder, not the Document folder. Because of this problem, I generally store the Documents path in a field in tblInfo, a little table containing miscellaneous information that I use in most of my databases. The first of the following two functions checks whether the path entered into the DocsPath textbox on the main menu is valid, and if so, the second function retrieves the path from tblInfo, defaulting to C:\My Documents if the field is blank. It also checks whether there is an Access Merge subfolder under the Documents folder, and creates one if needed.

 Public Function CheckDocsDir() As Boolean On Error GoTo ErrorHandler        Set dbs = CurrentDb    Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset)        With rst       .MoveFirst       strFolderPath = Nz(![DocsPath])       If strFolderPath = "" Then          strFolderPath = "C:\My Documents\"       End If    End With           ‘Test the validity of the folder path    Debug.Print "Folder path: " & strFolderPath        If strFolderPath = "" Then       strTitle = "No path entered"       strPrompt = "Please enter a Docs folder path on the main menu"       MsgBox strPrompt, vbOKOnly + vbCritical, strTitle       CheckDocsDir = False       GoTo ErrorHandlerExit    Else       Set fso = CreateObject("Scripting.FileSystemObject")       If fso.FolderExists(strFolderPath) = False Then          strTitle = "Folder path invalid"          strPrompt = "Please enter a valid Docs folder path on the main menu"          MsgBox strPrompt, vbOKOnly + vbCritical, strTitle          GoTo ErrorHandlerExit          CheckDocsDir = False       End If    End If        CheckDocsDir = True     ErrorHandlerExit:    Exit Function ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Function Public Function GetDocsDir() As String On Error GoTo ErrorHandler        Dim strFolderPath As String        Set dbs = CurrentDb    Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset)        With rst       .MoveFirst       strFolderPath = Nz(![DocsPath])       If strFolderPath = "" Then          strFolderPath = "C:\My Documents\"       End If    End With           ‘Test the validity of the folder path    Debug.Print "Folder path: " & strFolderPath        If strFolderPath = "" Then       strTitle = "No path entered"       strPrompt = "Please enter a Docs folder path on the main menu"       MsgBox strPrompt, vbOKOnly + vbCritical, strTitle       GoTo ErrorHandlerExit    Else       Set fso = CreateObject("Scripting.FileSystemObject")       If fso.FolderExists(strFolderPath) = False Then          strTitle = "Folder path invalid"          strPrompt = "Please enter a valid Docs folder path on the main menu"          MsgBox strPrompt, vbOKOnly + vbCritical, strTitle          GoTo ErrorHandlerExit       End If    End If        strDocsDir = strFolderPath & "Access Merge\"    Debug.Print "Access Merge subfolder: " & strDocsDir        ‘Test for existence of Access Merge subfolder, and create    ‘it if it is not found    Set fso = CreateObject("Scripting.FileSystemObject")    If Not fso.FolderExists(strDocsDir) Then       ‘Access Merge subfolder does not exist; create it       fso.CreateFolder strDocsDir    End If        GetDocsDir = strDocsDir     ErrorHandlerExit:    Exit Function ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Function

The CheckDocsDir and GetDocsDir functions are used whenever the code in the Word Data Exchange sample database needs to save a document, using the following code segment:

   If CheckDocsDir = False Then       GoTo ErrorHandlerExit    End If    strDocsPath = GetDocsDir

If you want to test whether the Word wdDocumentsPath named constant correctly retrieves the Documents path on your computer, run the DocsDir function in the Immediate window by typing:

?DocsDir

and then typing:

?GetDocsDir

and see what they return. On my computer, the DocsDir function generally returns D:\Documents\_Writing\Wiley\Chapters (the current path), while the GetDocsDir function returns D:\Documents\Access Merge, which is correct on my computer.