Solved

ADO Update query not running

Posted on 2013-05-29
9
483 Views
Last Modified: 2013-05-29
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

Open in new window

0
Comment
Question by:lorenda
  • 4
  • 3
  • 2
9 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Are you certain that you have recprds in Chargedtl meeting your criteria?

Try this varation... for an ADO recordset, I believe you need to use % instead of * for your wildcards.:

        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()

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Or this:

        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()

Open in new window

0
 
LVL 6

Author Comment

by:lorenda
Comment Utility
I do have records meeting my criteria, because when I copy/paste the commandtext from my locals window in VB.NET to Access, it updates several records.

* is the wildcard character for MS Access, and I've had no trouble with my other queries containing * updating records.
0
 
LVL 6

Author Comment

by:lorenda
Comment Utility
Very strange. I tried your second option and it worked perfectly. Guess I'm going back to change all my wildcards to %....

Thanks for the help.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Author Closing Comment

by:lorenda
Comment Utility
Worked like a charm!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
the conditions that you have (IIF clause and the Where conditions) could be the ones causing the failure of the update query .

i will, start  with a simple update statement and add the conditions one at a time to figure out which part is causing the update to fail.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
--->>> * is the wildcard character for MS Access,
Understood.

Not sure about VB.Net, but -

* works fine for me in the query builder in Access and in DAO recordsets... but I HAVE had issues with * being treated as a literal in ADO code in Access (meaning that in a case like yours you would get no errors - but no results either since the query is looking for a * instead of 'any character').  Those issues have been resolved by using % in my ADO code.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
sorry did not refresh...
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Glad that worked out...  :-)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now