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

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

This is the second in a series of two articles containing an advanced example that demonstrates how to use Visual Basic to request data from Microsoft Access by using dynamic data exchange (DDE).

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

The following Visual Basic example demonstrates how to use DDE to request information by using SQL statements and the rich set of items supported by Microsoft Access to navigate tables. Further, a subroutine that assists in parsing data from the tab-delimited records that are requested is provided.

Specifically, the example does the following:

1. Gets the number of records and fields in the table using the "FirstRow" and "FieldCount" items.

2. Creates an array to store the records when they are retrieved.

3. Requests the data from a table, one record at a time, using the "FirstRow" and "NextRow" items.

4. Requests a list of field names using the "FieldNames" item.

5. Uses a subroutine to parse each tab-delimited record requested from Microsoft Access and places the data in the array mentioned above. (A separate array holds the field names).

6. This step is optional. Once all the data is placed in the array, a Visual Basic grid control will be populated with the array contents. Further, the grid columns will be populated with the names of the fields.

To use this example, create a new Visual Basic program with a text box, a command button, and a grid control with the following properties:

   Text Box
   --------
      Name: Text1

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

   Grid
   ----
      Name: Grid1

Double-click the command button and type the code that follows between the Sub Command1_Click () and End Sub lines.

NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

   Dim CntRows, CntCols As Integer ' Row and column count in the table.
   Dim Row, Col As Integer         ' Current row/column being filled.
   Dim Record As String            ' Record read from employees table.

   ' Get the number of records in the Employees Table.
   Text1.LinkTopic = "MSACCESS|Northwind;SQL Select Count(*) As _
      EmployeeCount From Employees;"

   Text1.LinkItem = "FirstRow"   ' Setup to get the first row of data.
   Text1.LinkMode = 2            ' Establish a manual link.
   Text1.LinkRequest             ' Request the data into the Text1.
   Text1.LinkMode = 0            ' Terminate the link.
   CntRows = Val(Text1.Text)     ' Convert the result to a number.

   ' Establish a DDE Link to the Employees table.
   Text1.LinkTopic = "MSACCESS|Northwind;SQL Select * From Employees;"
   Text1.LinkMode = 2            ' Establish a manual link.

   ' Get the number of columns in the Employees Table.
   Text1.LinkItem = "FieldCount" ' Setup to get count of fields/cols.
   Text1.LinkRequest             ' Request the data into Text1.
   CntCols = Val(Text1.Text)     ' Convert the result to a number.

   ' Create the arrays to hold employee information and field names.
   ReDim Employees(CntRows, CntCols) As String
   ReDim FieldNames(1, CntCols) As String

   ' Get the field names in the Employees table from Microsoft Access.
   Text1.LinkItem = "FieldNames" ' Setup to get the field names.
   Text1.LinkRequest             ' Request the data into Text1.

   ' Parse the tab delimited list of field names and copy it into the
   ' .. FieldNames array. (ParseRecord subroutine is listed latter).
   ParseRecord FieldNames(), 1, (Text1.Text)

   ' Request each tab delimited record of data one at a time.
   For Row = 1 To CntRows
      If Row = 1 Then
         Text1.LinkItem = "FirstRow"  ' Setup for the first record.
      Else
         Text1.LinkItem = "NextRow"   ' Setup for the next record.
      End If
      Text1.LinkRequest          ' Request the data into Text1.

      ' Parse the tab delimited record and store it in Employees array.
      ParseRecord Employees(), Row, (Text1.Text)
   Next Row

   Text1.LinkMode = 0  ' Terminate link, arrays are filled with data.

   ' OPTIONAL: The following code populates a grid control with the
   ' contents of the Employees and FieldNames arrays.

   ' Setup the Grid with the correct number of Rows and Cols.
   Grid1.Rows = CntRows + 1      ' Add 1 for the row selector buttons
   Grid1.Cols = CntCols + 1      ' Add 1 for the column header buttons

   ' Fill the Grid column header with the data in the FieldNames array.
   Grid1.Row = 0                  ' Move to the column header row.
   For Col = 1 To CntCols
      Grid1.Col = Col                   ' Move to the column
      Grid1.Text = FieldNames(1, Col)   ' .. and fill it with data.
   Next Col

   ' Fill the Grid control rows with the data in the Employees array.
   For Row = 1 To CntRows
      Grid1.Row = Row                    ' Move to the row.
      For Col = 1 To CntCols
        Grid1.Col = Col                  ' Move to the column.
        Grid1.Text = Employees(Row, Col) ' Fill it with array data.
      Next Col
   Next Row
   End Sub

   Array()  - A two-dimensional array of strings to store data into.
   Row      - The row position in the array to store the parsed data.
   Record   - A string with tab delimited information to parse.

   Sub ParseRecord (Array() As String, ByVal Row As Integer, _
      ByVal Record As String)
      Dim Start As Integer    ' Start position of field in record.
      Dim TabStop As Integer  ' Position in the record of the next tab.
      Dim CntCols As Integer  ' The number of columns in the Array.
      Dim Col As Integer      ' The current column (field) being parsed.

      CntCols = UBound(array, 2)   ' Get count of columns in the array.
      Start = 1       ' Start parsing with first character.

      ' Find the first tab stop (ASCII character 9) in the record.
      TabStop = InStr(Start, Record, Chr$(9), 0)

      ' Loop until we fill up all but the last column (Col < CntCols)
      ' .. or until there are no more columns to read (TabStop <> 0 means
      ' .. that no tab was found).
      Col = 1
      While Col < CntCols And TabStop <> 0
         ' Extract the field from the string and store it in the array.
         Array(Row, Col) = Mid$(Record, Start, TabStop - Start)

         Start = TabStop + 1        ' Increment start to next tab stop.
         TabStop = InStr(Start, Record, Chr$(9), 0) ' Find next tab stop.
         Col = Col + 1              ' Increment to next column in array.
      Wend
      Array(Row, CntCols) = Mid$(Record, Start)  ' Get last col of data.
   End Sub

REFERENCES

For further information about using Microsoft Access as a DDE server, search the Help Index for "DDE."