Article ID: 96845
Article Last Modified on 10/30/2003
APPLIES TO
- Microsoft Visual Basic 2.0 Professional Edition
- Microsoft Professional Toolkit for Microsoft Visual Basic programming system for Windows
This article was previously published under Q96845
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: KB96845