Difference between revisions of "Microsoft Access VBA Code Snippets"
Jump to navigation
Jump to search
| (3 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | == ActiveX Data Objects (ADO) == | + | [[Category:Microsoft Access]] |
| + | [[Category:VBA]] | ||
| + | == General == | ||
| + | |||
| + | === ActiveX Data Objects (ADO) === | ||
Retrieving and processing a recordset works like this | Retrieving and processing a recordset works like this | ||
<syntaxhighlight lang="vb"> | <syntaxhighlight lang="vb"> | ||
| − | Dim | + | Dim cnn As ADODB.Connection |
| − | Dim | + | Dim rst As ADODB.Recordset |
| − | Set | + | Set cnn = CurrentProject.Connection |
| − | Set | + | Set rst = New ADODB.Recordset |
| − | With | + | With rst |
.Open _ | .Open _ | ||
Source:="SELECT * FROM tblTable", _ | Source:="SELECT * FROM tblTable", _ | ||
| − | ActiveConnection:= | + | ActiveConnection:=cnn |
.MoveFirst | .MoveFirst | ||
Do While Not .EOF | Do While Not .EOF | ||
| Line 21: | Line 25: | ||
</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: | ||
| Line 49: | Line 53: | ||
== Modules == | == Modules == | ||
| − | * [[Microsoft Access modAccess|modAccess]] | + | * [[Microsoft Access modAccess|modAccess]] |
* [[Microsoft Access modSetting|modSetting]] | * [[Microsoft Access modSetting|modSetting]] | ||
* [[Microsoft Access modUi|modUi]] | * [[Microsoft Access modUi|modUi]] | ||
Latest revision as of 00:01, 10 August 2010
General
ActiveX Data Objects (ADO)
Retrieving and processing a recordset works like this
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
.Open _
Source:="SELECT * FROM tblTable", _
ActiveConnection:=cnn
.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).