How to Run SQL in a Table Field from Module (1 Viewer)

Lrn2Code

Registered User.
Local time
Today, 01:59
Joined
Dec 8, 2008
Messages
56

Hello,

I am stumped and need some guidance. Have a table (br_stat_edits) which has sql statements in a field. Those statements are related to edit checks and I am trying to figure out how they are running in the code module.

I've pasted the code below and am hoping someone can help me understand what's happening. The code needs to be tweaked but since I'm not sure which statement is actually running the SQL from the table field I can't create similar functionality.

FYI - the term haystack refers to certain edits needing to run more than 1 query to get a final calculated amount.

Thanks for any guidance you can provide.

Lrn2Code


Code:
stredits = "select * from br_stat_edits"
Set rstEdits = dbs.OpenRecordset(stredits)
If rstEdits.RecordCount = 0 Then
    Dim a As Variant
    a = MsgBox("Congratulations. Your data is clean -- and no edits have been identified.")
    Exit Sub
End If

rstEdits.MoveLast
rstEdits.MoveFirst
Dim y As Variant

Do Until rstEdits.EOF
    With rstEdits
    strsql = .Fields("sql")
    Dim strEditCheckCode As String
    strEditCheckCode = .Fields("editid")
    Dim strTheHayStack As String
    Dim lngRunHaystack As Long
    If .Fields("morethanone") = True Then
        lngRunHaystack = 1
        strTheHayStack = .Fields("haystack")
    End If
    If .Fields("morethanone") = False Then
        lngRunHaystack = 0
        strTheHayStack = ""
    End If
    Dim strTheOrg As String
    Dim lngTheOrgCount As Long
    lngTheOrgCount = 0
    Dim rstSQL As DAO.Recordset

    Set rstSQL = dbs.OpenRecordset(strsql) [COLOR="Red"]- Would this line trigger the sql to run?[/COLOR]
    If rstSQL.RecordCount > 0 Then
        rstSQL.MoveLast
        rstSQL.MoveFirst
        
        'insert the edits that are triggered by the sql into the d_stat_edits table
        'make sure you loop through the records that are returned so you can also id the org with the bad records
     Do Until rstSQL.EOF
        
'============================================ NEED EDIT CHECKER TO IGNORE WHEN SW3STAT-1 AMOUNT IS $0  4-20-11
              
        If strEditCheckCode = "SW3STAT-1" Then

               Dim stramt As String
               Dim rstamt As DAO.Recordset
               Dim amt As Variant
               Dim Orgcde As String

               stramt = "Select * from d_sw3"
               Set rstamt = CurrentDb.OpenRecordset(stramt)
               amt = rstamt.Fields("5TotalAssessment")
               Orgcde = rstamt.Fields("OrgID")


                    'SKIP OVER THE EDIT CHECK WHEN AMOUNT IS $0
                    If amt = 0 And Not IsNull(Orgcde) = True Then
                        If rstSQL.EOF + 1 = False Then
                        rstSQL.MoveNext 
                        End If
                    End If  
                End If  
'============================================ END OF EDIT CHECKER TO IGNORE WHEN SW3STAT-1 AMOUNT IS $0  4-20-11

        
                 If strTheOrg = "" Then lngTheOrgCount = 0
                If strTheOrg <> rstSQL.Fields("orgid") Then lngTheOrgCount = lngTheOrgCount + 1
    
                strTheOrg = rstSQL.Fields("orgid")
                              
                strBad = "insert into d_stat_edits select editid,Description,Significance,edit,action from br_stat_edits where editid='" & strEditCheckCode & "'"
                Set qdfBad = dbs.CreateQueryDef("", strBad)
                qdfBad.Execute
                
     'POPULATE ERROR REPORT WHEN ORG IS LISTED MORE THAN ONCE 5-11-09
                If strEditCheckCode = "SW1STAT-6" Then
                
                            Dim strSourceName As String
                            strSourceName = rstSQL.Fields("5TuitionSourceName")
                            Dim strComments As String
                            strComments = " SourceName = " & strSourceName
                            
                            'rstSQL.Fields("Comments") & strSourceName
                
                End If
                
                            If strEditCheckCode = "STAT030-1" Then
                            Dim strFunction As String
                            strFunction = rstSQL.Fields("FunctionCode")
                            strComments = " Function  = " & strFunction
                                                                           'rstSQL.Fields("Comments") & strSourceName
                            End If
                
       'END OF POPULATE ERROR REPORT WHEN ORG IS LISTED MORE THAN ONCE 5-11-09
                
             'POPULATE ERROR REPORT WHEN ORG IS LISTED MORE THAN ONCE 5-11-09
                If strEditCheckCode = "SW1STAT-6" Or strEditCheckCode = "STAT030-1" Then
                        strBad = "update d_stat_edits set comments = '" & strComments & "' where isnull(orgid)"
                        Set qdfBad = dbs.CreateQueryDef("", strBad)
                        qdfBad.Execute
                End If
             'END OF POPULATE ERROR REPORT WHEN ORG IS LISTED MORE THAN ONCE 5-11-09

               strBad = "update d_stat_edits set orgid='" & strTheOrg & "' where isnull(orgid)"
                
                Set qdfBad = dbs.CreateQueryDef("", strBad)
                qdfBad.Execute
                Set qdfBad = Nothing
                'query the haystack here -- in a recordset and loop through
                'fields get value --and update a memo field with the values
                Dim qdfHaystack As DAO.QueryDef
                Dim strHaystackSQL As String
                Dim rstHaystack As DAO.Recordset
                Dim strErrorDesc As String
                Dim strFieldName As String
                Dim strValue As String
                Dim strNeedle As String
                Dim Fld As DAO.Field
                                            
                If lngRunHaystack = 1 Then
                
                    If strEditCheckCode = "SW1STAT-6" Then
                     strHaystackSQL = "select * from " & strTheHayStack & " Where orgid = '" & strTheOrg & "' and SourceName = '" & strSourceName & "'"
                                        
                                        ElseIf strEditCheckCode = "STAT030-1" Then
                                        strHaystackSQL = "select * from " & strTheHayStack & " Where orgid = '" & strTheOrg & "' and Function = '" & strFunction & "'"
                                        
                                     Else
                                    strHaystackSQL = "select * from " & strTheHayStack & " Where orgid = '" & strTheOrg & "'"
                       End If
                                    
                    Set rstHaystack = dbs.OpenRecordset(strHaystackSQL)
                    If rstHaystack.RecordCount > 0 Then
                    Dim lngHay As Long
                    lngHay = 0
                    rstHaystack.MoveLast
                    rstHaystack.MoveFirst
                        Do Until rstHaystack.EOF
                                strErrorDesc = ""
                            For Each Fld In rstHaystack.Fields
                                    If UCase(Fld.Name) <> UCase("editID") And UCase(Fld.Name) <> UCase("ID") And UCase(Fld.Name) <> UCase("orgid") Then
                                    strFieldName = UCase(Fld.Name)
                                    If IsNull(rstHaystack(Fld.Name)) Then strValue = UCase("Null")
                                    If Not IsNull(rstHaystack(Fld.Name)) Then strValue = UCase(rstHaystack(Fld.Name))
                                    strErrorDesc = strErrorDesc & strFieldName & "=" & strValue & ","
                                End If
                            Next Fld
                            lngHay = lngHay + 1
                          
                            Dim xNeedle As Long
                            
                            If strEditCheckCode = "SW6STAT-4" Then
                                 xNeedle = 0
                            End If
                               
                               If strEditCheckCode = "SW1STAT-6" Or strEditCheckCode = "STAT030-1" Then
                               strNeedle = "update d_stat_edits set needle='" & strErrorDesc & "' where (isnull(needle) or needle= '') and orgid='" & strTheOrg & "' and comments like '*" & strComments & "*' and editid='" & strEditCheckCode & "'"
                                Set qdfHaystack = dbs.CreateQueryDef("", strNeedle)
                                qdfHaystack.Execute
                            
                            Else
                            
                                strNeedle = "update d_stat_edits set needle='" & strErrorDesc & "' where (isnull(needle) or needle= '') and orgid='" & strTheOrg & "' and editid='" & strEditCheckCode & "'"
                                Set qdfHaystack = dbs.CreateQueryDef("", strNeedle)
                                qdfHaystack.Execute
                            
                            End If
                                
                            rstHaystack.MoveNext
                            strErrorDesc = ""
                        Loop
                    End If
              End If
                rstSQL.MoveNext
        Loop
        
    End If
    
    .MoveNext
     y = rstEdits.RecordCount
     
    End With
Loop

'have the edits --now add the comments that were entered and saved in the util_d_stat_edits table before

stredits = "select * from util_d_stat_edits"
Set rstEdits = dbs.OpenRecordset(stredits)
'if there had been no comments entered -- then exit
If rstEdits.RecordCount = 0 Then
y = MsgBox("Edit run complete.")
    
        Exit Sub
End If
'otherwise update d_stat_edits

rstEdits.MoveLast
rstEdits.MoveFirst

Do Until rstEdits.EOF
    With rstEdits
        Dim strEditName As String
        strEditName = .Fields("editid")
        Dim strCommentUpdate As String
        Dim strTheNeedle As String
        If Not IsNull(.Fields("needle")) Then strTheNeedle = .Fields("needle")
        
        strTheOrg = .Fields("orgid")

        
        'add comments and ufapp field status where used
        
        If Not IsNull(.Fields("comments")) And Not IsNull(.Fields("needle")) Then
            strCommentUpdate = "update d_stat_edits set comments='" & .Fields("comments") & "' where orgid='" & strTheOrg & "' and needle='" & strTheNeedle & "' and editid='" & strEditName & "'"
            'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
            Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
            qdfBad.Execute
        End If
        
        If Not IsNull(.Fields("comments")) And IsNull(.Fields("needle")) Then
            strCommentUpdate = "update d_stat_edits set comments='" & .Fields("comments") & "' where orgid='" & strTheOrg & "' and editid='" & strEditName & "'"
            'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
            Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
            qdfBad.Execute
        End If
        
        If .Fields("ufapp") = True And Not IsNull(.Fields("needle")) Then
            strCommentUpdate = "update d_stat_edits set Ufapp=true where orgid='" & strTheOrg & "' and needle='" & strTheNeedle & "' and editid='" & strEditName & "'"
            'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
            Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
            qdfBad.Execute
        End If
        
        
        If .Fields("ufapp") = True And IsNull(.Fields("needle")) Then
            strCommentUpdate = "update d_stat_edits set Ufapp=true where orgid='" & strTheOrg & "' and editid='" & strEditName & "'"
            'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
            Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
            qdfBad.Execute
        End If
        
        If .Fields("unflagwithpassword") = True And Not IsNull(.Fields("needle")) Then
            strCommentUpdate = "update d_stat_edits set UnflagwithPassword=true where orgid='" & strTheOrg & "' and needle='" & strTheNeedle & "' and editid='" & strEditName & "'"
            'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
            Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
            qdfBad.Execute
        End If
        
        If .Fields("unflagwithpassword") = True And IsNull(.Fields("needle")) Then
            strCommentUpdate = "update d_stat_edits set UnflagwithPassword=true where orgid='" & strTheOrg & "' and editid='" & strEditName & "'"
            'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
            Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
            qdfBad.Execute
        End If
        
        Set qdfBad = Nothing
        
        
        .MoveNext
    End With

Loop
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:59
Joined
Feb 19, 2002
Messages
42,981
The .OpenRecordset statements run the SQL and so do the .Execute statements.

FYI, this an extremely inefficient process because all the SQL is dynamic for no reason that I can see. If you've ever used SQL from a compiled language you probably know the difference between static and dynamic SQL. Static SQL is "precompiled" and saved with an execution plan ready to go at runtime whereas Dynamic SQL must be "compiled" each and every time it runs. The biggest downside to this with Access is database bloat. Each time an execution plan is calculated, a lot of workspace is allocated and that space cannot be recovered or reused until the Access database is compacted.

Rather than storing the SQL as strings in a table, why not use Access' native method which is Querydefs? When you run a querydef the first time, Access calculates an execution plan and saves it. From that point on, the saved plan is used. Compacting a database marks all querydefs as "uncompiled" which forces the execution plan to recalculate the next time the query is run. This allows the plan to change periodically as rows are added/deleted in the referenced tables and also to take advantage of any new indexes that might speed up the query. This is also one of the reasons to not specify Compact on Close as the default.
 

Lrn2Code

Registered User.
Local time
Today, 01:59
Joined
Dec 8, 2008
Messages
56
Thank you very much Pat. Your post makes so much sense...I haven't been coding very long...but this set up really seems a bit funky to me.

I will try to change how this is coded to be more efficient, as always time is a factor.

Thanks again for your guidance.

Lrn2Code
 

spikepl

Eledittingent Beliped
Local time
Today, 06:59
Joined
Nov 3, 2010
Messages
6,144
This is also one of the reasons to not specify Compact on Close as the default.

Your entire post #2 is interesting and educational, but I do not quite follow the quoted bit. For the average user, that has no clue about compacting now and then, it would seem an advantage to set Compact on close, exactly so as to get queries recompiled to reflect the current amount of data at any time. So what am I missing here?
 

Users who are viewing this thread

Top Bottom