Looking for Faster way to update Table - boolean field

[cheekybuddha]

rsFilesFound is an odd name for a table or query!

Try:

Dim strMsg As String
Dim strSql As String

If Not Me.chkDir Then
    'SQL below is to count all records not found in active directory search
    strSql = "SELECT COUNT(*) " & _
             "FROM tblClips c LEFT JOIN rsFilesFound ff ON c.fldLocation = ff.fldFileAddress " & _
             "WHERE c.blnAlive = True AND ff.fldFileAddress IS NULL;"
    
    With CurrentDb.OpenRecordset(strSql)
      strMsg = "The SQL Query contains " & .Fields(0) & " references."
      .Close
    End With
    If MsgBox(strMsg, vbOKCancel, "TESTING") = vbCancel Then Exit Sub
    strSQL = "UPDATE tblClips c1 " & _
             "SET c1.blnAlive = False " & _
             "WHERE c1.ID IN (" & _
               "SELECT c2.ID " & _
               "FROM tblClips c2 LEFT JOIN rsFilesFound ff ON c2.fldLocation = ff.fldFileAddress " & _
               "WHERE c2.blnAlive = True AND ff.fldFileAddress IS NULL" & _
             ");"
    CurrentDb.Execute strSQL, dbFailOnError
    
End If

 

[CJ_London]

Sounds like you might want what is called an upsert query

Upserting in Microsoft Access

I need to write an SQL query for MS-Access 2000 so that a row is updated if it exists, but inserted if it does not. (I believe this is called an "upsert") i.e. If row exists... UPDATE Table1 SET...

stackoverflow.com

 

[Milhouse4118]

rsFilesFound is an odd name for a table or query!

Try:

Dim strMsg As String
Dim strSql As String

If Not Me.chkDir Then
    'SQL below is to count all records not found in active directory search
    strSql = "SELECT COUNT(*) " & _
             "FROM tblClips c LEFT JOIN rsFilesFound ff ON c.fldLocation = ff.fldFileAddress " & _
             "WHERE c.blnAlive = True AND ff.fldFileAddress IS NULL;"
   
    With CurrentDb.OpenRecordset(strSql)
      strMsg = "The SQL Query contains " & .Fields(0) & " references."
      .Close
    End With
    If MsgBox(strMsg, vbOKCancel, "TESTING") = vbCancel Then Exit Sub
    strSQL = "UPDATE tblClips c1 " & _
             "SET c1.blnAlive = False " & _
             "WHERE c1.ID IN (" & _
               "SELECT c2.ID " & _
               "FROM tblClips c2 LEFT JOIN rsFilesFound ff ON c2.fldLocation = ff.fldFileAddress " & _
               "WHERE c2.blnAlive = True AND ff.fldFileAddress IS NULL" & _
             ");"
    CurrentDb.Execute strSQL, dbFailOnError
   
End If

Wow. Your code worked perfectly on the first go. I clearly have much more to learn in the land of SQL. Thank you very much for the help.

 

[Milhouse4118]

rsFilesFound is an odd name for a table or query!

Try:

Dim strMsg As String
Dim strSql As String

If Not Me.chkDir Then
    'SQL below is to count all records not found in active directory search
    strSql = "SELECT COUNT(*) " & _
             "FROM tblClips c LEFT JOIN rsFilesFound ff ON c.fldLocation = ff.fldFileAddress " & _
             "WHERE c.blnAlive = True AND ff.fldFileAddress IS NULL;"
   
    With CurrentDb.OpenRecordset(strSql)
      strMsg = "The SQL Query contains " & .Fields(0) & " references."
      .Close
    End With
    If MsgBox(strMsg, vbOKCancel, "TESTING") = vbCancel Then Exit Sub
    strSQL = "UPDATE tblClips c1 " & _
             "SET c1.blnAlive = False " & _
             "WHERE c1.ID IN (" & _
               "SELECT c2.ID " & _
               "FROM tblClips c2 LEFT JOIN rsFilesFound ff ON c2.fldLocation = ff.fldFileAddress " & _
               "WHERE c2.blnAlive = True AND ff.fldFileAddress IS NULL" & _
             ");"
    CurrentDb.Execute strSQL, dbFailOnError
   
End If

So a question. Was it necessary to do the c1 alias in the creation of the 2nd sql statement, or was that a convenience to aid in the readability of the code? I'm just trying to understand if the SQL alias in the first statement construction could NOT be reused during the SQL statement second construction... Sorry, I may just be confused

 

[cheekybuddha]

Was it necessary to do the c1 alias in the creation of the 2nd sql statement, or was that a convenience to aid in the readability of the code?

Certainly it helps with the readability - to distinguish the version of table tblClips that you are updating from the version of the same table that is being read.

Is it necessary? I *think* so, but you may find it's not necessary if you test it without the aliases. Within the subquery in the WHERE clause, it makes writing the selected fields and join simpler when using aliases rather than having to re-type the whole table names.

If you want to re-use the FROM clause in both the first SELECT statement and also the subquery in the UPDATE statement, then you can use the same aliases.

eg:

Dim strMsg As String
Dim strSql As String
Dim strFrom As String

If Not Me.chkDir Then
    'SQL below is to count all records not found in active directory search
    strFrom = "FROM tblClips c LEFT JOIN rsFilesFound ff ON c.fldLocation = ff.fldFileAddress " & _
              "WHERE c.blnAlive = True AND ff.fldFileAddress IS NULL"
    strSql = "SELECT COUNT(*) " & strFrom & ";"
    With CurrentDb.OpenRecordset(strSql)
      strMsg = "The SQL Query contains " & .Fields(0) & " references."
      .Close
    End With
    If MsgBox(strMsg, vbOKCancel, "TESTING") = vbCancel Then Exit Sub
    strSQL = "UPDATE tblClips c1 " & _
             "SET c1.blnAlive = False " & _
             "WHERE c1.ID IN (" & _
               "SELECT c.ID " & strFrom & _
             ");"
    CurrentDb.Execute strSQL, dbFailOnError
 
End If

Note that you must not include the semi-colon in strFrom otherwise it will bork the UPDATE query by terminating it early.

Also the sub-SELECT in the WHERE clause of the UPDATE query must be adjusted to use the 'c' alias as opposed to 'c2'.



hth,

d