OFF97: Using Automation to Run Microsoft Access

ID: Q167472

The information in this article applies to:

Microsoft Word 97 for Windows

SUMMARY

Advanced: Requires expert coding, interoperability, and multi-user skills.

This article demonstrates how to use Automation from a Microsoft Office 97 program with Visual Basic for Applications to start Microsoft Access, and then display a new category form from the Northwind Sample database. It also uses Visual Basic for Applications to fill some fields on that form, and then saves the record.

This article assumes that you are familiar with Visual Basic for Applications and creating Microsoft Access programs using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of "Building Applications with Microsoft Access."

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

To use the example, do the following:

1. In Word 97, create a new document.

   -or-

   In Microsoft Excel 97, create a new workbook.

   -or-

   In PowerPoint 97, create a new presentation.

2. On the Tools menu, click macro, and then click Visual Basic Editor to

   Open the Visual Basic Editor.

3. On the Insert menu, click Module to insert a new module.

4. On the Tools menu, click References

5. In the Available References box, click Microsoft Access 8.0 Object

   Library.

   If it doesn't appear in the list, click Browse and search for
   Msacc8.olb, which is installed by default in the Program Files\Microsoft
   Office\Office folder.

6. Type the following code in the Code window for the module.

   NOTE: In the following sample code, an underscore (_) at the
   end of a line is used as a line-continuation character.

    Public Sub OpenNwindCategoryForm()

      ' Create a variable that will refer to Microsoft Access.
      Dim MyAccessObject As Access.Application
      ' Create variables for file pathing.
      Dim sPsep, DBPath As String

      ' Set DBPath to the path of Northwind database example.
      ' NOTE: Because Microsoft PowerPoint Visual Basic for
      '       Applications does not recognize the PathSeparator
      '       function, check for the program that is running
      '       this routine. For Microsoft Word and Microsoft Excel,
      '       use the PathSeparator function.
      If Application.Name <> "Microsoft PowerPoint" Then
         ' The PathSeparator function returns the path separator,
         ' which is Operating System Platform specific.)
         sPsep = Application.PathSeparator
      Else
         ' For Microsoft PowerPoint 97, use the following line of
         ' code for the path separator.
         sPsep = "\"
      End If
      DBPath = Application.Path & sPsep & "Samples" & sPsep _
      & "Northwind.mdb"

      On Error GoTo ErrHandler
      ' Create an instance of the Access application object.
      Set MyAccessObject = CreateObject("Access.Application.8")
      With MyAccessObject
         .OpenCurrentDatabase DBPath  'Opens the Northwind file.
         .DoCmd.OpenForm "Categories"  'Opens the Employees form.
         'Set the caption Property of the form
         .Forms!Categories.Caption = "Test OLE Automation - Add _
            Categories"
         ' Add a new record to the form...
         ' acDataForm and acNewRec are pre-defined constants
         ' within the Access Object Library.
         .DoCmd.GoToRecord acDataForm, "Categories", acNewRec
         ' Set the field Category Name to a value.
         .Forms!Categories.CategoryName = _
          InputBox("Enter a category name", "Category name", _
             "A name")
         ' Set the field Description to a value.
         .Forms!Categories.Description = _
          InputBox("Enter a description", "Description", _
             "A description")
         ' Save the record...
         ' acCmdSaveRecord is a pre-defined constant
         ' within the Access Object Library.
         .DoCmd.RunCommand acCmdSaveRecord
         .Visible = True   ' Maximize Microsoft Access.
      End With
    ErrHandler:      ' < NOTE: This line must be left aligned!
         ' Error handler displays the error message
         ' and then quits the program.
         If Err <> 0 Then
            MsgBox Err.Description, Title:="Test OLE Automation"
            MyAccessObject.Quit   ' Close Microsoft Access.
         End If
         Set MyAccessObject = Nothing ' Set the object variable to
                                      ' nothing

    End Sub

7. Compile the procedure and make sure there are no errors.

8. On the Run menu, click Run Sub/User Form to run the procedure.

This starts Microsoft Access in the background, prompts the user for the name and description of the category, and then displays the Category form in Microsoft Access with the entered information.

REFERENCES

For more information about using Automation with Microsoft Access from another Microsoft Office program, please see the "Mastering Office 97 Development" compact disc. You can find information about this compact disc on the Microsoft Web page at the following address:

   http://msdn.microsoft.com/mastering/

NOTE: Because the Microsoft Web site is constantly updated, the site address may change without notice. If this occurs, link to the Microsoft home page at the following address:

   http://www.microsoft.com/

For more information about using Automation to work with other programs while in Microsoft Access 97, click the Office Assistant, type "Automation," click Search, and then click to view the topic.

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Access Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office
               Program or Component

For more information about using Automation to work with other programs while in Microsoft Access 97, click the Index tab in Microsoft Access Help, type the following text

 automation overview

and then double-click the selected text to go to the "Automation Overview" topic.

Additional query words: wordcon inf word8 word97 PgmObj 8.00 8.0 vb vbe vba OLE OFF97 8.00 XL97 ACC97 PPT97

Keywords          : kbinterop kbprg 
Version           : 97
Platform          : WINDOWS
Issue type        : kbinfo

Last Reviewed: April 5, 1999