ACC: Part 1: DDE in Visual Basic to Request MS Access Data

ID: Q98791

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
Microsoft Visual Basic versions 3.0, 4.0

SUMMARY

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

This article shows you how to use Microsoft Visual Basic to request data from Microsoft Access using dynamic data exchange (DDE).

For information that goes into greater detail and demonstrates how to parse the requested data in a Visual Basic array and then populate a grid control with the data, please see the following article in the Microsoft Access Knowledge Base:

   ARTICLE-ID: Q99405
   TITLE:      ACC: Part 2 DDE in Visual Basic to Request MS Access Data

MORE INFORMATION

This article provides a code example demonstrating how to request information from the Employees table in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

The following code example demonstrates how to fill a Visual Basic text box with the entire contents of a Microsoft Access table, including field names.

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

In Visual Basic, create a new form, add the following controls, and set the appropriate properties for each control:

   Text Box
   -------------------------------------------------
      Name: Text1
      Multiline: True
      Scrollbars: 3 - Both (vertical and horizontal)

   Command Button
   -----------------
      Name: Command1

NOTE: Size the text box large so that data can be seen and scrolled easily following the DDE request.

Double click the command button and enter the following code between the "Sub Command1_Click ()" and "End Sub" lines:

   Sub Command1_Click()
      Text1.LinkTopic = "MSACCESS|NORTHWIND;TABLE Employees"
      (or NWIND in Microsoft Access versions 1.x and 2.0.)
      Text1.LinkMode = 2     ' Establish a manual DDE link to Access
      Text1.LinkItem = "All" ' Setup the DDE request item to get ALL the
                             ' .. data from the Employees table,
                             ' .. including field names.
      Text1.LinkRequest      ' Request the data into the Text1 text box
      Text1.LinkMode = 0     ' Terminate the DDE link to Access
   End Sub

In this example, the first line specifies "MSACCESS" as the application to communicate with and "NORTHWIND;TABLE Employees" (or NWIND in Microsoft Access versions 1.x and 2.0) as the topic of the DDE conversation. We could also specify an SQL statement as the topic of the conversation. The following line of code is functionally equivalent to the first line above:

   Text1.LinkTopic = "MSACCESS|NORTHWIND;SQL Select * From Employees;"
   (or NWIND in Microsoft Access versions 1.x and 2.0)

In this example, the item "All" tells Microsoft Access to supply Visual Basic with the entire contents of the Topic, including field names. Microsoft Access supports a rich set of other items and topics. For a list of supported topics and items, please see the following article in the Microsoft Access Knowledge Base:

   ARTICLE-ID: Q89586
   TITLE:      ACC: Microsoft Access as a DDE Server

When you run this example, be sure that Microsoft Access is running and has the Northwind.mdb database opened (or NWIND in Microsoft Access versions 1.x and 2.0). When you run this code, the text box will be filled with the entire contents of the Employees table. Each field will be delimited by a tab character, Chr$(9), and each record row will end with a newline character (a carriage return followed by a linefeed character).

Additional query words: vb b_vbasic

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

Last Reviewed: November 20, 1998