The information in this article applies to:
- Microsoft Access versions 2.0, 7.0
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
In Microsoft Access, you cannot poke data into a table using a dynamic data
exchange (DDE) channel. However, you can pass information to a Visual Basic
for Applications function using a function with parameters in a DDE channel
to the SQL topic. The data can then be processed by the function and added
to a table.
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 the "Building
Applications with Microsoft Access for Windows 95" manual.
NOTE: Visual Basic for Applications (used in Microsoft Access for
Windows 95 version 7.0) is called Access Basic in version 2.0. For
more information about Access Basic, please refer to the "Building
Applications" manual.
MORE INFORMATION
The following example demonstrates how to add a new customer to the
Customers table in the sample database Northwind.mdb (or NWIND.MDB in
version 2.0) using data from Microsoft Excel and Microsoft Word for
Windows:
- Create a table called None with one field of any data type and one
record.
- Open a module and create a procedure with the parameters that you want
to pass. The following Visual Basic for Applications function creates a
new record in the Customers table, and accepts an argument for the
CustomerID field and an argument for the CompanyName field.
NOTE: In version 2.0, there is a space in the Customer ID and the
Company Name fields.
Declarations Section
Option Explicit
Function AddNewCust (CustomerID$, CompanyName$)
Dim MyDB As Database, MyTable As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
' Trap any error that might occur.
On Error Resume Next
' Open table.
Set MyTable = MyDB.OpenRecordset("Customers", DB_OPEN_TABLE)
MyTable.AddNew ' Prepare new record.
MyTable("CustomerID")= CustomerID$ ' Set record key.
' NOTE: The above line should read MyTable("Customer ID")=
' CustomerID$ in version 2.0.
MyTable("CompanyName") = CompanyName$ ' Set company name.
' NOTE: The above line should read MyTable("Company Name")=
' CompanyName$ in version 2.0.
MyTable.Update ' Save changes.
MyTable.Close ' Close table.
' Return the error code.
AddNewCust = Err
End Function
This function returns 0 if successful; otherwise, a Microsoft Access
error number is returned. If a record in the Customers table already
exists with the specified Customer ID, error number 3022 is returned.
This error would return the following error message if it were not
trapped:
Duplicate value in index, primary key, or relationship.
Changes were unsuccessful.
- In the DDE client application, initiate a DDE link using MSACCESS
as the application and a SQL select query as the topic. The topic
should be of the form:
DatabaseName;SQL Select FunctionName(args) From None;
In the sample topic above, None is the name of the empty table, and
FunctionName(args) is the call to the Visual Basic function that
processes the arguments (args) passed to it.
For example, the following Microsoft Excel macro inserts a new
record with "JOHNJ" as the CustomerID and "John's Place" as the Company
Name into the Customers table:
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 in Access Basic.
AddNewCustomer
chan=INITIATE("MSACCESS","NORTHWIND;SQL SELECT _
AddNewCust(""JOHNJ"",""John's Place"") FROM None;")
=ALERT(REQUEST(chan,"FirstRow"))
=TERMINATE(chan)
=RETURN()
The following is the equivalent Microsoft Excel Visual Basic
Sub procedure:
Sub AddNewCustomer()
Chan = DDEInitiate("MSACCESS", "NORTHWIND;SQL SELECT _
AddNewCust(""TADO"",""Tad's Place"") FROM None;")
Result = DDERequest(Chan, "FirstRow")
Msgbox str(result(1))
DDETerminate Chan
End Sub
The following is the equivalent Microsoft Word for Windows Word-
Basic macro:
Sub MAIN
var1$ = "JOHNJ"
var2$ = "John's Place"
qt$ = Chr$(34) ' create the quote character
Funct$ = "AddNewCust(" + qt$ + var1$ + qt$ + "," + qt$ +_
var2$ + qt$ + ")"
Chan = DDEInitiate("MSACCESS", "NORTHWIND;SQL SELECT " + _
Funct$ + " FROM None;")
Result$ = DDERequest$(Chan, "FirstRow")
MsgBox Result$
DDETerminate Chan
End sub
How the SQL Initiate Technique Works
When you initiate a DDE link with Microsoft Access using an SQL statement,
each function in the SELECT statement will run one time for each record in
the table the query selects from.
In the examples above, the None table has only one record to query against.
Because the table has only one record, the AddNewCust() function runs only
one time. If the None table had two records, the AddNewCust() function
would run twice, trying to add the same record two times.
The query's result will return the results of the function being called in
the SELECT statement. The AddNewCust() function returns the error code if
an error occurs. This is useful because it lets the DDE client, such as
Microsoft Excel, Word, or Visual Basic, know whether the function ran
successfully or not.
REFERENCES
For more information about "Using Microsoft Access for Windows 95 as a
DDE Server", search for "DDE," and then "Overview" using the Microsoft
Excel, Word, or Visual Basic Help Index
For more information about implementing DDE in version 1.x, please see the
following article here in the Microsoft Knowledge Base:
ARTICLE-ID: Q100167
Title : ACC1x: How to Use DDE to Pass Information to MS Access 1.x