DDE - PLC->Access 97 db.

Hi All,

I am hoping there are folks out there that know Access VB in addition to Siemens 545 PLC's.

I have 32 V locations that I need to gather data from every 60 seconds. The client(s) are Access 97 apps. The server is a CTI2572 IO Server. Its version is 5.1. The topic is set to use TCP, has a Update Interval (milSecs) of 60000, and has a Response Time Out of 20.

I need to do that as long as I have a true condition that is part of the code. I am using DDERequests to hit the V locations. For whatever reason I can't get all the data in a minutes time. So I split the Access VB app in half. When they both run together the memory usage on the XP system hosting them goes down big time. As an example, the host system starts of with 512MB of RAM and before long the available memory drops down to under 100MB. What I am wondering is there any way to streamline the Access VB code? I believe that the VB code is causing the problems.

If your going to check out the code in the Access VB your going to want to look at the code associated with the form "PLCLoggerNoCodeExecuteCheck"

GrinderDryerCheck = True
CalcinerCheck = True

These are Local Variables, so every time the Subroutine is enetered you have to force them on. That means the sub runs every scan and takes up a lot of extra memory. Find a way to make these Global Variables.

Im no expert but here's my two cents worth.

Briandr said:

Hi All,

For whatever reason I can't get all the data in a minutes time. So I split the Access VB app in half.

Click to expand...

DDE has always been slow..
When you try to get all DDE at once does your CPU usage go sky high ? What does it do when you spilt the app. Seems to me that your app simply needs more time to get the data. Did it used to work and this problem has developed later ? If not I think you are on the right track about splitting the app.

Briandr said:

Hi All,
When they both run together the memory usage on the XP system hosting them goes down big time. As an example, the host system starts of with 512MB of RAM and before long the available memory drops down to under 100MB.

Click to expand...

If you are talking about memory usage and not cpu usage here dosen't it suggest that something in the code/dde is remaining in memory instead of clearing ?

Is there a limit to number of dderequests that can be open at one time ? Can you put short delay in between your requests to see if this effects the memory usage ? I was thinking along the lines of if you split the app and it behaves better this may be due to freeing up cpu time and if this is the case then maybe by introducing short waits beteen requests this will free up more cpu time.
Another thing you might try is to create more than one dde channel and stagger the requests to these new channels to allow the request to be answered before sending another request down the same channel.

Sorry to bombard you with so many suggestions, just to trying to cover as many bases as possible.

I’m assuming your trouble is in the Form_Timer Method.

First, I don’t see where you are opening and closing the database connection. You should open a database connection at the beginning of the method and close the connection at the end of the method. My guess is that the OpenRecordset is forcing a database connection, but that connection isn’t getting closed. So every time this method is called, another database connection is created, which will bog down your system real fast if these connections don’t get closed.

Second, I would remove the If Not GrinderRS Is Nothing Then statement (along with the others), there is no need for it. If you enter into the statement where the recordset is created there will always be a recordset and you should always close the recordset, so there is no need to check if the recordset is Nothing. Just close the recordset.

Third, you are using a recordset when you really should be using a simple insert statement. When you create a recordset you are creating a image of the data in memory, in this case you are creating an image of the entire table. This is way more overhead than is needed. Recordsets were designed so that a program could pull out data from a database, play with the data for a while then give the data back to the database where the database would determine what had changed and update the database accordingly. Since you are only inserting a new record, this is more overhead than is needed. If for some reason you have your mind set on using a recordset you should at least set the source so that you are not pulling the entire table.

I suspect that your main trouble is that the database connections are not being closed. You should use an insert statement instead of a recordset. Although you might not be seeing performance issues by using a recordset at this time once your table gets large you will, especially if you try to pull the entire table into the recordset. Lastly, remove the If statement around the recordset close, there is no need for it and who knows something strange might happen at some point and the recordset might not get closed, just use a close statement without a condition.

Hi All,


So many suggestions in such a short span of time. Thanks to all who have offered to help. A Couple things and then I'll try and address comments, suggestions and questions. First, I think its safe to rule out the PLC as being part of the problem. Second, I actually did write this code from sratch. I just took it from another app and adjusted it to fit my needs. I really am not a programmer. A hack is more like it.

Ok. To the comments/suggestions/questions.

Lancie1, can you or someone else help me make these checks part of Global Variables? I wouldn't know how to do this.

Consys, Yes processor utilization goes through the roof. It bounches from 60% - 100% untilization. Most of the time its roaming close to 100%. Process utilization was high before the split and continues to be high after the app was split. I agree with your assesment that something in the code is staying in memory and not clearing as it should. I really can't put in a pause between DDERequests as I need to grab the data and get out before a minutes time is up. I don't think there is a limit to the number of DDERequests.

Tark, I think your right about the database connection. Would you be able to help modify my code to account for this and your other suggestions? I will take your suggestion and remove the If Not GrinderRS Is Nothing Then statement.

If I see something I didn't address I'll come back to it when I am fresh in the morning.

Thank you to all.

My money is on Tark...

I think Tark is correct. Forget the global variables and use Insert instead of Recordset.

I don’t have Access97, so I don’t know for sure if your version has this method, if it does you would do something like this –

Code:

Set db = OpenDatabase("c:\Path\DatabaseName.mdb")
 
db.Execute "INSERT IGNORE INTO tblGrinderProcessVariables " & _
"(fieldName1, fieldName2, ..., fieldNameN) " & _
"VALUES ('" & value1.Text & "' , " & _
"'" & value2.Text & "' , " & _
"'" & ... & "' , " & _
"'" & valueN.Text & "')"
 
db.Execute "INSERT IGNORE INTO tblDryerProcessVariables " & _
"(fieldName1, fieldName2, ..., fieldNameN) " & _
"VALUES ('" & value1.Text & "' , " & _
"'" & value2.Text & "' , " & _
"'" & ... & "' , " & _
"'" & valueN.Text & "')"
 
 
db.Close

It’s a bit hard to read the insert string, but the syntax is –

INSERT IGNORE INTO tablename (fieldName1, fieldName2, ..., fieldNameN)
VALUES (‘value1’, ‘value2’, ‘…’, ‘valueN’)

You don’t need to close the database until the end of the method unless you are doing something else in the method that can be time consuming, in which case I would close the connection as soon as you are done with it.

I probably should have mentioned this before, but the data is going to a SQL backend table. The Access 97 app I posted here is the front end. I am not sure if you already figured that out, but I wanted to make note of it. One other thing came to mind after the fact. CTI had me do a dump so they could see the stats on the
2572-A card. The one thing that stood out to them was there alot of TCP connections. I wonder if that is related to how the Access VB code was written for this originally. I really doubt the PLC is the source of these problems. I just wanted to mention that to see if anyone had any thoughts.

Thanks again.

If you are using Access as a front end I would dump it. You can download Visual Basic 2005 Express for FREE. It didn't appear that there was a lot of code, so it shouldn't take that long to create the program. Plus a compiled VB app will run faster than your Access front end and you will have access to newer methods.

Either way I think the code I posted should work accessing a SQL database.

http://msdn.microsoft.com/vstudio/express/vb/download/default.aspx

I started looking at your code and trying to see how I could integrate into the existing code. Alot of this went over my head. I added in your code the best I could. I also left note. There are two things. One, how do I bring in the data from the DDERequests and how do I address (open) the SQL back end database? If you could show me and then re-post, I'd appreciate it.

I wanted to add some other thoughts that came to mind. First, I did take your suggestion and removed the If Not is Nothing statement around the record close. I also added db.close command to the end of the code. Memory being gobbled up continues to be a problem. Your probably right it would be more efficient to use the Insert To statements. As I mentioned in my last post, I don't know how to integrate that code into the existing program. Just to let you know I am not in favor of using the record sets if they are causing problems. All I know is to have a system with over 500MB of RAM drop to 100MB of RAM or less is defintely a problem that can't be ignored. Thanks again.

It'll be late tonight before I can get something to you.

Ok. Things like adding in the db.close were not included in the attachment. I added that into my working copy of the app. I will keep wrestling with the code as best I can. Right now I just keep Googling looking for examples.

Thanks for the help you provided thus far.

A couple of things came to mind. If you can't connect to the database with the instruction I gave you, go back to the one you were using. Either way make sure you have db.close at the end of the method.

Something I would do is to comment out parts of the code to find where the bottleneck is. I would start by commenting out all of the instructions that are updating the database. Then try commenting out the database open and close statements. Finally I would comment out the DDE statements. This should give and idea where the bottleneck is.

One of the things that has been bothering me is that the DDE connection is made at the start of the method and then closed at the end of the method. If I had created the program I doubt that I would have done this. I would have created the DDE connection when the program started and closed the DDE connection when the program closed, unless there were long periods in which there were no need for the DDE connection.

One last thing, what is the timer set at that calls this method? How often is the method called?