Best Way to Store Constants 2

[drtree]

Hey all, as usual, thanks for any help/advice. I am going through an old application I built in Access 2000, which relied heavily on VBA. I'm moving a lot of the functionality to Access (hey - I'm a newbie, its ok to reinvent the wheel, right ), and have a question regarding constant declarations. I've been thinking about simply creating a table called 'System' and declaring all my constants in there, but I'm confused as to what the best way to access them would be. Does anyone have experience regarding this situation? Should I just make a function to call them and, at runtime, do a type conversion (right now ALL data in the table is stored as text)? How would I be exactly sure that I'm getting the right variable / data? Would it be adding an extra layer (ie, instead of just declaring it, I have to query the system to get it, convert it, etc)? As you can see, I've got a lot of questions still..thanks...

 

[psemianonymous]

I use a "GLOBALS" table and store some variables I believe may be changed in the future (i.e. the "export to" directory). Then I have a function getExportToDirectory() which will retrieve the value.


Truly *constant* variables you can store pretty much anywhere. You can either create a code module and do "Public myVariableName as String", but there are some tradeoffs. There have been a ton of threads on this discussion:

http://groups.google.com/groups?hl=...ariables"&meta=group=comp.databases.ms-access

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[JimInKS]

I have a module I call 'Declarations' Where I declare constants and all globals. Constants are good to use for readability and to allow for changes at the design level. For example:

Public Const UsrErr_DupIndex As Long = 3022

For values that may change at run time, such as directory names I use a table I call CONFIG. To access values from CONFIG I try to use a function to add a level of abstraction in my code. By using a Static variable to hold the return value for the function the table is only accessed once, subsequent calls to the function just return the value already saved. This will help performance greatly if this happens to be a value referred to often.
My code looks something like this:

Function DbName() as string
    Static strDbName As String
    On Error GoTo HandleErr
    If strDbName = "" Then strDbName = GetConfig("dbname")
    DbName = strDbName
    Exit Function
HandleErr:
    ErrorMsg
End Function

Function GetConfig(FieldName As String) As Variant
    Dim db As Database
    Dim rst As Recordset
    On Error GoTo HandleErr
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Config")
    GetConfig = rst(FieldName)
    rst.close
    set rst=nothing
    set db=nothing
    Exit Function
HandleErr:
    Errmsg
End Function