DDE Example: Setup MS Access to receive data from RS232/RS232C ports

1.       Create new database. In this example I have created a database named serialdatabase.mdb in folder C:\SerialLogs
 
 
2.       Create new table in this database and add column/field, which will receive data from RS232 port. In this example, I have created a table named “SerialLog” with only one column/field named “RS232Data” and a default primary key. I have selected data type MEMO, which can store variable length character data.
 
 
Image: MS Access table SerialLog will store data from RS232 Port.
Image: MS Access and table wizard. Table SerialLog will store data from RS232 Port.
 

 

 
3.       Create a new module and type the following line in the Declarations section:
 
 
'Attribute VB_Name = "Module1"
Option Compare Database
Option Explicit
 
Global comport As Integer
 
' this function is executed by CPS Plus when data is received on COM port
Function GetCPSDataNew(cport As Integer)
comport = cport ' this function is called by CPS Plus to notify MS access when data is received via RS232.
' Parameter cport contains index of COM port that has sent notification.
' Example: 1 for COM1, 2 for COM2, 3 for COM3 ... etc.
' this index is stored in a global variable named comport.
End Function
 
Function GetCPSData() ' Function that actually reads data from CPS Plus
Dim ChannelNumber, MyData As Variant
Dim cpsTopic As String
Dim cpsComPortData As String
cpsTopic = "COM" & CStr(comport) ' connect to RS232 port that initiated event
cpsComPortData = "COM" & CStr(comport) & "DATA"
ChannelNumber = DDEInitiate("CPSPLUS", cpsTopic)
MyData = DDERequest(ChannelNumber, cpsComPortData)
DDETerminate ChannelNumber
'MsgBox CStr(MyData)
 
If Len(MyData) = 0 Then Exit Function
Dim MyDB, MyTable
'Dim MyDB As Database, MyTable As Recordset ' <<< For Access versions earlier than 2000
Set MyDB = CurrentDb()
Set MyTable = MyDB.OpenRecordset("SerialLog")
 
MyTable.AddNew
 
' insert data read from COM port into table
MyTable("RS232Data") = MyData
 
' alternate usage: if data is out of bounds send alarm email...
 
MyTable.Update
MyTable.Close
 
End Function
 
 
4. Add new macro named GETDATA.
-          Macro action: RunCode
-          Function Name: GetCPSData()
 
 
Resources:
MS Access macro collect data from rs232 com port
Image: MS Access - New macro setup screen.
 
Leave Access running and switch to CPS Plus.