[AccessD] How to do an Admin Table

I have a table in my databases that is called tblProperty and it has the
following structure:

- PropertyInternalNumber (Autonumber)
-PropertyName (Text:255)
-PropertyValue (Text:255)

Then I have functions that can retrieve and update the values as needed.

Function GetPropertyValue(myPropertyName As String, myconnection As String)
As String
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmdtext As String
Dim mymessage As String

On Error GoTo GetPropertyValueError

con = myconnection
con.Open

cmdtext = "SELECT tblProperty.PropertyValue"
cmdtext = cmdtext & " FROM tblProperty"
cmdtext = cmdtext & " WHERE tblProperty.PropertyName = '" & myPropertyName
& "';"

rs.Open cmdtext, con

If Not rs.EOF And Not rs.BOF Then 'Property is present
    rs.MoveFirst
    GetPropertyValue = rs.Fields("PropertyValue")
    con.Close
Else
    con.Close
    GetPropertyValue = "Unknown"
End If

Exit Function

GetPropertyValueError:
    GetPropertyValue = "Unknown"
End Function

Function SetPropertyValue(myPropertyName As String, mypropertyvalue As
String, myconnection As String)
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmdtext As String
Dim mymessage As String

On Error GoTo GetPropertyValueError

con = myconnection
con.Open

cmdtext = "UPDATE"
cmdtext = cmdtext & " tblProperty"
cmdtext = cmdtext & " SET"
cmdtext = cmdtext & " tblProperty.PropertyValue =" & Chr(34) &
mypropertyvalue & Chr(34)
cmdtext = cmdtext & " WHERE"
cmdtext = cmdtext & " tblProperty.PropertyName=" & Chr(34) & myPropertyName
& Chr(34) & ";"

rs.Open cmdtext, con

con.Close

Exit Function

GetPropertyValueError:
    SetPropertyValue = "Unknown"
End Function

I can store an custom "property" and change it.  For example I have the
version of the program, the program name, last time interest was
caluculated, etc..

I believe I saw the idea orginally from John Colby.

-Jeff


                                                                                                                                              
                    "John Clark"                                                                                                              
                    <John.Clark at niagaracounty.       To:     accessd at databaseadvisors.com                                                     
                    com>                             cc:                                                                                      
                    Sent by:                         Subject:     [AccessD] How to do an Admin Table                                          
                    accessd-bounces at databasead                                                                                                
                    visors.com                                                                                                                
                                                                                                                                              
                                                                                                                                              
                    02/06/2004 01:24 PM                                                                                                       
                    Please respond to "Access                                                                                                 
                    Developers discussion and                                                                                                 
                    problem solving"                                                                                                          
                                                                                                                                              
                                                                                                                                              




Forgive me, if this is an easy one, but I've even asked someone that
teaches the Microsoft Access certification classes, and they didn't know
how to do this either--or maybe I wasn't clear, when I asked.

I have had many programs where it would be useful to have an "admin"
table. This table would contain one single record with information
referenced in other parts of the program. For instance, I have a program
that figures monthly mileage for our county legislators, but it is
currently setup so that the rates are hard-coded and I have to alter
them everytime they are changed. I would like the user to alter this
themselves.

I have also thought about using this for programs--maybe in their
splash screens--to list the current directors names, and other info that
may or may not change in the future.

Any ideas how?

Thanks ahead of time...because I know if it can be done, somebody on
this list knows how to do it!!!

John W Clark

John W. Clark
Computer Programmer
Niagara County
Central Data Processing
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com