OPC Server and Visual Basic Script

[daisy4u721]

Hello,

I am a newbie to Visual basic and OPC, and I hope someone can help me here. I have an historical database which has data in intervals of 40mins, I also have an OPC server which intends to go into the database and get data. I am trying to write a Visual basic script to tell the OPC Server to get the historical data (from a “start date” to an “end date”) and manipulate the data such that the intervals are 1 min and the value remain the same until the next time. Below is the description of what I want to achieve:

Historical database data looks like this:
Number Date/Time Value
536 10/1/2011 0:00 AM 21
400 10/1/2011 0:40 AM 18
256 10/1/2011 1:20 AM 16
589 10/1/2011 2:00 AM 17
896 10/1/2011 2:40 AM 20

I want the VB Script to keep adding 1 min to the “Date/Time”, and keep the same “Value” and “Number” until the next “Number”. Description below:

Number Date/Time Value
536 10/1/2011 0:00 AM 21
536 10/1/2011 0:01 AM 21
536 10/1/2011 0:02 AM 21
536 10/1/2011 0:03 AM 21
:
:
536 10/1/2011 0:39 AM 21
400 10/1/2011 0:40 AM 18
400 10/1/2011 0:41 AM 18
:
:
:
400 10/1/2011 1:19 AM 18
256 10/1/2011 1:20 AM 16
:
:

Any help will be appreciated.

Thanks

 

[Geates]

How is the data in the historical database stored? Text file? Is the time difference always 40 mins? If so:

1. read a line of data
2a. change text
2b. print 40 times
3. repeat until end of file.

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[daisy4u721]

Thanks Geates,

The database is a SQL database, with historical date. The time difference is not always 40 mins (Sorry i did not mention this earlier.

-Dee

 

[daisy4u721]

Sorry i meant "with historical data"

 

[Geates]

First of all, establish a connection to the SQL database and get the data you need(Number, Date/Time, Value) into a record set so we can play with the data. Once this is accomplished, we can address the rest.

Typical SQL connection to a database name "stories" on a computer named "charles" with the username of "dickens\oliver" and a password of "twist":

const adOpenStatic = 3
const adLockOptimistic = 3

set objConnection = CreateObject("ADODB.Connection")
set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=SQLOLEDB;Data Source='charles';Trusted_Connection=Yes;Initial Catalog='stories';User ID='dickens\oliver';Password='twist'
;"

objRecordSet.Open "SELECT number, date, value FROM table", objConnection, adOpenStatic, adLockOptimistic

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer