Difference between revisions of "Microsoft Access VBA Code Snippets"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
| − | == ActiveX Data Objects (ADO) == | + | == General == |
| + | |||
| + | === ActiveX Data Objects (ADO) === | ||
Retrieving and processing a recordset works like this | Retrieving and processing a recordset works like this | ||
| Line 21: | Line 23: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | == Setter and Getter == | + | === Setter and Getter === |
In order to store and retrieve settings from the user's registry, it is wise to implement Setters and Getters. The general code for a variable named "Id" of the variable type "Long" looks like this: | In order to store and retrieve settings from the user's registry, it is wise to implement Setters and Getters. The general code for a variable named "Id" of the variable type "Long" looks like this: | ||
Revision as of 14:05, 1 July 2010
General
ActiveX Data Objects (ADO)
Retrieving and processing a recordset works like this
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
.Open _
Source:="SELECT * FROM tblTable", _
ActiveConnection:=cn
.MoveFirst
Do While Not .EOF
'Record based instructions
.MoveNext
Loop
.Close
End With
Setter and Getter
In order to store and retrieve settings from the user's registry, it is wise to implement Setters and Getters. The general code for a variable named "Id" of the variable type "Long" looks like this:
10 Public Sub setId(lngId As Long)
11 SaveSetting getProjectName, "RunTime", "Id", CStr(lngId)
12 End Sub
20 Public Function getId() As Long
21 Dim lngResult As Long
22
23 lngResult = CLng(GetSetting(getProjectName, "RunTime", "Id", 0))
24
25 getId = lngResult
26 End Function
A few remarks on this code:
- Lines 11 and 23: "RunTime" just indicates that the variable will change frequently while working with the database. You may want to use different "categories" for your settings such as "RunTime", "Import", "User" and so on in order to reflect either the scope or the frequency of the settings. If you are using distinct namespaces like this, you should consider putting the namespace into the names of the functions, so that on the one hand it is possible to tell from the names which settings are meant and on the other hand you could reuse a term like Id in both namespaces; example: getId vs getImportId
- Lines 11 and 23: The setting's name ("Id") should be the same that the functions have ("setId", "getId").
- Lines 10, 20ff: The variable type should always be converted explicitly, although VBA is able to cast implicit conversions (like strResult = datNow).