Solved

ADO Update query not running

Posted on 2013-05-29
9
629 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
[X]
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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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 …

691 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