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.

 

 

 

 

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()

Get serialdatabase.mdb from http://www.programbl.net/resources/cpsplus/serialdatabase.zip