I’m trying to use SQL with VBA to update an Excel worksheet. My macro currently updates 5 worksheets in the same file (Review, Used, Road, Inspec, and Misc). The macro runs SQL to update each worksheet based on some criteria.

What I’m trying to do is put an “X” in the TMP column and a code in the TMP2 column (on the MatcheLines worksheet) for the records

‘closing the connection from the previous SELECT query. The SELECT query is the first one and runs fast. No problems with it and it’s performance.

            dbRS.Close
            dBcn.Close
            
        
'          ADD X TO TMP COLUMN and S to TMP2 column FOR REVIeW RECORDS
           Do Until ActiveSheet.Name = "MatchedLines"
                DoEvents
                Workbooks(ThisBook).Worksheets("MatchedLines").Activate
            Loop
            
            dBcn.Open dbconn
            
            vsql = ""
            vsql = "UPDATE [MatchedLines$] M " _
                & " SET M.[TMP] = 'X', [TMP2] = 'S' " _
                & "WHERE M.[RO_ID] IN " _
                & " (SELECT DISTINCT [RO_ID] FROM [Review$])"
            
            dbRS.Open vsql, dBcn

When the macro runs the 2nd query, it bogs down and freezes Excel. I usually use Task Manager to close Excel at this time.

The purpose is to “mark” these records in the MatchedLines worksheet, so subsequent queries do select the same records.

Is there something wrong with the SQL that is causing this slowdown / freezing in Excel?

Is there a better way to do this? Would a DELETE query be better and remove these records from the MatchedLines worksheet?

Thanks in advance for any suggestions or advice. FYI……..I rarely use SQL so I’m not sure what is causing my issue.

Thanks again…