OFF97: Using Automation to Run Microsoft Access

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. 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.