Article ID: 109397
Article Last Modified on 1/18/2007
APPLIES TO
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q109397
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.
Microsoft Access does not have any facility for running Visual Basic for Applications functions as a dynamic data exchange (DDE) server. However, Visual Basic functions can be run from a DDE client application if they are contained in an SQL statement used to initiate a DDE conversation with Microsoft Access using the SQL topic.
This article describes how to run Visual Basic functions from a DDE client application.
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 your version of the "Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0
MORE INFORMATION
A DDE client application such as Microsoft Excel or Microsoft Word for Windows can use DDE to run commands in Microsoft Access as a DDE server. When it is used as a DDE server, Microsoft Access recognizes any of the following as a valid command:
- The name of a macro in the currently open database
- Any action that you can run in Visual Basic using the DoCmd object (or DoCmd statement in versions 1.x and 2.0).
- The OpenDatabase and CloseDatabase commands, which are used only for DDE operations
Note that Microsoft Access does not have DDE server functionality for running Visual Basic functions.
How to Run Visual Basic Functions from a DDE Client Application
The following examples demonstrate how to run Visual Basic functions from DDE client applications. In both examples below, the MyFunct() function is run against the None table in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
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.
This is the DDE command issued by Microsoft Excel or Word for Windows:
chan=DDEInitiate("MSACCESS", "Northwind;SQL SELECT MyFunct() _
FROM <tablename>;")
Note: You should replace <tablename> with the name of any table that exists in the database.
The following example demonstrates how to add a new customer to the Customers table in the Northwind database by running a Visual Basic function from a DDE client application:
- Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0 and 1.x).
- Create a module and type the following line in the Declarations section:
Option Explicit
Type the following procedure:
Function AddNewCust$ (CustomerID$, CompanyName$) Dim MyDB As Database, MyRD as Recordset Set MyDB = CurrentDB() Set MyRD = MyDB.OpenRecordset("Customers") ' Open the table. MyRD.AddNew ' Prepare new record. MyRD("CustomerID") = CustomerID$ ' Set record key. MyRD("CompanyName") = CompanyName$ ' Set company name. MyRD.Update ' Save changes. MyRD.Close ' Close the table. End FunctionNOTE: In versions 1.x and 2.0, there is a space in Customer ID and Company Name.
- In the DDE client application, initiate a DDE conversation with MSACCESS as the application and an SQL statement that calls the AddNewCust() function you created in step 2 as the topic. If you are using Access 2.0 as you DDE server, you will have to have Access open with the Northwind (Nwind.mdb) database active for this code to work.
The following example demonstrates how to run the AddNewCust() function from Microsoft Excel versions 5.0 and 7.0, Microsoft Excel 97, and Microsoft Word 97:
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.
Sub AddNewCustomer
Dim chan as Integer
chan=DDEInitiate("MSACCESS", "NorthWIND.MDB;SQL SELECT _
AddNewCust$(""JOHNJ"",""John's Place"") FROM Shippers;")
DDETerminate chan
End Sub
Note: In the DDEInitiate statement above, you will have to replace the word Northwind with the actual path to the Northwind database. DDE does not support long file names, so the DOS alias names must be used for directory names longer than eight characters. (i.e. C:\mydocu~1\file.mdb)
The following example demonstrates how to run the AddNewCust() function from Word for Windows versions 6.0 and 7.0:
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.
Sub MAIN
DDETerminateAll
qt$ = Chr$(34)
Funct$ = "AddNewCust$(" + qt$ + "JOHNJ" + qt$ + "," + qt$ + _
"John's Place" + qt$ + ")"
Chan = DDEInitiate("MSACCESS", _
"Northwind;SQL SELECT " + Funct$ + "FROM Shippers;")
DDETerminate Chan
End Sub
Note: In the DDEInitiate statement above, you will have to replace the word Northwind with the actual path to the Northwind database. DDE does not support long file names, so the DOS alias names must be used for directory names longer than eight characters. (i.e. C:\mydocu~1\file.mdb)
REFERENCES
For more information about using Microsoft Access as a DDE server, search for DDE using the Microsoft Access 97 Help Index.
Keywords: kbhowto kbinterop kbprogramming KB109397
