1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
| Option Explicit
Option Compare Database
' ---
' CONSTANTES
' ---
'
Public Const PARAM_TABLE As String = "tbl Paramètres"
Public Const PARAM_NAME As String = "Nom Paramètre"
Public Const PARAM_VALUE As String = "Valeur Paramètre"
' ---
' CREATION DE LA TABLE DES PARAMETRES
' ---
'
Sub CreateParamTable()
Dim strTable As String
Dim strSQL As String
' Vérifier si la table de paramètres existe
On Error Resume Next
strTable = CurrentDb.TableDefs(PARAM_TABLE).Name
If Err = 0 Then Exit Sub
If MsgBox("La table de paramètres n'existe pas." & vbCrLf _
& "Souhaitez-vous la créer ?", _
vbQuestion + vbYesNo + vbDefaultButton2) = vbNo Then
Exit Sub
End If
On Error GoTo CreateTableErr
strSQL = StringFormat( _
"CREATE TABLE [{0}] (" _
& "[{1}] TEXT(100) PRIMARY KEY," _
& "[{2}] TEXT(255))", _
PARAM_TABLE, PARAM_NAME, PARAM_VALUE)
CurrentDb.Execute strSQL
Access.Application.RefreshDatabaseWindow
MsgBox "Table créée.", vbInformation
Exit Sub
CreateTableErr:
MsgBox "Erreur : " & Err.Number & vbCrLf & Err.Description, _
vbExclamation
Exit Sub
End Sub
' ---
' ECRITURE D'UN PARAMETRE
' ---
'
Sub SetParam( _
ByVal strParamKey As String, _
ByVal varValue As Variant)
' Ouverture de la table
On Error GoTo SetParamErr
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(PARAM_TABLE, dbOpenDynaset)
' Recherche du paramètre (s'il existe)
rst.FindFirst StringFormat("[{0}] = '{1}'", PARAM_NAME, strParamKey)
If rst.NoMatch Then
rst.AddNew
Else
rst.Edit
End If
' Ecriture du paramètre et de sa valeur
rst(PARAM_NAME) = UCase(strParamKey)
rst(PARAM_VALUE) = Nz(varValue, "")
rst.Update
rst.Close
Set rst = Nothing
Exit Sub
SetParamErr:
MsgBox "Erreur : " & Err.Number & vbCrLf & Err.Description, _
vbExclamation
Exit Sub
End Sub
' ---
' LECTURE D'UN PARAMETRE
' ---
'
Function GetParam( _
ByVal strParamKey As String, _
Optional ByVal varDefault As Variant = "") _
As Variant
' Valeur du paramètre
On Error GoTo GetParamErr
GetParam = Nz(DLookup( _
"[" & PARAM_VALUE & "]", _
PARAM_TABLE, _
"[" & PARAM_NAME & "] = '" & UCase(strParamKey) & "'"), _
varDefault)
Exit Function
GetParamErr:
MsgBox "Erreur : " & Err.Number & vbCrLf & Err.Description, _
vbExclamation
Exit Function
End Function
' ---
' SUPPRESSION D'UN PARAMETRE
' ---
'
Sub DelParam(ByVal strParamKey As String)
On Error GoTo DelParamErr
CurrentDb.Execute StringFormat( _
"DELETE * FROM [{0}] WHERE [{1}] = '{2}'", _
PARAM_TABLE, _
PARAM_NAME, _
UCase(strParamKey))
Exit Sub
DelParamErr:
MsgBox "Erreur : " & Err.Number & vbCrLf & Err.Description, _
vbExclamation
Exit Sub
End Sub |
Partager