In the Handicap UDF, there’s a call to another UDF: GetStartingHC. Every golfer has a handicap to start and it’s stored in TblPlayers. For most golfers, it’s a carry over from last season. For new members, it can be their USGA handicap. When a golfer doesn’t have three scores, the starting handicap is used as one of the scores.
Dim rsPlayers As ADODB.Recordset
Const sBEGHC As String = “BegHC”
If gadoCon Is Nothing Then
InitGlobals
End If
Set rsPlayers = New ADODB.Recordset
rsPlayers.Open “TblPlayers”, gadoCon, adOpenDynamic
rsPlayers.MoveFirst
rsPlayers.Find “Player = “ & lPlyr
If Not rsPlayers.EOF Then
GetStartingHC = CLng((rsPlayers.Fields(sBEGHC) / 0.8) + 36)
End If
End Function
Nothing too fancy, just using the Find method to get to the proper record quickly. This recordset is definitely a candidate for moving global so it doesn’t need to be created and destroyed every time the function is called. I so skillfully created a constant to refer to the field in the recordset, but I leave 0.8 and36 as literals. Shameful. If this program will ever be useful outside of my league, those are going to be user settings. I need to make them user settings right now instead of being so lazy.
DK: “I so skillfully created a constant to refer to the field in the recordset, but I leave 0.8 and36 as literals. Shameful. If this program will ever be useful outside of my league, those are going to be user settings. I need to make them user settings right now instead of being so lazy.”
If you think of the tables and their columns in your database as being ‘classes’ and their columns as ‘members’ for your front end application use, you’ll see it is reasonable to refer to them as literals (you don’t use CallByName too much, do you :) ?). What might start out as a maintenance-assisting exercise (i.e. if the column name ever changed you’d only need to amend one place in client code) may end up with code such as:
Const TABLE_PLAYERS_NAME As String = “TblPlayers”
Const TABLE_PLAYERS_COL_PLAYER_ID As String = “Player”
Const TABLE_PLAYERS_COL_BEGHC_NAME As String = “BegHC”
Then you realize you could organize this in a collection class of tables each with dependent collection class of columns, just to store meta data element names. Then you realize you can query the database’s data dictionary, schema system tables or equivalent to dynamically populate your schema objects. Then you realize you have an application that seemingly has no clue about its database until run-time.
That is not the way to create tiered architecture. Changing the underlying database structure will always have non-trivial implications for a front end application.
My take on it is that referring to members as literals such as “SELECT Player, BegHC FROM TblPlayers” and Fields(“BegHC”) is entirely acceptable. I think putting 0.8 and 36 into user settings is correct. I’d also suggest changing the global variables for your connection and recordset objects into respective Property Get’s e.g. would be able to test the state etc of the underlying ADODB object before returning a reference to it.
Jamie.