Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ADO Update query not running

Posted on 2013-05-29
9
622 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
ID: 39205012
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
ID: 39205019
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
ID: 39205030
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 6

Author Comment

by:lorenda
ID: 39205051
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
 
LVL 6

Author Closing Comment

by:lorenda
ID: 39205052
Worked like a charm!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39205056
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
ID: 39205057
--->>> * 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39205059
sorry did not refresh...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39205061
Glad that worked out...  :-)
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

840 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