AnsweredAssumed Answered

Attempting to draw data from PI using VBA in Access

Question asked by Kcrochet on Jul 8, 2019
Latest reply on Jul 11, 2019 by TimCarmichael

Sub subGetTVScompPthru(vtblTarget As String, vTag As String, _
Optional opStartTime As Date, Optional opStopTime As Date)

Set DB = CurrentDb


If CStr(opStartTime) <> "12:00:00 AM" Then
BOM = Str(opStartTime)
Else
BOM = Str(Forms!frmStart_Stop!StartTime)
End If
If CStr(opStopTime) <> "12:00:00 AM" Then
EOM = Str(opStopTime)
Else
EOM = Forms!frmStart_Stop!StopTime
End If

Call subQueryDrop("qryPthrough", True) 'Note: SQL does not accept "qryPthru"; causes fault
Set QDF = DB.CreateQueryDef("qryPthrough")
QDF.Connect = "ODBC;DSN=PI_ODBC_SYS;"
QDF.SQL = "SELECT time, value, digstring(status) FROM picomp " _
& "WHERE tag= '" & vTag & "' " _
& "AND [time]>'" & BOM & "' " _
& "And [time]<'" & EOM & "';"

Call subTableDrop(vtblTarget)
DB.Execute "SELECT * INTO " & vtblTarget & " FROM qryPthrough ORDER BY [time];"
DoCmd.DeleteObject acQuery, "qryPthrough"

 

Historically, the code worked using the # date # notation but something in the OSI program changed from, say, year 2017 to 2018.  Apparently, the program needs all the parameters to be strings.  If it matters, the PI tag (vTag) is 

WATERWELL/FI-7112/AI1/PV.CV.  Am using office 2016.

Outcomes