Thursday, August 11, 2005


Rule 18: Using DB Code

Here is an example of using the DB Code

' Load Duration sheet
Sub Load_Duration()
Dim sqlQry As String
Dim ds As String
Dim dbConn As ADODB.Connection

ds = Format(Worksheets("Main").Range("Risk_Date"), "YYYYMMDD")
sqlQry = "select isin, duration, date from durations where date = '" & ds & "'"

Set dbConn = Db.OpenConnection_ODBC(GetDBParams("FASER", "_SERVER"), _
GetDBParams("FASER", "_DB"), _
GetDBParams("FASER", "_USER"), _
GetDBParams("FASER", "_PASSWORD"))
Call SQLToRangeName(dbConn, sqlQry, WSDuration, "Duration", True)

If WSDuration.Range("Duration").Rows.count < 2 Then
AddLogItem ("Load_Duration : Warning - no data retrieved for Duration")
End If
End Sub

Pretty easy. The parameters are straight forward extracts from a sheet or a parameter file.

Setting up the sql query is easy.

Results go to one sheet.

To further use the results, vlookups are the easy way. That means the first column should be the key for the table. The vlookup is then

=vlookup (key, Duration, 2, FALSE)
Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]