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

Last reviewed: October 18, 1995
Article ID: Q96845
The information in this article applies to:

- Professional Edition of Microsoft Visual Basic 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

  1. 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.

  2. 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.

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

  4. 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
    
    

  5. 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.
    
    

  6. 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.

  7. 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
    
    

  8. 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 "

  9. 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
    
    

  10. Add the following code to the Form_Load event of Form1:

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

  11. Add the following code to the GetQueryList_Click event procedure:

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

  12. Add the following code to the GetQueryList_Click event procedure:

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

  13. Add the following code to the List1_Click event procedure:

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

  14. Add the following code to the List2_Click event procedure:

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

  15. 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
    
    

  16. 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
    
    

  17. 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.
    

  18. Choose the Get Table List button to see a list of all the tables in the

        NWIND database displayed in the List1 box.
    

  19. 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.
    

  20. Select one of the items in either the List1 or List2 box to see the

        results displayed in Grid1.
    

  21. 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.
    

  22. Choose the Prev Row button. The row previous to the one displayed in

        the Text1 box is displayed.
    


Additional reference words: 1.00 2.00
KBCategory: kbinterop kbprg kbcode
KBSubcategory: IAPDDE


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 18, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.