Access Archon #152
The Sample Forms
The sample database has two forms, frmGetFromDBProps and frmSaveToDBProps. frmGetFromDBProps has an Open event procedure that first clears and then refills a table with the current database properties, and requeries the cboSelectProperty combo box. The txtPropertyValue field displays the property value from the 2nd column of the combo box.
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim prp As DAO.Property
‘Clear table of old property names
strSQL = “DELETE * FROM tblDBProperties”
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
‘Fill table with current property names
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblDBProperties”)
On Error Resume Next
For Each prp In dbs.Properties
rst.AddNew
rst![PropertyName] = prp.Name
rst![PropertyValue] = Nz(prp.Value)
rst.Update
Next prp
Me![cboSelectProperty].Requery
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number _
& “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
The form is shown in Figure A:
Figure A. A form that lets you select existing database properties
On the other form, frmSaveToDBProps, you can enter a name and value, and select a data type for creating a custom database property. This form is shown in Figure B.
Figure B. A form for creating custom database properties
The code on the Save Property command button is listed below:
Private Sub cmdSaveProperty_Click()
On Error GoTo ErrorHandler
Dim strPropertyName As String
Dim lngDataType As Long
Dim varPropertyValue As Variant
strPropertyName = Nz(Me![txtPropertyName].Value)
If strPropertyName = “” Then
GoTo ErrorHandlerExit
End If
lngDataType = Nz(Me![cboPropertyDataType], 10)
varPropertyValue = Nz(Me![txtPropertyValue].Value, “Default value”)
Call SetProperty(strPropertyName, lngDataType, _
varPropertyValue)
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number _
& “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
After creating a property, it will be listed the next time you open frmGetDBProps, as shown in Figure C:
Figure C. A newly created property in the Select Property drop-down list
If you enter a name for an existing database property on this form, the value you enter will override the saved value.
References
The code in the sample database needs the following reference (in addition to the default references):
Microsoft DAO 3.6 Object Library
If you import code or objects into a database of your own, you may need to set this reference. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch152.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Database Properties (AA152).mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
