Difference between revisions of "Microsoft Access VBA Code Snippets"

From database24
Jump to navigation Jump to search
 
(87 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">
−
The first guess for this is usually '''CurrentProject.Name''' but unfortunately this just returns the name of the file. If you want to bind your settings to a certain project, you certainly don't want to rely on the exact naming of a file; to the contrary you want to be able to use your stored settings no matter what the database file is named.
+
    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
 +
</syntaxhighlight>
  
−
Function getProjectName() As String
+
=== Setter and Getter ===
−
    Dim strResult As String
+
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:
−
   
 
−
    strResult = Application.VBE.ActiveVBProject.Name
 
−
   
 
−
    getDbAppTitle = strResult
 
−
End Function
 
  
−
=== existsTable ===
+
<syntaxhighlight lang="vb" line start="10" highlight="2">
−
Function existsTable(strTable As String) As Boolean
+
Public Sub setId(lngId As Long)
−
    Dim blnResult As Boolean
+
    SaveSetting getProjectName, "RunTime", "Id", CStr(lngId)
−
    Dim tdf As TableDef
+
End Sub
−
   
+
</syntaxhighlight>
−
    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="20" highlight="4">
−
Function existsQuery(strQuery As String) As Boolean
+
Public Function getId() As Long
−
    Dim blnResult As Boolean
+
    Dim lngResult As Long
−
    Dim qdf As QueryDef
+
   
−
   
+
    lngResult = CLng(GetSetting(getProjectName, "RunTime", "Id", 0))
−
    blnResult = False
+
   
−
    For Each qdf In CurrentDb.QueryDefs
+
    getId = lngResult
−
        If qdf.Name = strQuery Then
+
End Function
−
            blnResult = True
+
</syntaxhighlight>
−
            Exit For
 
−
        End If
 
−
    Next
 
−
   
 
−
    existsQuery = blnResult
 
−
End Function
 
  
 +
A few remarks on this code:
  
−
== modWsh ==
+
* 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''
−
Windows Scripting Host Object based methods
 
  
−
=== getUserName ===
+
* Lines 11 and 23: The setting's name ("''Id''") should be the same that the functions have ("set''Id''", "get''Id''").
−
Function getUserName() As String
 
−
    Dim strResult As String
 
−
   
 
−
    Dim wshNet As WshNetwork
 
−
   
 
−
    Set wshNet = New WshNetwork
 
−
    strResult = wshNet.UserName
 
−
   
 
−
    getUserName = strResult
 
−
End Function
 
−
 
−
=== getComputerName ===
 
−
Function getComputerName() As String
 
−
    Dim strResult As String
 
−
   
 
−
    Dim wshNet As WshNetwork
 
−
   
 
−
    Set wshNet = New WshNetwork
 
−
    strResult = wshNet.ComputerName
 
−
   
 
−
    getComputerName = strResult
 
−
End Function
 
  
−
=== displayActiveUsers ===
+
* Lines 10, 20ff: The variable type should always be converted explicitly, although VBA is able to cast implicit conversions (like strResult = datNow).
−
Sub displayActiveUsers()
+
 
−
    Dim strUsers As String
+
== Modules ==
−
    Dim cn As New ADODB.Connection
+
* [[Microsoft Access modAccess|modAccess]]
−
    Dim rs As New ADODB.Recordset
+
* [[Microsoft Access modSetting|modSetting]]
−
    Dim wshNet As WshNetwork
+
* [[Microsoft Access modUi|modUi]]
−
+
* [[Microsoft Access modFunction|modFunction]]
−
    strUsers = "Computer Name"
+
* [[Microsoft Access modSql|modSql]]
−
    Set cn = CurrentProject.Connection
+
* [[Microsoft Access modFso|modFso]]
−
    Set rs = cn.OpenSchema( _
+
* [[Microsoft Access modWsh|modWsh]]
−
        Schema:=adSchemaProviderSpecific, _
+
* [[Microsoft Access modExcel|modExcel]]
−
        SchemaId:="{947bb102-5d43-11d1-bdbf-00c04fb92675}" _
+
* [[Microsoft Access modString|modString]]
−
        )
 
−
    Debug.Print _
 
−
        rs.Fields(0).Name & " " & _
 
−
        rs.Fields(1).Name ' & " " & _
 
−
        rs.Fields(2).Name & " " & _
 
−
        rs.Fields(3).Name
 
−
    Set wshNet = New WshNetwork
 
−
    With rs
 
−
        Do While Not .EOF
 
−
            strUsers = strUsers & vbCrLf & Chr$(149) & "  " & Left(.Fields(0).Value, Len(wshNet.ComputerName))
 
−
            If Left(.Fields(0).Value, Len(wshNet.ComputerName)) = wshNet.ComputerName Then
 
−
                strUsers = strUsers & " (me)"
 
−
                Debug.Print _
 
−
                    wshNet.ComputerName & "*     " & _
 
−
                    wshNet.UserName ' & " " & _
 
−
                    .Fields(2).Value & " " & _
 
−
                    .Fields(3).Value
 
−
            Else
 
−
                Debug.Print _
 
−
                    Left(.Fields(0).Value, Len(wshNet.ComputerName)) & "      " & _
 
−
                    Trim(.Fields(1).Value) ' & " " & _
 
−
                    .Fields(2).Value & " " & _
 
−
                    .Fields(3).Value
 
−
            End If
 
−
            .MoveNext
 
−
        Loop
 
−
    End With
 
−
    MsgBox strUsers, vbOKOnly, "Current Users"
 
−
End Sub
 

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"