DDE and Microsoft Access

zmanvortex

Guest
Z
I am building a Microsoft Access database to log scale weights in a batch mixing application. I am using RSLinx and a PLC 5/20. I know how to use the DDE link to link to the scale values but I don't know how to set up a trigger to log the weight just before the batch dumps. I have a batch ready bit that goes high just before the batch dumps but I am unsure how to use the bit in Access as a trigger. Thanks
 
I did something very similar using DDE and EXCEL and had the same problems. Maybe I can tell you my example and give you some direction since I have never used DDE and ACCESS before.

Basically I set up a HOT TOPIC DDE link from Excel to a BIT in the PLC. Since I wanted the PLC to trigger a "log this information bit" in my Excel Sheet. So when the HOT DDE LINK changed, I used a VB Script or Excel command called .OnData. When the PLC changed the bit from 0 to 1, EXCEL seen this change on the worksheet called "INDATA', then ran my macro called "START".

Warning! Make sure that NO OTHER information is on the worksheet called INDATA. Because you want to make sure that it is dedicated to the PLC's bit changes and nothing else.



Sub Auto()

Worksheets("INDATA").OnData = "Start"
' A bit change from the PLC Hot Linked on INDATA
' will automaticly Run Macro (start).

End Sub




Once my macro "start" was running, a bunch of COLD DDE links were opened up and I starting reading information from registers in the PLC
and wrote them in the Excel Sheet.

More Comments after code below.............



Sub Start()

Dim lngRow As Long
Dim varCycle As Variant
Dim varLogging As Variant
Dim varResults As Variant
On Error GoTo Error


'opens a COLD DDE link
RSIchan = DDEInitiate("RSLinx", "M1138")

'assign PLC bit values to VB variant varibles
varLogging = DDERequest(RSIchan, "B3/163")
varCycle = DDERequest(RSIchan, "B3/161")

'close COLD DDE link
DDETerminate (RSIchan)

'check to see if the Cycle bit went to "1" if it did, excute read data
If varCycle(1) = "1" And varLogging(1) = "1" Then

'starts at row 3 of sheet
lngRow = 3

If Range("INDATA!A3").Value > 3 Then
'look up last cell and change position
lngRow = Range("INDATA!A3").Value
End If

'check until end of sheet
For lngRow = lngRow To 65500
'look for next empty cell
If Cells(lngRow, 1) = "" Then Exit For

'write current cell location to sheet INDATA
'rather than writing a loop to search on
'every cycle, by the time the log is at row 21,500
'it could take a long time to search the rows...
Range("INDATA!A3").Value = lngRow + 1

'add 1 to row "x" to check next row
Next

'opens a COLD DDE link
RSIchan = DDEInitiate("RSLinx", "M1138")

'there might be a better way to do this like
'using this somehow ???????? but I don't know how
'data = DDERequest(RSIchan, "f11:0,L7,C7")
'Range("[M1138.xls]LOG!R[x]C1:R[x]C7").Value = data


'read word F8:10 and load into a VB variant variable
f810data = DDERequest(RSIchan, "F8:10")
'read word F8:11 and load into a VB variant variable
f811data = DDERequest(RSIchan, "F8:11")
'read word F8:12 and load into a VB variant variable
f812data = DDERequest(RSIchan, "F8:12")
'read word F8:16 and load into a VB variant variable
f816data = DDERequest(RSIchan, "F8:16")
'read word F8:18 and load into a VB variant variable
f818data = DDERequest(RSIchan, "F8:18")
'read word F8:17 and load into a VB variant variable
f817data = DDERequest(RSIchan, "F8:17")
'read word F8:20 force check #1 and load into a VB variant variable
f820data = DDERequest(RSIchan, "F8:20")
'read word F8:21 force check #2 and load into a VB variant variable
f821data = DDERequest(RSIchan, "F8:21")
'read word F8:22 force check #3 and load into a VB variant variable
f822data = DDERequest(RSIchan, "F8:22")
'read word F8:23 force check #4 and load into a VB variant variable
f823data = DDERequest(RSIchan, "F8:23")
'read word F8:25, get PASS or FAIL status from PLC and load into a VB variant variable
varResults = DDERequest(RSIchan, "F8:25")

'close COLD DDE link
DDETerminate (RSIchan)

'write all values to cells
Cells(lngRow, 1).Value = f810data
Cells(lngRow, 2).Value = f811data
Cells(lngRow, 3).Value = f812data
Cells(lngRow, 4).Value = f816data
Cells(lngRow, 5).Value = f818data
Cells(lngRow, 6).Value = f817data
Cells(lngRow, 7).Value = f820data
Cells(lngRow, 8).Value = f821data
Cells(lngRow, 9).Value = f822data
Cells(lngRow, 10).Value = f823data

Range("INDATA!A4").Value = varResults
If Range("INDATA!A4").Value = 1 Then
Cells(lngRow, 11).Value = "PASS"

'copies the conditioinal format to the next cell
Range("K" & lngRow + 1).Select
Selection.Copy
Range("K" & lngRow + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If

If Range("INDATA!A4").Value = 2 Then
Cells(lngRow, 11).Value = "FAIL"

'copies the conditional format to the next cell
Range("K" & lngRow + 1).Select
Selection.Copy
Range("K" & lngRow + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If

If Range("INDATA!A4").Value = 0 Then
Cells(lngRow, 11).Value = "NO DATA"
End If
If Range("INDATA!A4").Value > 2 Then
Cells(lngRow, 11).Value = "ERR"
End If

'capture time and date stamp into column 8
Cells(lngRow, 12).Value = Now()

'scrolls the screen down automatically
'Range("A" & lngRow + 1).Select

End If

GoTo Done:
Error:
frmError.Hide
frmError.Show

'trying a timed form box, so disabled the old MESSAGE BOX here for now
'MsgBox ("Communications to PLC have been lost! Or Log Sheet is FULL! Can not log data anymore. Check PLC connection and wiring.")
Application.Run Macro:="ChkTime"

Done:

'check for date change, if so then save as new sheet
Application.Run Macro:="ChkTime"
End Sub




So maybe hopeful you might be able to use the same concept to trigger your access sheet.

If you want to download the whole EXCEL SHEET CODE, you can get that file at:

http://www.mrplc.com/cgi-bin/code/filedisplay.cgi?category=excel

Scroll down until you see:
Allen Bradley SLC500 DDE Link to Excel Logger 1.0

Ofcourse look at the Macro VB Scripts to see the code used in Module1.

Ignore all the PDF macros.

Good Luck,...
 
The last time I did something like this, I found that MS Access did not support hot links. (Access 97)

My solution was to create the database with Access and then use VB to create programs that can incorporate hot links and manipulate the database with them. MS Access is not required after the database has been created (the .mdb file).

One other approach to consider is using the RSData 'object' which I believe has some logging capabilities, though I haven't used it.
 
Hot links

Chako,

As I understand it, Excel does (and did) support hot links, whereas Access did not. I still use Office 97 so don't know if later versions of Access have changed this situation.
 
I am tring to figure out this vb script . i can not get it to work . I set up a hotlink topic Indata i put s4:7 as my toggle bit.
I changed my varlogging to S4:4 and my varcycling to s4:4. I then went to the f810 data and changed f810data = DDERequest(RSIchan, "S4:4")i still did not get it to log to excel. My hot link was toggling in excel a1. also i am not to good with these programs.What does this mean.
Range("INDATA!A4").Value = varResults
If Range("INDATA!A4").Value = 1 Then
Cells(lngRow, 11).Value = "PASS"

'copies the conditioinal format to the next cell
Range("K" & lngRow + 1).Select
Selection.Copy
Range("K" & lngRow + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If

If Range("INDATA!A4").Value = 2 Then
Cells(lngRow, 11).Value = "FAIL"

'copies the conditional format to the next cell
Range("K" & lngRow + 1).Select
Selection.Copy
Range("K" & lngRow + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If

If Range("INDATA!A4").Value = 0 Then
Cells(lngRow, 11).Value = "NO DATA"
End If
If Range("INDATA!A4").Value > 2 Then
Cells(lngRow, 11).Value = "ERR"
 
Before we get too deep let me ask some basic questions:

1: Do you have a FULL VERSION of RS LINX, either FULL version or SINGLE NODE?

2: Did you create a TOPIC in RS LINX? A DDE topic that is, and called your topic in your hot link the same name?

The code you don't understand is just some CELL values that I wrote to the screen to let the operator know if it was PASS or FAIL parts. Then using conditional formating, if the cells value was PASS, it would color the whole cell a green color. It really has nothing to do with DDE or Hot topics, it was just a beatification code and for clarity.

Let me know your above answers.
 
1 I have the rslogix gateway full version


2. I created a hot link topic in rslinx I named it INDATA I copied s4:7 and pastd it special to cell a1 in excel.

thanks for he quick response
 
The code "Range("INDATA!A4")
is purely for Excel.

It references a worksheet called INDATA, and a cell A4. If your sheet is still called 'Sheet1', this script will not work.

Looking at ChakoRules original script, 'M1138' is the name of the Topic configured in RSLinx.

I'm guessing here, but cells A4 and A3 have a hot link connection to RSLinx, having had the desired data copied from RSLinx, and Paste Special to Excel.

The script you were asking about seems to just convert a (human-)meaningless code of 0,1,2,+ into a more meaningful "NO DATA", "PASS", "FAIL" and "ERROR" into a designated cell.
 

Similar Topics

I need to support some old data collection that is running on Excel, but I need to get it running on LibreOffice. The following statement works...
Replies
0
Views
56
Hey Friends. I am currently trying to write approximately 700 tags from FtView13 to an Logix Emulate 5000. I am using rslinx DDE/OPC to create an...
Replies
0
Views
380
I am upgrading an old RSView32 v7.60 HMI to FactoryTalk View SE version 12.0 and I have an issue. There are some excel reports generated daily...
Replies
2
Views
980
This used to be an easy task in the past to get data into Excel, now I have 365, Excel just hangs when I paste the link. I've seen some guides to...
Replies
1
Views
1,225
Hi, I'm brand new to the forum and spent some time looking around but could not find an answer to my question. I'm using an excel spreadsheet to...
Replies
3
Views
1,325
Back
Top Bottom