Code Snippets

In all the times I have spent participating in forum discussions about all sorts of users' questions, I have learnt a great deal from all the participants.

I have decided to post some of the sample codes that I found users were asking for almost all the time to help me keep them in one place and to make it easier for me to share them again and again.

Hope you find them useful.



Leigh's Execute Action Queries

This is a companion function to Leigh's Generic Recordset that takes care of executing action queries with parameters. Thanks to fellow MVP, Leigh Purvis (LPurvis) for allowing me to post it here. Enjoy!

Function fExecuteQuery(strQuery As String, _
	Optional intOptions As DAO.RecordsetOptionEnum = dbFailOnError, _
	Optional blnReturnAuto As Boolean = False, _
	Optional pdb As DAO.Database) As Long

Dim db As Database
Dim prm As DAO.Parameter
Dim qdf As QueryDef
Dim rst As DAO.Recordset

If Not pdb Is Nothing Then
   Set db = pdb
Else
   Set db = CurrentDb
End If

Select Case Left(strQuery, 7)
   Case "INSERT ", "UPDATE ", "DELETE "
      Set qdf = db.CreateQueryDef("", strQuery)
   Case Else
      Set qdf = db.QueryDefs(strQuery)
End Select

For Each prm In qdf.Parameters
   prm.Value = Eval(prm.Name)
Next

qdf.Execute intOptions

If blnReturnAuto Then
   Set rst = db.OpenRecordset("SELECT @@Identity")
   fExecuteQuery = rst(0)
   rst.Close
End If

Set prm = Nothing
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

End Function