Control.com - Nerds in Control

Hi,

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;

This has a numeric display on a screen that changes every second:

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

Code to write to an Excel spreadsheet:

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

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

Regards,<br>
Kevin