WinWedge DDE Server Settings
Remember to set WinWedge up in DDE Server Mode! Click here.
Access 2007 and older:
DDE Application Name: MSACCESS
DDE Topic: NameOfDatabase
DDE Command: [NameOfMacro]
Access 2010 and newer:
DDE Application Name: MSACCESS
DDE Topic: NameOfDatabase.ACCDB
DDE Command: NameOfMacro
In the above DDE command, replace NameOfMacro with the actual name of the macro you wish to use and NameOfDatabase with the name of your database file. Please note that Macros in Access are different than subroutines. You can have a macro that runs a specific subroutine, which is the case with the examples below. You can also have a macro that does many other tasks, without the need to write VBA code.
If you compile your Access database to an MDE file, then you will need to change the DDE Topic to the name of the MDE file including the MDE file name extension. For example, if you compile your database and create a MDE file named MyData.MDE, you will need to specify “MyData.MDE” for the DDE Topic. You do not need to include the file path for the MDE file and should only specify the file name with the MDE extension. This also applies to MDB files that are used with the Microsoft Access Runtime engine. For example, if you will be using your MDB file with the Access Runtime engine, then you will need to change the DDE Topic to the name of the MDB file including the “.MDB” file name extension.
Setting Up Access:
MSAccess does not have a “GrabData” DDE command so the first step is to create one. When you define a macro in an Access database, the name of the macro automatically becomes a valid MSAccess DDE command for as long as the database is open.
Create a new macro that performs the “RunCode” Action. For the function name that is required by the RunCode action, enter the name “PlaceDataIntoTable()”. Save the macro with the macro name “GrabData” then switch to the Modules tab and create a new module containing the code below.
Function PlaceDataIntoTable()
Dim Chan As Long, MyData As Variant, MyDB, MyTable
Chan = DDEInitiate("WinWedge", "Com1") 'Open a link to WinWedge
MyData = DDERequest(Chan, "Field(1)") 'Request the data from Field 1
' Store multiple fields in different variables like so:
' MyData2 = DDERequest(Chan, "Field(2)")
DDETerminate Chan 'Terminate the DDE link
If Len(MyData) = 0 Then Exit Function 'Quit if there is no data.
' Data from Field(1) in WinWedge is now in the variable "MyData".
' The following code adds a new record to TABLE1 and stores the
' data in a field named "SerialData"
Set MyDB = CurrentDb()
Set MyTable = MyDB.OpenRecordset("Table1")
MyTable.AddNew ' add a new record
' write our data to a field named "SerialData"
MyTable![SerialData] = MyData 'Write data to field named
MyTable.Update ' update the table
MyTable.Close ' close the table
' Destroy objects
Set MyDB = Nothing
Set MyTable = Nothing
End Function
When finished, save your Module as modWedgeCode. The example above sets up the Wedge to issue a DDE command consisting of the name of an Access macro (directly to the Access database that contains the macro) after each data record is received from your serial device. The macro calls an Access VBA function that performs a DDERequest back to the Wedge and returns the contents of FIELD(1) to a variable named “MyData”. It then creates a new record in a table and inserts the data into the new record.
Troubleshooting
The Macro Stops with a “The expression you entered has a function name that Microsoft Access can't find” (or similar) error.
Usually this means that you named your Module “GetWedgeData”. You should never give a module the same name as a procedure, or Access may be unable to find that procedure. If this is the case then simply rename your module.
Error 3078 : The Microsoft Jet database engine cannot find the input table or query 'TABLE1'. Make sure it exists and that its name is spelled correctly. or Error 3265 : Item not found in this collection.
Our example uses a table called “Table1” and a field called “SerialData”. You will need to modify the code slightly so that it opens YOUR table and writes to fields that exist in that table. Change the lines below to point to your table and a valid field in that table.:
Set MyTable = MyDB.OpenRecordset("TABLE1")
MyTable![SerialData] = MyData
Error 3421 : Data type conversion error.
Make sure that the data type for that field is appropriate: If the MyData variable (the data from WinWedge) contains “X = 123” and your field can only contain numbers you will get this error.
Compile Error: User-Defined Type not Defined.
Older versions of the WinWedge Manual contain the line:
Dim MyDB as Database, MyTable As Recordset
However MS Access 2000 and later no longer support the Database type declaration so the easiest solution is simply to delete the words “as Database” from the statement:
Dim MyDB, MyTable
Error 282 : Microsoft Access can't find the specified application and topic because it can't open the DDE channel.
This error usually means that you forgot to change the com port in your code – check the line:
ChannelNumber = DDEInitiate("WinWedge", "Com1")
and change “Com1” to “Com2” or whichever com port you are using. If you are using TCPWedge the line should be something like:
ChannelNumber = DDEInitiate("TCPWedge", "123.45.67.89")
Be sure to change “123.45.67.89” to the IP Address TCPWedge is connected to.
Nothing Happens in Access but I do see the data appear in the WinWedge window.
This problem has a number of possible causes. First it is important to realize that the table will not usually update in real time, in most cases you will need to close the table and reopen it in order to see the new data. In order to see the table update in real time you will need to change the refresh rate. To do so, click on Tools > Options and change the “Refresh Interval (sec)” value from its 60 second default to just 1 or 2 seconds.
If you still have no data then make sure that WinWedge is correctly configured in DDE Server Mode as described at the top of this page. Next, with WinWedge running and containing data from your instrument, try running the macro manually and see if it enters the data into your table.
If the macro works when you run it manually then there is a problem with the DDE connection between WinWedge and MS Access. Usually this is caused by long FileNames. While 32 Bit versions of WinWedge suport long file names, there seems to be an upper limit to the length of a DDE Topic. Since Access uses the name of the database as the topic filenames that exceed this limit may be truncated. Furthermore DDE Topics that contain spaces may need to be enclosed in quotes. Suppose your database is called “This is my WinWedge Test Database.mdb” – then the DDE Topic should be “This is my WinWedge Test Database” however it may be truncated to “This is my W” or possibly just “This” if the topic was not enclosed in quotations. Either way, the topic does not exist and the macro will not run. Try enclosing your DDE Topic in quotes or rename your database based on the old 8.3 short file name rules and then update the Topic in WinWedge.
If the macro does not work when you run it manually then there is probably something wrong with the code. Usually the following line is to blame:
If Len(MyData) = 0 Then Exit Function
Before entering data into the table this line checks to see if there actually is any data in the MyData Variable and If there is no data it simply exits. If you have made any customizations to the code you may have changed the variable names and you are no longer using a variable called MyData. If this is the case then it will always be empty and the function will always stop at this line. Delete the line or modify it to check a variable that does exist and try again.
If the macro still doesn't work you will need to set a break point near the top of the code and step through it to see what happens. To set a break point simply place the cursor on the line:
ChannelNumber = DDEInitiate("WinWedge", "Com1")
and Press F9. (It should turn a brown color). Then when you run the macro again the line will turn Yellow. Keep pressing F8 to step through the code line by line, place your cursor over variables to see what is in them and make a note of which line you are on when it stops or causes an error. When you are done, be sure to remove the break point using the same steps you used to add it (F9 will toggle it on and off) or the code will always stop executing at that point.