I am experiencing some weird situation. The following code
can be run successfully in Excel. But when I shift them to
VBScript, I got an error message telling me "Unexpected
Error - Provider: Retrieving Data" on line of retrieving
recordset. And yes, I changed the syntax to clear the
difference beween VB and VBScript.
I checked status step by step and found the database is
opened conn.state = adStateOpen and dbcmd.state = 0. So I
am guessing there should be something wrong with the
Since I am new to this area, could someone please help
The purpose of the following code is to get historical
data from SQL DB through a third party provider. The
format of the command is listed under .CommandText.
Thanks a lot in advance,
Dim conn As ADODB.Connection
Dim dbcmd As ADODB.Command
Dim rs1 As ADODB.Recordset
Dim icols As Integer
Dim ws As Worksheet
tagatom = "110AALI100SEL.PV/SIG"
' tagatom = "SINE1.PV/SIG"
adate = "9/10/2002 9:15:00 AM"
dtime = "9/10/2002 10:00:00 AM"
suffixcode = "1" ' 0=raw,
Interval = "1"
IntervalUnit = "2" '
'Create variables for ADO Connection and Resultset
Set dbcmd = CreateObject("ADODB.Command")
Set conn = CreateObject("ADODB.Connection")
conn.CursorLocation = adUseClient
conn.Mode = adModeRead
conn.Open ("Provider = MNT_Historian")
Set dbcmd.ActiveConnection = conn
dbcmd.CommandType = adCmdText
' dbcmd.CommandType = adRowset
dbcmd.CommandText = tagatom & "," & adate & "," & dtime
& "," & suffixcode & "," & Interval & "," & IntervalUnit
dbcmd.CommandTimeout = 180
Set rs1 = CreateObject("ADODB.recordset")
rs1.CacheSize = 100
rs1.CursorType = adOpenStatic
rs1.LockType = adLockReadOnly
' arrResults = rs1.GetRows
Set ws = Excel.ActiveSheet
If (rs1.EOF = True) Then
Debug.Print "No Return Values!"
For icols = 0 To rs1.Fields.Count - 1
ws.Cells(7, icols + 1).Value = rs1.Fields
ws.Range("A3") = "TagAtom:"
ws.Range("B3") = tagatom
ws.Columns("A").NumberFormat = "mm/dd/yyyy h:mm:ss
Set rs1 = Nothing