Difference between revisions of "Microsoft Access VBA Code Snippets"

From database24
Jump to navigation Jump to search
(Created page with '== modAccess == modAccess consists basically of methods, which are specific to Microsoft Access like methods for retrieving information about properties, checking Access objects ...')
 
 
(89 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
== modAccess ==
+
[[Category:Microsoft Access]]
−
modAccess consists basically of methods, which are specific to Microsoft Access like methods for retrieving information about properties, checking Access objects for their existence.
+
[[Category:VBA]]
 +
== General ==
  
−
=== getDbAppTitle ===
+
=== ActiveX Data Objects (ADO) ===
−
Function getDbAppTitle() As String
+
Retrieving and processing a recordset works like this
−
    Dim strResult As String
 
−
   
 
−
    strResult = CurrentDb.Properties("AppTitle").Value
 
−
   
 
−
    getDbAppTitle = strResult
 
−
End Function
 
  
−
=== getProjectName ===
+
<syntaxhighlight lang="vb">
−
Function getProjectName() As String
+
    Dim cnn As ADODB.Connection
−
    Dim strResult As String
+
    Dim rst As ADODB.Recordset
−
   
+
   
−
    strResult = CurrentDb.Properties("AppTitle").Value
+
    Set cnn = CurrentProject.Connection
−
   
+
    Set rst = New ADODB.Recordset
−
    getDbAppTitle = strResult
+
    With rst
−
End Function
+
        .Open _
 +
            Source:="SELECT * FROM tblTable", _
 +
            ActiveConnection:=cnn
 +
        .MoveFirst
 +
        Do While Not .EOF
 +
            'Record based instructions
 +
            .MoveNext
 +
        Loop
 +
        .Close
 +
    End With
 +
</syntaxhighlight>
  
−
=== existsTable ===
+
=== Setter and Getter ===
−
Function existsTable(strTable As String) As Boolean
+
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:
−
    Dim blnResult As Boolean
 
−
    Dim tdf As TableDef
 
−
   
 
−
    blnResult = False
 
−
    For Each tdf In CurrentDb.TableDefs
 
−
        If tdf.Name = strTable Then
 
−
            blnResult = True
 
−
            Exit For
 
−
        End If
 
−
    Next
 
−
   
 
−
    existsTable = blnResult
 
−
End Function
 
  
−
=== existsQuery ===
+
<syntaxhighlight lang="vb" line start="10" highlight="2">
−
Function existsQuery(strQuery As String) As Boolean
+
Public Sub setId(lngId As Long)
−
    Dim blnResult As Boolean
+
    SaveSetting getProjectName, "RunTime", "Id", CStr(lngId)
−
    Dim qdf As QueryDef
+
End Sub
−
   
+
</syntaxhighlight>
−
    blnResult = False
+
 
−
    For Each qdf In CurrentDb.QueryDefs
+
<syntaxhighlight lang="vb" line start="20" highlight="4">
−
        If qdf.Name = strQuery Then
+
Public Function getId() As Long
−
            blnResult = True
+
    Dim lngResult As Long
−
            Exit For
+
   
−
        End If
+
    lngResult = CLng(GetSetting(getProjectName, "RunTime", "Id", 0))
−
    Next
+
   
−
   
+
    getId = lngResult
−
    existsQuery = blnResult
+
End Function
−
End Function
+
</syntaxhighlight>
 +
 
 +
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 ("set''Id''", "get''Id''").
 +
 
 +
* Lines 10, 20ff: The variable type should always be converted explicitly, although VBA is able to cast implicit conversions (like strResult = datNow).
 +
 
 +
== Modules ==
 +
* [[Microsoft Access modAccess|modAccess]]
 +
* [[Microsoft Access modSetting|modSetting]]
 +
* [[Microsoft Access modUi|modUi]]
 +
* [[Microsoft Access modFunction|modFunction]]
 +
* [[Microsoft Access modSql|modSql]]
 +
* [[Microsoft Access modFso|modFso]]
 +
* [[Microsoft Access modWsh|modWsh]]
 +
* [[Microsoft Access modExcel|modExcel]]
 +
* [[Microsoft Access modString|modString]]

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).

Modules

Retrieved from "http://wiki.database24.net/index.php?title=Microsoft_Access_VBA_Code_Snippets&oldid=289"