How to Use DDE to Display Microsoft Access Data in VB 3.0

ID: Q96845

The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, version 2.0
Microsoft Professional Toolkit for Microsoft Visual Basic programming system for Windows, version 1.0

SUMMARY

This article demonstrates how to use DDE to obtain and display data from a Microsoft Access database. While Microsoft Access does not support poking data into a database, it does provide several LinkTopics, so you can get information out from a database.
Using supported LinkTopics, you can receive:

The contents of a Microsoft Access table

The result of a stored query in the Microsoft Access database

The result of a SQL expression that you pass to Microsoft Access

Specifics about a Microsoft Access database

MORE INFORMATION

Below you'll find example code and a detailed list of the LinkTopics and LinkItems supported by Microsoft Access. For the most updated list of LinkTopics and LinkItems supported by Microsoft Access, query on the following words in the Microsoft Knowledge Base:

access and DDE and item and topic and server

LinkTopics Supported

Here are the LinkTopics supported by Microsoft Access:


System              : List of supported LinkTopics.
<Database>          : <Database> is the filename of an existing database.
<TableName>         : <TableName> is a table within the specified database.
<QueryName>         : <QueryName> is a query within the specified database.
SQL <SQL Statement> : Result of a SQL Query where <SQL Statement> is a
                      valid SQL expression.

LinkItems Supported for Each LinkTopic

Here are the LinkItems supported for each LinkTopic and the results they return

System:


   SysItems    - List of LinkItems supported by the System LinkTopic.
   Formats     - List of formats Microsoft Access can post to the clipboard.
   Status      - Busy or Ready.
   Topics      - List of all open databases.
   <Macro>     - Name of a macro to be executed.

Database:


   TableList   - List of tables
   QueryList   - List of queries
   MacroList   - List of scripts
   ReportList  - List of reports
   FormList    - List of forms
   ModuleList  - List of modules
   <Macro>     - The name of a macro to be executed.

Table Name, Query Name, and SQL <expression>:


   All         - All the data in the table including the column names.
   Data        - All rows of data without the column names.
   FieldCount  - Count of columns in the table or query results.
   FieldNames  - List of Columns.
   NextRow     - The next row in the table or query. When the conversation
                 begins, NextRow returns the first row. If the current row
                 is the last record, a NextRow request fails.
   PrevRow     - The previous row in the table or query. If PrevRow is the
                 first request over a new channel, the last row of the
                 table or query is returned. If the current row is the
                 first record, a PrevRow request fails.
   FirstRow    - Data in the first row.
   LastRow     - Data in the last row.
   <Macro>     - The name of a macro to be executed.

Although all three LinkTopics (table name, query name, and SQL expression) return contents from the database and all three support the same LinkItems, their syntax structures differ slightly. Each LinkTopic must specify the database the object is in, a semicolon (;), the keyword (TABLE, QUERY, or SQL), and the name of an existing table, query, or SQL expression. Here are the syntax structures:


   [db Name];TABLE <Table name>
   [db Name];QUERY <Query name>
   [db Name];SQL <SQL expression>;

Here are examples:


   Text1.LinkTopic = "C:\ACCESS\NWIND.MDB;TABLE Employees"
   Text1.LinkTopic = "C:\ACCESS\NWIND.MDB;QUERY Sales Totals"
   Text1.LinkTopic = "C:\ACCESS\NWIND.MDB;SQL Select * from Employees;"

Note that all SQL statements must end with a semicolon (;).

Step-by-Step Example

Start Visual Basic or from the File menu, choose New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.

From the File menu, choose Add File. In the Files box, select the GRID.VBX custom control file. The grid tool appears in the Toolbox.

Add two list boxes (List1 and List2) to Form1. The List1 box holds the list of Tables and the List2 box holds the Queries.

Add two command buttons (Command1 and Command2) to Form1, placing the Command1 button beneath the List1 box and the Command2 button beneath the List2 box. Change the following properties:
```
   Default    Name           Caption
   -----------------------------------------
   Command1   GetTableList   Get &Table List
   Command2   GetQueryList   Get &Query List
 
```

Add a grid control (Grid1) to Form1 giving it the following properties:
```
   Default   Name    FixedCols
   ---------------------------
   Grid1     Grid1   0
 
```
The user chooses to display a table or the results of a query in Grid1.

Add two text boxes (Text1 and Text2) to Form1. The Text2 box acts as the destination for the data added to List1 and List2, so the user doesn't need to see this text box. But the Text1 box needs to be visible to the user because it acts as the destination for individual rows returned from a query or table.

Add two more command buttons to Form1, placing them beneath the Text1 box. Give the two command buttons the following properties:


   Default    Name      Caption
   ----------------------------------
   Command3   NextRow   &Next Row
   Command4   PrevRow   &Previous Row

Add the following code to the General Declarations section of Form1:


   Const None = 0
   Const Automatic = 1
   Const Manual = 2
   Const dbname = "C:\ACCESS\NWIND.MDB"  ' Change Paths as necessary
   Const accesspath = "C:\ACCESS\MSACCESS.EXE "

Add the following three Sub procedures to the General Declarations section of Form1:


   Sub ClearGrid ()
      ' Select all grid cells.
      Grid1.SelStartCol = 0
      Grid1.SelStartRow = 0
      Grid1.SelEndCol = Grid1.Cols - 1
      Grid1.SelEndRow = Grid1.Rows - 1
      ' Clear the cells.
      Grid1.Clip = ""
      ' Clean up the grid.
      Grid1.Col = Grid1.FixedCols
      Grid1.Row = Grid1.FixedRows
      Grid1.SelEndCol = Grid1.SelStartCol
      Grid1.SelEndRow = Grid1.SelStartRow
   End Sub

   Sub PopulateGrid (IsTable%, QueryOrTable$)
      If IsTable% Then
         Text1.LinkTopic = "MSACCESS|" + dbname + ";TABLE " + QueryOrTable$
      Else
         Text1.LinkTopic = "MSACCESS|" + dbname + ";QUERY " + QueryOrTable$
      End If
      Text1.LinkItem = "FieldCount"
      text1.linkmode = Manual
         text1.linkrequest
      Grid1.Cols = Val(Text1.Text)

      Text1.LinkItem = "FieldNames"
      Grid1.FixedRows = 0      ' Cannot additem to a fixed row
      Grid1.AddItem Text1.Text, 0
      Grid1.FixedRows = 1

      On Error GoTo LastRowErr
      Text1.LinkItem = "LastRow"
      Grid1.AddItem Text1.Text, 1
      Text1.LinkItem = "PrevRow"
      Do
         Grid1.AddItem Text1.Text, 1
         Text1.LinkRequest
      Loop
      Exit Sub
   LastRowErr:
      Exit Sub  ' Error occurs when last row is reached
   End Sub

   Sub GetList (L As ListBox, ListType$)
      text2.LinkMode = Manual
      text1.linkrequest
      text2.LinkTopic = "MSAccess|" + dbname
      text2.LinkItem = ListType$
      text2.LinkMode = Automatic
      StartPos% = 1
      Do
         Pos% = InStr(StartPos%, text2.Text, Chr$(9))
         If Pos% = 0 Then Exit Do
         L.AddItem Mid$(text2.Text, StartPos%, Pos% - StartPos%)
         StartPos% = Pos% + 1
      Loop
   End Sub

Add the following code to the Form_Load event of Form1:


    Sub Form_Load ()
       result% = Shell(accesspath + dbname, 1)
    End Sub

Add the following code to the GetQueryList_Click event procedure:


    Sub GetQueryList_Click ()
       GetList List2, "QueryList"
    End Sub

Add the following code to the GetQueryList_Click event procedure:


    Sub GetTableList_click ()
       GetList List1, "TableList"
    End Sub

Add the following code to the List1_Click event procedure:


    Sub List1_Click ()
       Table$ = List1.Text
       ClearGrid
       PopulateGrid True, Table$
    End Sub

Add the following code to the List2_Click event procedure:


    Sub List2_Click ()
       Query$ = List2.Text
       ClearGrid
       PopulateGrid False, Query$
    End Sub

Add the following code to the NextRow_Click event procedure:


    Sub NextRow_click ()
       On Error GoTo NextRowErrHand:
       Text1.LinkItem = "NextRow"  ' Get the next row of results
         text1.linkmode = manual
         text1.linkrequest
       Exit Sub
    NextRowErrHand:
       MsgBox "Last row reached"
       Exit Sub
    End Sub

Add the following code to the PrevRow_Click event procedure:


    Sub PrevRow_Click ()
       On Error GoTo PrevRowErrHand
       Text1.LinkItem = "PrevRow"
         text1.linkmode = manual
         text1.linkrequest
       Exit Sub
    PrevRowErrHand:
       MsgBox "First Row Reached"
       Exit Sub
    End Sub

From the Run menu, choose Start (ALT, R, S) to run the program. Microsoft Access is shelled with the NWIND.MDB sample database open and Form1 showing on the screen.

Choose the Get Table List button to see a list of all the tables in the NWIND database displayed in the List1 box.

Choose the Get Query List button to see a list of the previously defined queries that exist in the NWIND database displayed in the List2 box.

Select one of the items in either the List1 or List2 box to see the results displayed in Grid1.

Choose the Next Row button to see the second row displayed in the Text1 box. Continue to choose the Next Row button to display successive rows until you get to the last row. When you get to the last row, a message box appears to tell you that you reached the last row.

Choose the Prev Row button. The row previous to the one displayed in the Text1 box is displayed.

Additional query words: 1.00 2.00

Keywords :
Version :
Platform :
Issue type :