Looking for Faster way to update Table - boolean field

[cheekybuddha]

I don't think the "upsert" works with SQL server but someone will correct me if I am wrong.

T-SQL MERGE statement

 

[Pat Hartman]

Thanks, the Access SQL "upsert" version using a right join doesn't work so you need to use a pass-through query using the MERGE statement. I didn't mean to imply that SQL couldn't perform the same function, only that the Access SQL method didn't work.

 

[Isaac]

Perhaps you could store the files incrementally in a table as they are generated/come into existence, so that all "unprocessed" files would be as simple as a query that you could loop through to perform the updates. This is very similar to Doc's suggestion, I'm just emphasizing that maybe you can control the process as the files are generated rather than the heavyweight process of looping through them all from scratch.

 

[Pat Hartman]

Perhaps you could store the files incrementally in a table as they are generated/come into existence,

That means you need a timer event running 24/7 to identify when new files have been added.

 

[Milhouse4118]

Thanks for all the help. I'm close to completing the update to my db, but I've a snag when trying to work with sql. Per the recommendations found in this thread, I've done some testing and have gotten to the point where a LEFT JOIN query accomplishes the task of showing all records in the main table that were not found during the recursive directory search. When this query is run in the Access query builder interface, I realized I can take it further and change the value of blnAlive directly in the query window and the results flow back to the primary data table. Now I'm struggling on how to do that entirely in vba code.

See my snipet below and let me know if it is possible execute an UPDATE have the entirety of the records move from TRUE to FALSE.

Dim strMsg As String
Dim strSql As String
Dim rsFilesDead As DAO.Recordset




'Only do the section below if the user did NOT choose a directory to search.
If Not Me.chkDir Then
'SQL below is to identify all records not found in active directory search
    strSql = "SELECT tblClips.ID, tblClips.blnAlive " & _
                "FROM tblClips LEFT JOIN rsFilesFound ON tblClips.fldLocation = rsFilesFound.fldFileAddress " & _
                    "WHERE (((tblClips.blnAlive)=True) AND ((rsFilesFound.fldFileAddress) Is Null));"
    
    Set rsFilesDead = CurrentDb.OpenRecordset(strSql)
    If rsFilesDead.RecordCount > 0 Then
        rsFilesDead.MoveLast
        rsFilesDead.MoveFirst
    End If
    
    strMsg = MsgBox("The SQL Query contains " & rsFilesDead.RecordCount & " references.", vbOKCancel, "TESTING")
    If strMsg = vbCancel Then Exit Sub
    
    'Insert UPDATE Function on rsFilesDead to set the blnAlive field to FALSE.
    
End If

 

[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