The following example uses Excel to access the WIMS database (MS SQL 2005) via Microsoft VB 6.0(or VBA) and ADO (ActiveX Data Objects). All examples are in VB 6.0.
NOTE: You should be familiar with VB and basic programming techniques to use this section. HACH offers NO support for any custom application and information provided here is intended for programmers.
Step 1: Add a user/login into MS SQL Server for use in reading data. We sugguest you add the Login name "WIMSRO" with a password of READONLY with data reader access to all your WIMS Facility databases:
Step 2: In the Excel Visual Basic Developer add a reference to ADO. Go to Tools, References and add a reference to Microsoft ActiveX Data Objects 2.7 Library:
Step 3: Paste the following code into a new VB Module. Edit the DB_LOGIN information highlighted:
'===========================================================================================================
'DISCLAIMER : Use this code at your own risk. HACH is not liable for any damage caused by execution
' of this code and/or any modified version of it.
'===========================================================================================================
'===========================================================================================================
'NOTES: This code snippet in its original form has been tested to be safe and working. However, it
' has not been in any way optimized for speed and server resource utilization.
'===========================================================================================================
'===========================================================================================================
'local constants - these must be configured to match your SQL server environment
Const DB_SERVER_NAME As String = "(local)\OPSSQL" 'hold the name of your SQL 2000 or later server
Const DB_LOGIN_USER As String = "WIMSRO" 'holds the login name of your user
Const DB_LOGIN_PASSWORD As String = "READONLY" 'holds the login password of of your user
Const DB_LOGIN_DATABASE As String = "OPSWWSALES" 'holds the database name to connect to
Dim cnn As New ADODB.Connection
'===========================================================================================================
'===========================================================================================================
' GetSQL_RawValue
' DESC : function returns a value of an OPS SQL datapoint uniquely identified by its VARID and DATESTAMP
' INPUT : VarId = Variable ID, Currdate = DATESTAMP
' OUTPUT: If a value is found it is returned. Otherwise a blank string is returned
'===========================================================================================================
Function GetSQL_RawValue(VarId As Long, CurrDate As Date)
'declare local varibales
Dim rs As New ADODB.Recordset
Dim source_table As String
Dim date_string As String
Dim SQL As String
On Error GoTo errors_happen:
'Exit Function
If cnn.State <> 1 Then
'setup a database connection
cnn.ConnectionString = "Provider=SQLNCLI;" 'driver name SQLOLEDB for 2000
cnn.ConnectionString = cnn.ConnectionString & "Data Source=" & DB_SERVER_NAME 'server name
cnn.ConnectionString = cnn.ConnectionString & ";initial catalog=" & DB_LOGIN_DATABASE 'database name
cnn.ConnectionString = cnn.ConnectionString & ";User Id=" & DB_LOGIN_USER 'login as
cnn.ConnectionString = cnn.ConnectionString & ";Password=" & DB_LOGIN_PASSWORD 'login password
cnn.ConnectionString = cnn.ConnectionString & ";OLE DB Services =-2;"
cnn.CursorLocation = adUseClient
'attempt to establish database connection
cnn.Open
End If
'set the cursor location
'set the source table for records. Remember that different variable frequencies are stored in different tables
'also make sure that your logged in MSSQL user has been granted SELECT priviledges on the particular table
source_table = "DATATBL"
'translate the date argument into an a valid SQL syntax
date_string = SQLDate(CurrDate)
'generate SQL query
SQL = "SELECT CURVALUE FROM DBO." & source_table & " WHERE VARID = " & VarId & " AND DATESTAMP = '" & date_string & "'"
'run the query against the open connection
rs.Open SQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
If Not rs.EOF Then
GetSQL_RawValue = rs(0) 'query returned a match
Else
GetSQL_RawValue = "" 'query returned no match
End If
'close connection
rs.Close
'cnn.Close
'return control to the parent procedure
Exit Function
errors_happen:
'catch errors that may occur
'generate error message text
msg$ = "Error Occured in function GetSQL_RawValue" & Chr(13) & Chr(10)
msg$ = msg$ & "Details : " & cnn.Errors.Item(0).Number & " - " & cnn.Errors.Item(0).Description
'display error message
MsgBox msg$, vbExclamation + vbOKOnly
GetSQL_RawValue = ""
End Function
'===========================================================================================================
' GetSQL
' DESC : function returns a value that represents an agregate result of a query for a particular variable
' id in a specified date-range
' INPUT : VarId = Variable ID, StartDate = Start DATESTAMP, EndDate = End DATESTAMP
' Stat = AGREGATE STATISTIC {MAX,MIN,AVG,SUM, etc}
' OUTPUT: If a value is found it is returned. Otherwise a blank string is returned
'===========================================================================================================
Function GetSQL(ByVal VarId As Long, ByVal Stat As String, ByVal StartDate As Date, ByVal EndDate As Date)
'declare local varibales
'Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim source_table As String
Dim date_string As String
Dim date_string2 As String
Dim SQL As String
On Error GoTo errors_happen:
If cnn.State <> 1 Then
'setup a database connection
cnn.ConnectionString = "Provider=SQLOLEDB;" 'driver name
cnn.ConnectionString = cnn.ConnectionString & "Data Source=" & DB_SERVER_NAME 'server name
cnn.ConnectionString = cnn.ConnectionString & ";initial catalog=" & DB_LOGIN_DATABASE 'database name
cnn.ConnectionString = cnn.ConnectionString & ";User Id=" & DB_LOGIN_USER 'login as
cnn.ConnectionString = cnn.ConnectionString & ";Password=" & DB_LOGIN_PASSWORD 'login password
cnn.ConnectionString = cnn.ConnectionString & ";OLE DB Services =-2;"
'set the cursor location
cnn.CursorLocation = adUseClient
'attempt to establish database connection
cnn.Open
End If
'set the source table for records. Remember that different variable frequencies are stored in different tables
'also make sure that your logged in MSSQL user has been granted SELECT priviledges on the particular table
source_table = "DATATBL"
'translate the date arguments into an a valid SQL syntax
date_string = SQLDate(StartDate)
date_string2 = SQLDate(EndDate)
'generate SQL query
SQL = "SELECT " & Stat & "(CURVALUE) FROM DBO." & source_table & " WHERE VARID = " & VarId & " AND DATESTAMP >= '" & date_string & "' AND DATESTAMP <'" & date_string2 & "'"
'run the query against the open connection
rs.Open SQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
If Not rs.EOF Then
GetSQL = rs(0) 'query returned a value
Else
GetSQL = "" 'query returned no match
End If
'close connection
rs.Close
'cnn.Close
'return control to the parent procedure
Exit Function
errors_happen:
'catch errors that may occur
'generate error message text
msg$ = "Error Occured in function GetSQL" & Chr(13) & Chr(10)
msg$ = msg$ & "Details : " & cnn.Errors.Item(0).Number & " - " & cnn.Errors.Item(0).Description
'display error message
MsgBox msg$, vbExclamation + vbOKOnly
GetSQL = ""
End Function
'===========================================================================================================
'a helper function
Private Function SQLDate(ByVal TempDate As Date) As String
SQLDate = Format(TempDate, "yyyy-mm-dd") 'MSSQL
End Function
'===========================================================================================================
Step 4: Enter the following formula in Excel:
=GETSQL_RawValue(1,"1/1/2009")
The GETSQL_RawValue(1,"1/1/2009") returns the value for the VARID (NOT VARNUM) 1 for the date 1/1/2009.
Step 5: Enter the GETSQL formula in cell B4:
=GETSQL(1,"AVG","1/1/2009","1/15/2009")
You have now retrieved data from the WIMS Database into Excel. You can save your file and expand on it.
NOTE: HACH offers NO Support on using these functions. If you need assistance it will require a services contract.