Replacing SaveSetting with saving to a parameter file (Acc..

I am trying to replace VB6 SaveSetting command, to use a parameter file
rather than the registry.

I want to make the equivalent of :
SaveSetting appname, section, key, setting
where I pass "key" and "setting" to new function MySaveSetting(), where
appname and section are no longer relevant, i.e. all settings are held in
one record in an Access table

I am seeking a way to pass the field name to a function which opens the
database, creates a recordset (of one record), then edits it and updates
just the field I want to change, then updates it.

How can I make the field name a parameter of the function?

Or should I follow a different approach?

Thanks
David

"David Bernheim" <db> wrote in message

> I am trying to replace VB6 SaveSetting command, to use a parameter file
> rather than the registry.
>
> I want to make the equivalent of :
> SaveSetting appname, section, key, setting
> where I pass "key" and "setting" to new function MySaveSetting(), where
> appname and section are no longer relevant, i.e. all settings are held in
> one record in an Access table
>
> I am seeking a way to pass the field name to a function which opens the
> database, creates a recordset (of one record), then edits it and updates
> just the field I want to change, then updates it.
>
> How can I make the field name a parameter of the function?
>
> Or should I follow a different approach?
>
> Thanks
> David
>

Although I personally think an Access DB is a bit of
overkill for app settings, its a good learning object.
Here's some code you can use/study. I started by going into
Access and creating a simple DB (Config.mdb) with a table
called "Settings". Then I added two text fields, one
called "Key" and the other "Value". Then I wrote the
code below. Since you will probably want a separate
DB for each app, I included a routine to create the DB.
Alternatively, you could have 3 fields in the table
"AppName", "Key", "Value" and save all your settings
to one DB, but I think this would complicate things.

Option Explicit
Private DB As DAO.Database
'**********change this line***********
Private Const DBName As String = "c:\[path\name to your mdb file.mdb]"
Private Sub Form_Load()
CreateDB DBName
Set DB = OpenDatabase(DBName)
SetKey "FormWidth", Me.Width
SetKey "FormHeight", Me.Height
Me.Width = GetKey("FormWidth")
ReadAll
'DelKey "FormHeight"
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Not DB Is Nothing Then
DB.Close
Set DB = Nothing
End If
End Sub

Private Sub SetKey(ByVal Key As String, ByVal Val As String)
Dim RS As DAO.Recordset
'might add some error checking here, e. g. Len(Key) etc
Set RS = DB.OpenRecordset("Select * From Settings Where Key = " &
Quote(Key))
If RS.EOF Then 'key not there so add it
RS.AddNew 'once you have your settings stored,
RS.Fields("Key").Value = Key 'you may want to raise an error here
RS.Fields("Value").Value = Val 'instead of storing some erroneous key
Else 'key exists, so update it
RS.Edit
RS.Fields("Value").Value = Val
End If
RS.Update
RS.Close
Set RS = Nothing
End Sub

Private Function GetKey(ByVal Key As String)
Dim RS As DAO.Recordset
Set RS = DB.OpenRecordset("Select * From Settings Where Key = " &
Quote(Key))
If RS.EOF Then
Debug.Print "Key not found" ' or some such
Else
GetKey = RS.Fields("Value").Value
End If
RS.Close
Set RS = Nothing
End Function

Private Sub DelKey(ByVal Key As String)
Dim RS As DAO.Recordset
Set RS = DB.OpenRecordset("Select * From Settings Where Key = " &
Quote(Key))
If RS.EOF Then
Debug.Print "Key not found" ' or some such
Else
RS.Delete
End If
RS.Close
Set RS = Nothing
End Sub

Private Sub ReadAll()
Dim RS As DAO.Recordset
Dim i As Long
Set RS = DB.OpenRecordset("Settings")
If RS.EOF Then
Debug.Print "No Keys stored" ' or some such
Else
RS.MoveLast
RS.MoveFirst 'reset recordcount
For i = 1 To RS.RecordCount
Debug.Print RS.Fields(0).Value, RS.Fields(1).Value 'or whatever
RS.MoveNext
Next
End If
RS.Close
Set RS = Nothing
End Sub

'helper for sql strings
Private Function Quote(ByVal Str As String) As String
Quote = Chr$(34) & Str & Chr$(34)
End Function

Private Sub CreateDB(ByVal MDBPath As String)
Dim NDb As DAO.Database
Dim TDef As DAO.TableDef
Dim Fld As DAO.Field
'Dir is not the best way to check
'for file exists but I used it here for brevity
If Len(Dir$(MDBPath)) Then
Kill MDBPath 'or raise an error/exit
End If
'create the empty db
Set NDb = CreateDatabase(MDBPath, dbLangGeneral)
'create an empty table definition
Set TDef = NDb.CreateTableDef("Settings")
'create a field for the table
Set Fld = TDef.CreateField("Key", dbText)
Fld.AllowZeroLength = False
Fld.Required = True
TDef.Fields.Append Fld 'add it to the table
Set Fld = TDef.CreateField("Value", dbText)
Fld.AllowZeroLength = False
Fld.Required = True
TDef.Fields.Append Fld 'add this one too
NDb.TableDefs.Append TDef 'add the table
Set Fld = Nothing 'clean up
Set TDef = Nothing
NDb.Close
Set NDb = Nothing
End Sub