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