Multisession Variables, keep value after close?

[bignose2]

Hi,

Access 2007 accdb & accde/runtime (recently upgraded from 2003 mdb)

I found the code below (Hope I am not breeching any rights doing this)that seems to simplify my age old issue of storing simple variable values that I would like to keep after closing the DB.
I do use tables but always seems over complicated for a simple requirement, I have tables and use dlookup to get the values, but its not pretty, always need to make sure only 1 record in the table (OK easily done).

It basically does the table handling for you easily creating new fields & data type etc etc. nice clear instructions.

WriteGV "strLastName", strText, "Soto"
Me.Test = ReadGV("strLastName", strText)

HOWEVER I get a compile error on Dim rst As ADODB.Recordset
I guess I need to tick another reference BUT Not keen on this idea, firstly extra references don’t always seem to port to other computers, especially in runtime without full access installation.
Secondly, & more important is I am little worried adding a extra type of recordset/DB I am worried if I have not declared specifically throughout my very large database I might get unexpected result that don’t show up until too late.
I am no expert and might be talking rubbish, I wrote most a long time ago & just make little additions from time to time.
Can I declare it differently, what if I take out the adodb.?
Is adding this reference 100% sure NOT to effect anything even if I have declared recordsets previously with perhaps poor protocol?
(I did ask this on the original posting below but quite old & not sure anyone will be reading it)

https://accessexperts.com/blog/2011/01/12/multi-session-global-variables/#comment-301323
Set 1 create table
Step 2: Create module level Enum
In a stand alone module, insert the following code at the top before your first procedure::
Enum ProgramOptions
strText = 1
lngNumber = 2
dteDate = 3
logLogical = 4
End Enum
Step 3: Add the following three methods to the module:
Note: If you don’t wrap this line of code then you don’t need the line continuation character, “_”, used below.
Public Function ReadGV(strVariableName As String, strVariableType As _
ProgramOptions) As Variant
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenStatic
.ActiveConnection = CurrentProject.Connection
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.Source = "tblProgramOptions"
.Open
If .State = adStateClosed Then
GoTo ErrorProcessor
End If
.MoveFirst
.Find "[OptionName] = '" & strVariableName & "'"
If .EOF Then
'No match found, return Null
ReadGV = Null
Else
ReadGV = .Fields(strVariableType)
End If
.Close
End With

Set rst = Nothing
Exit Function

ErrorProcessor:
‘Insert your error handling code here
Exit Function
End Function
Public Function WriteGV(strVariableName As String, strVariableType As _
ProgramOptions, varValue As Variant) As Boolean
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenKeyset
.ActiveConnection = CurrentProject.Connection
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Source = "tblProgramOptions"
.Open
If .State = adStateClosed Then
GoTo ErrorProcessor
End If
.MoveFirst
.Find "[OptionName] = '" & strVariableName & "'"
If .EOF Then
'No match found, add new record to table
.AddNew
!OptionName = strVariableName
If Not varValue = "" Then
.Fields(strVariableType) = varValue
End If
.Update
Else
'Match found, update value of variable
If Not varValue = "" Then
.Fields(strVariableType) = varValue
End If
.Update
End If
.Close
End With

Set rst = Nothing
Exit Function

ErrorProcessor:

Exit Function
End Function
Public Function DeleteGV(strVariableName As String) As Boolean
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenKeyset
.ActiveConnection = CurrentProject.Connection
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.Source = "tblProgramOptions"
.Open
If .State = adStateClosed Then
GoTo ErrorProcessor
End If
.MoveFirst
.Find "[OptionName] = '" & strVariableName & "'"
If .EOF Then
'No match found, exit
DeleteGV = True
Else
'Match found, delete record
.Delete
DeleteGV = True
End If
.Close
End With

Set rst = Nothing
Exit Function

ErrorProcessor:
DeleteGV = False
Exit Function
End Function

 

[Galaxiom]

I do use tables but always seems over complicated for a simple requirement, I have tables and use dlookup to get the values, but its not pretty, always need to make sure only 1 record in the table (OK easily done).

It basically does the table handling for you easily creating new fields & data type etc etc. nice clear instructions.

It doesn't create new fields for each variable. It uses a table with a record for each variable and a field for its value rather than a field named for each variable which I gather you are doing.

It uses an ADODB recordset for reasons that are not obvious. A DAO recordset could do the same job.

If you really wish to use ADODB without a reference you would need to use Late Binding.

Dim rst As Object
Set rst = CreateObject("ADODB.Recordset")

All the adXXX constants would need to be provided as integers in your module.

 

[bignose2]

HI,

Thank you for that, I guess the original function was nothing special but much better than anything I would have thought up but in the end I have pretty much re-written in DAO, which required quite a lot of changes.
I imagine I should have started from scratch rather than changing each bit as I think it is rather messy & perhaps poor coding, if else, if else etc.

Does seem to work & quite a neat result, I wish I had had many years ago.

Don't know if you have a second to glance over the code & if anything really bad. I don't have the time nowadays to really study & understand, rather a lot of trial & error.

I did add a date field, last used, to the code, I figured quite handy to look back at the table and see if perhaps a GV variable is not really being used (if ever)



Enum ProgramOptions
strText = 1
lngNumber = 2
dteDate = 3
logLogical = 4
End Enum


' === Step 1: Create tblProgramOptions table to store your global variables ===
'
' WriteGV "strLastName", strText, "Soto"
' Me.test = ReadGV("strLastName", strText)
' MsgBox Nz(ReadGV("MMMDelay", strText), "null")

' DoCmd.OpenTable "tblProgramOptions", acViewNormal


Public Function ReadGV(strVariableName As String, strVariableType As ProgramOptions) As Variant
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblProgramOptions", dbOpenDynaset)
With rst
.MoveFirst
.FindFirst "[OptionName] = '" & strVariableName & "'"
If .NoMatch Then
ReadGV = Null
Else
ReadGV = .Fields(strVariableType)
End If
.Close
End With

Set rst = Nothing
Exit Function

ErrorProcessor:
'Insert your error handling code here
Exit Function
End Function
Public Function WriteGV(strVariableName As String, strVariableType As ProgramOptions, varValue As Variant) As Boolean
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblProgramOptions", dbOpenDynaset)
With rst
If .RecordCount = 0 Then
'Empty table completely, add new record to table
.AddNew
!OptionName = strVariableName
If Not varValue = "" Then
.Fields(strVariableType) = varValue
End If
.Update
Else ' Are records
.MoveFirst
.FindFirst "[OptionName] = '" & strVariableName & "'"
If .NoMatch Then ' are records but no match so add new
.AddNew
!OptionName = strVariableName
!LastUsed = Date
If Not varValue = "" Then
.Fields(strVariableType) = varValue
End If
.Update
Else ' are records so overwrite
If Not varValue = "" Then
.Edit
!LastUsed = Date
.Fields(strVariableType) = varValue
End If
.Update

End If

End If
.Close
End With

Set rst = Nothing
Exit Function

ErrorProcessor:

Exit Function
End Function
Public Function DeleteGV(strVariableName As String) As Boolean
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblProgramOptions", dbOpenDynaset)
With rst
.MoveFirst
.FindFirst "[OptionName] = '" & strVariableName & "'"
If .NoMatch Then
'No match found, nothing to delete anyway, exit
DeleteGV = True
Else
'Match found, delete record
.Delete
DeleteGV = True
End If
.Close
End With

Set rst = Nothing
Exit Function

ErrorProcessor:
DeleteGV = False
Exit Function
End Function

 

[MarkK]

In ReadGV, I wouldn't open the huge recordset and then search it, rather, open exactly the recordset you need. Consider . . .

Public Function ReadGV(OptionName As String, OptionType As ProgramOptions) As Variant
    Dim sql As String
    sql = _
        "SELECT * FROM tblProgramOptions " & _
        "WHERE OptionName = '" & OptionName & "'"
    With CurrentDb.OpenRecordset(sql)
        If Not .EOF Then
            ReadGV = .Fields(OptionType)
        Else
            ReadGV = Null
        End If
        .Close
    End With
End Function

Also,

 tags are nice if you post code.
Also . . .
[CODE]Public Function DeleteGV(OptionName As String) As Boolean
    With CurrentDb
        .Execute _
            "DELETE FROM tblProgramOptions " & _
            "WHERE OptionName = '" & OptionName & "'", dbFailOnError
        DeleteGV = .RecordsAffected
    End With
End Function

Cheers,