I want to log the data from RSView32 SCADA into Access data base and reporting in Excel. How to make ODBC connectivity for the above application, and what steps do I have to follow to make it workable?

 

A Reporting Tool WinMIS allows logging of data from any OPC Compliant HMI (RSView comes with a free OPC Server) and saving it to MySQL Database. It also provides configurable report generation in MSExcel. Visit http://www.egenietech.com/ for more information.

Vipul Shah

 

<p>Hi,

<p>Personally I would not use Access but would prefer Microsoft MSSQl Express which will probably be good enough for your needs. Anyhow it does not really matter what database you use
1) Set up up your database, create the table, etc.
2) Create an ODBC connection to the database
code to write to a SQL database;

<p>This has a numeric display on a screen that changes every second:
<pre>
Private Sub NumericDisplay1_Change()
'this is writes data into the SQL database each time the display value changes
'no error checking is carried out to test database exists etc.
'a valid ODBC connection must be created to the SQL database
'ODBC (DSN) called - Process_log
'Data Base name - tower
'Table name - Process_log
'table columns - TimeStmp,PLC_Tag,Val,Operation

On Error GoTo ErrorHandler

'Connection
Dim objConnection
Dim strConnectionString
Dim strSQL
Dim objCommand

'SQL Table
Dim SQLID
Dim SQLTimeStmp
Dim SQLPLCTag
Dim SQLVal
Dim SQLOperation

On Error Resume Next

strConnectionString = "Provider=MSDASQL;DSN=Process_Log;UID=;PWD=;"
HT_Table = "Process_log"

' report table
SQLTimeStmp = "'" & Date & " " & Time & "'"
SQLPLC_Tag = "'" & "PLC Tag 1" & "'"
SQLVal = "'" & Sin(NumericDisplay1.Value * 0.1) & "'"
SQLOperation = "'" & "Sine Wave" & "'"

'Debug.Print Sin(NumericDisplay1.Value * 0.1)

strSQL = "INSERT INTO " & HT_Table & _
" (TimeStmp,PLC_Tag,Val,Operation) VALUES (" _
& SQLTimeStmp & "," & SQLPLC_Tag & "," & SQLVal & "," & SQLOperation & ")"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = strSQL
End With

objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing

Exit Sub
ErrorHandler:
LogDiagnosticsMessage Err.Number & " KG Development - auto storing data to SQL"
Resume Next
</pre>

<p>Code to write to an Excel spreadsheet:
<pre>
Private Sub Button5_Released()
' Variable Declarations
Dim objExcel As Object
'no error checking carried out to see in file exists etc.

' If Excel is open, use GetObject, otherwise create a new Excel object
'to test if the excel is already open is not always necessary if you have control of
'what's going on the PC

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
'MsgBox Err.Number

If Err.Number = 429 Then
Set objExcel = CreateObject("Excel.Application")
'If Err.number = 429 Then Set objExcel = New Excel.Application
Set objWorkbook = objExcel.WorkBooks.Open("C:\temp\test.xls")
End If

objExcel.Visible = False 'set to run in the background
objExcel.Cells(1, 1).Value = 123 'writes to cell A1

'this runs a macro in excel but the code could reside here
ObjExcelApp.run ("test.xls!Print_Report")

ObjExcelApp.WorkBooks("test.xls").close (False)
ObjExcelApp.Quit
End Sub
</pre>
<p>Note these examples where taken from a recent project using SE, in fact these will work just as well in WinCC, etc. (with minor tweaks).

<p>Regards,<br>
Kevin

 

Thanks, Kevin.

 

Hi,

I thinking this is what I need for a project, however I use PostgresSQL how do I declare the provider for it?

strConnectionString = "Provider=MSDASQL;DSN=DemoReg;UID=;PWD=;"

I tried provider= POSTGRESQL but it doesn't worked (I was suspecting that but I tried anyway)

Thanks,
Filipe