ADO Update query not running

Hello experts - I've been working on a script for the last week or so, and I've run into a particularly troubling issue. I have an update statement that I am trying to execute on records in an MS Access Database from VB.NET. This is the 7th of several SQL statements I'm executing. It's also the ONLY one that isn't running. I get no error messages, but when I insert a break point just after the execute command, I check the table in Access, and the values have not changed.

However, if I copy the command text from my locals window and paste it directly into the MS Access SQL query window and run, the values in the table ARE updated, so I know it's not a logic problem with the SQL text. My connection is active, and all subsequent queries run with no problems.

Can someone help me figure out why this update statement is so unhappy?

Public Shared Sub Mac_DataProcessing(ByRef cn As ADODB.Connection)
        Dim objCommand As ADODB.Command
        objCommand = New ADODB.Command

        objCommand.CommandTimeout = 9999999
        objCommand.ActiveConnection = cn
        'Dim teststring As String
        '1014-Delete Todays IFACE
        cn.Execute("DELETE [Tbl-Todays IFACE].DEPARTMENT FROM [Tbl-Todays IFACE];")
        
        '1000-Update RBP Sim Qty
        cn.Execute("UPDATE Chargedtl SET Chargedtl.[RBP Sim Qty] = IIf(Left([Chargedtl]![SIMCD],3)=" & """RBP""" & ",[Chargedtl]![Qty]*-1,0) WHERE (((Chargedtl.[RBP Sim Qty]) Is Null));")
        '1001-Update Facility ID
        cn.Execute("UPDATE Chargedtl SET Chargedtl.Facility = Left([Chargedtl]![Acct#],1) WHERE (((Chargedtl.Facility) Is Null));")
        '1002-Update Qty to Reconcile
        cn.Execute("UPDATE Chargedtl SET Chargedtl.[Qty to Reconcile] = [Chargedtl]![Qty]+[Chargedtl]![RBP Sim Qty] WHERE (((Chargedtl.[Qty to Reconcile]) Is Null));")
        '1003-Update Recon Depts
        cn.Execute("UPDATE [Tbl-Recon Depts] RIGHT JOIN Chargedtl ON [Tbl-Recon Depts].RevCntr = Chargedtl.RevCntr SET Chargedtl.[RevCntr-Num] = [Tbl-Recon Depts]![RevCntr-Num]," & _
                   "Chargedtl.System = [Tbl-Recon Depts]![System], Chargedtl.[Recon Group] = [Tbl-Recon Depts]![Recon Group] WHERE (((Chargedtl.[RevCntr-Num]) Is Null));")
        '1003c-Update Recon Depts-SLab
        cn.Execute("UPDATE Chargedtl SET Chargedtl.[Recon Group] = 'Lab', Chargedtl.[Recon Group + Facility] =" & """P-Lab""" & ", Chargedtl.Facility = " & """P""" & ", Chargedtl.[Facility Name]" & _
                   "=" & """Lovelace Medical Center""" & " WHERE (((Chargedtl.Facility)=" & """s""" & ") AND ((Chargedtl.RevCntr)=5012 Or (Chargedtl.RevCntr)=5075 Or (Chargedtl.RevCntr)=5094 " & _
                   "Or (Chargedtl.RevCntr)=5111) AND ((Chargedtl.ChgDt)>#1/31/2011#));")
        '1004-Update Recon Group + Facility
        cn.Execute("UPDATE Chargedtl SET Chargedtl.[Recon Group + Facility] = [Chargedtl]![Facility] &" & """-""" & "& [Chargedtl]![Recon Group] WHERE (((Chargedtl.[Recon Group + Facility]) Is Null));")
        '___________________________________________________________________________________________________________________________________
        '1004a-Update Recon Group + Facility
        'This is the query that is not running. I've put it in a command text so I can cut/paste into access from the locals window
        'However, I used to have it set up in a simple cn.execute("SQL here") statement before using the objCommand.CommandText format
        'Neither seems to work. I've also tried adding System.threading.sleep commands to no avail
        objCommand.CommandText = "UPDATE Chargedtl SET Chargedtl.[Recon Group + Facility] = IIf([Chargedtl]![Ini]=" & """***""" & ",[Chargedtl]![Facility] & " & """-""" & " & [Chargedtl]![Recon Group], " & _
                   " [Chargedtl]![Facility] & " & """-""" & " & [Chargedtl]![Recon Group] & " & """-HIM""" & "), Chargedtl.[Recon Group] = IIf([Chargedtl]![Ini]=" & """***""" & ",[Chargedtl]![Recon Group],[Chargedtl]![Recon Group] & " & """-HIM""" & ")" & _
                    " WHERE (((Chargedtl.[Recon Group + Facility]) Like " & """*Med Surg*""" & " Or " & _
                   " (Chargedtl.[Recon Group + Facility]) Like " & """*ICU*""" & " Or (Chargedtl.[Recon Group + Facility]) Like " & """*FDU*""" & " Or (Chargedtl.[Recon Group + Facility]) Like " & """*NICU*""" & " Or " & _
                   " (Chargedtl.[Recon Group + Facility]) Like " & """*OBU*""" & ") AND ((Chargedtl.System)=" & """HOM""" & "));"
        objCommand.Execute()
        
        '___________________________________________________________________________________________________________________________________

        '1005-Update Facility Name
        cn.Execute("UPDATE Chargedtl LEFT JOIN [Tbl-Facility ID and Name] ON Chargedtl.Facility = [Tbl-Facility ID and Name].[Facility ID] SET Chargedtl.[Facility Name] =" & _
                   " [Tbl-Facility ID and Name]![Facility Name] WHERE (((Chargedtl.[Facility Name]) Is Null));")
        cn.Properties.Refresh()
        '1006-Delete Todays Charges
        cn.Execute("DELETE [Tbl-Todays Charges].[Acct#] FROM [Tbl-Todays Charges];")
        cn.Execute("UPDATE Chargedtl SET Chargedtl.[Recon Group + Facility] = IIf([Chargedtl]![Ini]=" & """***""" & ",[Chargedtl]![Facility]& " & """-""" & " & [Chargedtl]![Recon Group], " & _
                  " [Chargedtl]![Facility] & " & """-""" & " & [Chargedtl]![Recon Group] & " & """-HIM""" & "), Chargedtl.[Recon Group] = IIf([Chargedtl]![Ini]=" & """***""" & ",[Chargedtl]![Recon Group],[Chargedtl]![Recon Group] & " & """-HIM""" & ")" & _
                   " WHERE (((Chargedtl.[Recon Group + Facility]) Like " & """*Med Surg*""" & " Or " & _
                  " (Chargedtl.[Recon Group + Facility]) Like " & """*ICU*""" & " Or (Chargedtl.[Recon Group + Facility]) Like " & """*FDU*""" & " Or (Chargedtl.[Recon Group + Facility]) Like " & """*NICU*""" & " Or " & _
                  " (Chargedtl.[Recon Group + Facility]) Like " & """*OBU*""" & ") AND ((Chargedtl.System)=" & """HOM""" & "));")

        '1007-Append Todays Charges
        Console.WriteLine("Appending today's processed charges...")
        cn.Execute("INSERT INTO [Tbl-Todays Charges] ( [Acct#], [Pat Name], Amt, Qty, SIMCD, [Item Desc], RevCntr, SrvDt, ChgDt, [Chg Loc], [Lt Chg], Ini, OrdNo," & _
                   "ChgTime, [RBP Sim Qty], [Qty to Reconcile], Facility, [RevCntr-Num], System, [Recon Group], [Recon Group + Facility], [Facility Name] )" & _
                   "SELECT Chargedtl.[Acct#], Chargedtl.[Pat Name], Chargedtl.Amt, Chargedtl.Qty, Chargedtl.SIMCD, Chargedtl.[Item Desc], Chargedtl.RevCntr," & _
                   "Chargedtl.SrvDt, Chargedtl.ChgDt, Chargedtl.[Chg Loc], Chargedtl.[Lt Chg], Chargedtl.Ini, Chargedtl.OrdNo, Chargedtl.ChgTime, Chargedtl.[RBP Sim Qty]," & _
                   "Chargedtl.[Qty to Reconcile], Chargedtl.Facility, Chargedtl.[RevCntr-Num], Chargedtl.System, Chargedtl.[Recon Group], Chargedtl.[Recon Group + Facility]," & _
                   "Chargedtl.[Facility Name] FROM Chargedtl")

        '1007a-Update Bariatric BVM
        cn.Execute("UPDATE [Tbl-Todays Charges] SET [Tbl-Todays Charges].RevCntr = 52011, [Tbl-Todays Charges].[Recon Group] =" & """Bariatric-Clinic""" & "," & _
                   " [Tbl-Todays Charges].[Recon Group + Facility] = " & """M-Bariatric-Clinic""" & " WHERE ((([Tbl-Todays Charges].RevCntr)=5201) AND " & _
                   "(([Tbl-Todays Charges].[Chg Loc]) Not Like " & """BSM*""" & "));")
        '1007b-Update OP Speech Therapy
        cn.Execute("UPDATE [Tbl-Todays Charges] SET [Tbl-Todays Charges].RevCntr = 51961, [Tbl-Todays Charges].[Recon Group] = " & """OP Therapies""" & ", " & _
                   "[Tbl-Todays Charges].[Recon Group + Facility] = " & """M-OP Therapies""" & ", [Tbl-Todays Charges].System = " & """STAR""" & " WHERE " & _
                   "((([Tbl-Todays Charges].RevCntr)=5196) AND (([Tbl-Todays Charges].[Chg Loc]) Like " & """TOM*""" & "));")
        '1007c-Update OP PICC
        cn.Execute("UPDATE [Tbl-Todays Charges] SET [Tbl-Todays Charges].RevCntr = 52221, [Tbl-Todays Charges].[Recon Group] = " & """OP PICC""" & ", " & _
                   "[Tbl-Todays Charges].[Recon Group + Facility] = [Tbl-Todays Charges]![Facility] &" & """-""" & "&" & """OP PICC""" & ", " & _
                   "[Tbl-Todays Charges].System = " & """STAR""" & " WHERE ((([Tbl-Todays Charges].RevCntr)=5222) AND (([Tbl-Todays Charges].[Chg Loc])=" & """PIP""" & "));")
        '1007d-Update Rehab Lab to LMC
        cn.Execute("UPDATE [Tbl-Todays Charges] SET [Tbl-Todays Charges].[Facility Name] = " & """Lovelace Medical Center""" & " WHERE ((([Tbl-Todays Charges].[Facility Name])=" & """Lovelace Rehab Hospital""" & ") AND " & _
                   "(([Tbl-Todays Charges].[Recon Group + Facility])=" & """P-Lab""" & "));")
        '1009A
        cn.Execute("UPDATE [Tbl-Todays Charges] LEFT JOIN [Tbl-Recon Depts] ON [Tbl-Todays Charges].RevCntr = [Tbl-Recon Depts].RevCntr SET [Tbl-Todays Charges].[Recon Group] =" & _
                   "[Tbl-Recon Depts]![Recon Group] &" & """-""" & " & " & """HIM""" & ", [Tbl-Todays Charges].[Recon Group + Facility] = [Tbl-Todays Charges]![Facility] & " & """-""" & " & [Tbl-Recon Depts]![Recon Group] & " & """-""" & " & " & """HIM""" & " WHERE" & _
                  " ((([Tbl-Todays Charges].[Recon Group]) Like " & """*Med Surg*""" & " Or ([Tbl-Todays Charges].[Recon Group]) Like " & """*ICU*""" & " Or ([Tbl-Todays Charges].[Recon Group])" & _
                   "Like " & """*NICU*""" & " Or ([Tbl-Todays Charges].[Recon Group]) Like " & """*FDU*""" & " Or ([Tbl-Todays Charges].[Recon Group]) Like " & """*OBU*""" & " Or " & _
                   "([Tbl-Todays Charges].[Recon Group]) Like " & """*ATU*""" & ") AND (([Tbl-Todays Charges].Ini)<>" & """***""" & "));")
        'qry_AddReconDept
        cn.Execute("UPDATE [Tbl-IFACE Depts] RIGHT JOIN [Tbl-Todays Charges] ON [Tbl-IFACE Depts].IFACE = [Tbl-Todays Charges].SIMCD SET " & _
                   "[Tbl-Todays Charges].[Recon Group] = [Tbl-IFACE Depts]![Recon Group], [Tbl-Todays Charges].[Recon Group + Facility] = " & _
                   "[Tbl-Todays Charges]![Facility]&" & """ - """ & "&[Tbl-IFACE Depts]![Recon Group] WHERE ((([Tbl-Todays Charges].[Recon Group]) Is Null));")


        '1008-Delete Todays Recon Groups
        cn.Execute("DELETE [Tbl-Todays Recon Groups].[Recon Group + Facility] FROM [Tbl-Todays Recon Groups];")

        '1009-Append Todays Recon Groups
        cn.Execute("INSERT INTO [Tbl-Todays Recon Groups] ( [Recon Group + Facility], Facility, [Recon Group], [Chg Dt] ) SELECT [Tbl-Todays Charges].[Recon Group + Facility], " & _
                   " [Tbl-Todays Charges].Facility, [Tbl-Todays Charges].[Recon Group], [Tbl-Todays Charges].ChgDt FROM [Tbl-Todays Charges] GROUP BY " & _
                   " [Tbl-Todays Charges].[Recon Group + Facility], [Tbl-Todays Charges].Facility, [Tbl-Todays Charges].[Recon Group], [Tbl-Todays Charges].ChgDt;")

        
       

closecn:


    End Sub

Select allOpen in new window