MS Access – VBA – Convert Form SQL into Queries Automatically

When I develop, I always, initially, build my SQL statements directly within my form’s Record Source. Now, it is a best practice to always create an independant query and base form’s off of the query rather than use an embedded SQL Statement. This is all fine a dandy when your db has 5-10 forms, but when you are working on databases with 10s or 100s of forms, this can become quite a teadeous task to convert all of the SQL statments into queries and then reassign the newly created queries to their respective forms. So, I thought about it for a couple minutes and quickly realized that this could actually all be automated quite easily. As such, I developed the following procedure.

'---------------------------------------------------------------------------------------
' Procedure : ConvertSQL2QRY
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   :
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
'
'
' Usage:
' ~~~~~~
'
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-30                 Initial Release
'---------------------------------------------------------------------------------------
Sub ConvertSQL2QRY()
    On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
'    Dim sSQL             As String
    Dim ctl              As Control
    Dim frm              As AccessObject
    Dim dbo              As AccessObject
    Dim dbp              As Object
    Dim sFrmName         As String
    Dim sFrmRS           As String
    Dim sQryRS As String
 
    Set db = CurrentDb
    Debug.Print "ConvertSQL2QRY Begin"
    Debug.Print "================================================================================"
 
    'Check Forms
    For Each frm In CurrentProject.AllForms
        sFrmName = frm.name
        DoCmd.OpenForm sFrmName, acDesign
        sFrmRS = Forms(sFrmName).RecordSource
        Debug.Print "Processing Form: " & sFrmName
        If Len(sFrmRS) > 0 And Left(sFrmRS, 4) <> "qry_" Then
            Debug.Print "   Converting Form: " & sFrmName
            If Left(sFrmRS, 7) = "SELECT " Then
                sQryRS = sFrmRS
            Else
                sQryRS = "SELECT [" & sFrmRS & "].* FROM [" & sFrmRS & "];"
            End If
            'Create a query based on the active RS and name it based on the form name for
            '   traceability
            CreateQry "qry_" & sFrmName, sQryRS
            'Change the form RS to use the newly created query
            Forms(sFrmName).RecordSource = "qry_" & sFrmName
        End If
        DoCmd.Close acForm, frm.name, acSaveYes
    Next frm
 
    Debug.Print "================================================================================"
    Debug.Print "ConvertSQL2QRY End"
 
Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Set ctl = Nothing
    Set frm = Nothing
    Set dbp = Nothing
    Set dbo = Nothing
    Exit Sub
 
Error_Handler:
    LogError Err.Number, Err.Description, _
             sModName & "/ConvertSQL2QRY", _
             , True
    Resume Error_Handler_Exit
End Sub

What does this procedure do? Well, it will go through all the forms in your database and create a query using the current record source and then reasign the newly created query as the form’s record source. I run this query just before deploying a databaase. It is also intelligent enough to only process those forms that don’t already have a query created, so it can be rerun whenever needed should I add new forms, etc…

Please note this procedure is dependent on my CreateQry procedure, so be sure to get it as well!

View ratings
Rate this article

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>