ACC: VB Example to Open an MS Access Database via DDE

ID: Q128811

The information in this article applies to:

Microsoft Access versions 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how you can use dynamic data exchange (DDE) in Microsoft Visual Basic versions 3.0 or 4.0 to request information from a Microsoft Access database. By using DDE, you can manipulate Microsoft Access objects that are not available through Microsoft Visual Basic's data access features. For example, you can run a Microsoft Access macro to print a report or to open a form from Microsoft Visual Basic.

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

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

To use DDE in Microsoft Visual Basic to request information from a Microsoft Access database, follow these steps.

NOTE: This example does not verify if Microsoft Access is running (it assumes Microsoft Access is open at the time you run DDE commands). Also, the code does not include any error trapping. You may want to modify this example to include these enhancements in your project.

1. Start a new project in Microsoft Visual Basic. Note that Form1 is

   created by default.

2. Add two text boxes named AppItems and DBItems to Form1.

3. Add a command button named RunAccessDDE.

4. Place the following code in the RunAccessDDE button's Click event.

    Private Sub Command1_Click()
        Const LINK_MANUAL = 2, LINK_NONE = 0
        Dim Cmd

        ' Create a string containing a Microsoft Access macro command.
        Cmd = "[OPENDATABASE ""C:\MSOFFICE\ACCESS\SAMPLES\NorthWind.MDB""]"

        NOTE: The sample database is called NWIND.MDB in versions 1.1
        and 2.0.

        If AppItems.LinkMode = LINK_NONE Then

           ' Set the DDE server application and topic to MSACCESS|SYSTEM.
           AppItems.LinkTopic = "MSACCESS|SYSTEM"

           ' Specify the information passed to the AppItems text box.
           AppItems.LinkItem = "SysItems"

           ' Set the LinkMode to a valid non-zero setting which causes
           ' Visual Basic to initiate a link to the program defined in
           ' the LinkTopic. The LINK_MANUAL constant value of 2 ensures
           ' that the link is not updated until the LinkRequest method
           ' is invoked.
           AppItems.LinkMode = LINK_MANUAL

           ' Update the contents of the AppItems text box.
           AppItems.LinkRequest

           ' Run a valid DDE operation for Microsoft Access, such as
           ' opening the sample database NorthWind.MDB.
           AppItems.LinkExecute Cmd

           If DBItems.LinkMode = LINK_NONE Then

           ' Set the DDE Server and topic to MSACCESS|NorthWind.
           DBItems.LinkTopic = "MSACCESS|NorthWind"

           ' Pass a list of Microsoft Access tables to DBItems text box.
           DBItems.LinkItem = "TableList"
           ' Set the LinkMode to update the DBItems text box only when the
           ' LinkRequest method is invoked.
           DBItems.LinkMode = LINK_MANUAL

           ' Update the contents of the DBItems text box.
           DBItems.LinkRequest
           'Close the DDE link for the DBItems text box.
           DBItems.LinkMode = LINK_NONE
           End If

           ' Run a valid DDE operation for Microsoft Access, such as
           ' closing the sample database NorthWind.MDB.
           AppItems.LinkExecute "[CloseDatabase]"

           ' Close the DDE link for the AppItems text box.
           AppItems.LinkMode = LINK_NONE
        End If

    End Sub

5. Start the project, and click the RunAccessDDE button. Note that the

   AppItems text box displays a tab-delimited string of system topics
   supported in Microsoft Access. The DBItems text box displays a tab-
   delimited string of table names from the sample database NorthWind.MDB.

NOTE: Microsoft Access does not support the LinkPoke method for any object other than a SQL Topic. For instance, you cannot LinkPoke data into a Microsoft Access table or form.

REFERENCES

For more information on using DDE in Microsoft Visual Basic, search for "DDE" using the Microsoft Visual Basic Help menu.

For more information on DDE topics and items available in Microsoft Access, search the Help Index for "DDE."

Keywords          : kbinterop
Version           : 1.10 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo

Last Reviewed: November 20, 1998