Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    sgtdetritus's Avatar
    sgtdetritus is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    19

    Odd behavior of DB with an Append query

    I'm getting a strange error in my access database.


    The Error:
    The table '[insert table name here]' already exists. You created or renamed a table and then tried to save it. before Microsoft access could save the table, another user created or renamed one using the same name.
    This is happening when an RPA team runs an Access Macro that is just a list of queries to run in sequence.
    The error comes up during an append query that takes records from one table and adds them to another table that is the core of a work environment.
    This is only happening to the RPA team. When I run the same Macro from my workstation, it runs flawlessly. When 2 other people who are authorized run the Macro, it runs flawlessly. I can't duplicate it. I also cannot find any reference to this error in this circumstance in the time I have spent on google or here. I'm stumped. I could just be typing in keyword searches wrong, but I don't quite know where to go next with this.

    Any Ideas why this might happen? Also, I can copy the SQL if need be, but the tables are very large (due to the way I have to generate a report in SAP). My normal work environment Table is about 45k records and the table it pulls the SAP report from is usually around 22k records, most of which are discarded. This makes the SQL very hard to read. I

    Thanks in advance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please provide more detail and a description of the application generally --start at 30,000 ft and work down to help with context.
    It is often better for you and readers if you avoid jargon and acronyms. RPA?
    Is this a split database with multiple users? Does each use have a copy of the FrontEnd on their individual PC?

  3. #3
    sgtdetritus's Avatar
    sgtdetritus is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    19
    From 30,000 feet then. SAP is very good at managing very large quantities of data, but it is hard to surface information on what needs attention right now. So I take a very large report every day, bring it into access, and strip out the dross aso people can look and find out what needs to be worked on. It is a split database on a large shared drive. IT constraints prevent us from giving it any sort of dedicated machine or server. Each user gets an accdr front end to access the data on their own PC's. I have about 6 users. They are able to take notes and run reports from the front end.
    The data coming from SAP takes a very long time to export and when it's ready, we load it into a table that gets scrubbed every day. I have another table that is where everything gets stored long term. A series of append queries take records from the table that is cleared and refreshed every day and adds them to the long term table, relying on the primary key in the long term table to prevent duplication. More queries examines the new data and fills in any blank fields in the long term table. This has worked for about a year and a half with only fairly small changes. I have all of these queries in a Macro so they all consistently run in sequence. I left it so that we see every pop-up box, as the numbers can tell us whether or not something went screwy with the extraction from SAP.
    Since it takes so long to update every day, the company has hired a team to automate the update process. RPA stands for robotic process automation. Think writing a Macro that works across multiple applications within Windows. In this case, it has to manage SAP, MS Excel, and MS Access in sequence. The RPA team has automated the SAP extract so it can run overnight, but has had a lot of problems getting the data into access and having the Macro run without issues. This is where the error comes up.
    As the Macro gets to the first Append query, this is what pops up:Click image for larger version. 

Name:	MS Access db error.JPG 
Views:	21 
Size:	24.8 KB 
ID:	35456
    "The table 'New Merge ZCOM' already exists. You created or renamed a table, and then tried to save it. Before Microsoft Access could save the table, another user created or renamed one using the same name."
    IN my db, the is no table by that name. That name is the name of the Append query that is trying to run.
    I have never seen this error before. It never comes up when I run the update process, or when 2 other people in my office run it as my backups. I have not changed the query in a year. It is only happening to the RPA team.
    The RPA team is located in India, while the bulk of the company is in the US and Canada. The RPA is using a Virtual Machine to do their tasks.

    If you want the SQL for the query itself, I can provide that too. LIke I said in my original post, it's really ugly and hard to read though. The short explanation is that it is a pretty standard append query created in the normal GUI, not written as raw SQL.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It is only happening to the RPA team
    .
    That may be a clue. Does the RPA team have some different settings/macros/... than you and your team?

    Can you ask the RPA team the technical details of the ZCOM table/query? Seems Access is seeing it whether you/your group does or not.
    You could post the SQL --someone my decipher it and see an issue.

  5. #5
    sgtdetritus's Avatar
    sgtdetritus is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    19
    The RPA team frankly knows nothing about Access at all. I have had to hold their hand through this entire process. They do use a program called Blue Prism for task automation.
    The query/settings are exactly the same ones we use in our location. There was one modification to the overall macro on an unrelated step where I had to modify a filepath so that access can export a copy of the long term table to a backup area of the shared drive.
    To be clear, this error is intermittent. It's not every time, but it is often enough to question whether we can complete this automation.
    I wrote the queries in question myself.

    Here is the SQL:

    INSERT INTO [New ZSPWAR Prime] ( Payer, AI, Bill_doc, Sqwak, Site, Plnt, Billing_Dt, ProfitCntr, Customer, Equipment, Issued_Material, Material_Description, SLoc, Serial_No, ItCC, Mtrl_Grp, Manuf_No, Manuf_Name, RTN, Warranty, PPC, SVO_No, Ref_No, Notification_Subject, AC_C, Corrective_Action, SB_Data, AC_R, Corr_Action_R, SaleDebCre, Sales_Ord, SO_Date, SU, Returned_Material, Returned_SNR, [Reason for Rejection], SLCo, Canel_Doc, Can_Doc_Dt, Tail, ItCa, Sold_to, Sold_to_Name, Customer_PO_No, [User], Comp_Date, OB_Del_NO, OB_Del_DT, Ret_Delv, Rt_SO_It, Ret_Date, Ret_EQUI, Ret_Note, NoteCR_DT, NoteCM_DT, Ret_Note_DESC, WACD, Wty_Prog_Code_Desc, PO_NO, PO_DT, PO_PI, VR_Amount, INV_PT_DT, ZZ07, Freight_Amt, [ME23N PO_Owner], VDCD, Ven_Den_Code_Desc, DISP, [Disp code Descr], PRI, HVNE, [Approve By], [MM Vendor Number], [Sourcing Vendor Number], [Sourcing Vendor Name], [Vendor Contract], [FFF Class], [Ref Contract], [Company Code], [BUSAC TYPE], [Warr Typ], [Aircraft Material], [Aircraft Serial Number], [Aircraft Program], [GL Date], [Obj Type], [ID Number], [MD04 Purchasing Group], [MD04 PurG Name], [RO Number], [RO Creation Date], [RO Contact Name], [RO Vendor Code], [RO Vendor Name], [ODC Amount], [Issue_New/Rotable], Issue_Core, [Recovery_New/Rotable], Recovery_Core, Labor_Hours, Rate, Labor_Cost, Mtrl_Qty_Billed, Mtrl_ea_Cost, Mtrl_ttl_Cost, Total_Cost, MAUC_STD, Core_cost, Cat_Price, Discount, [% Discount], [ODC Freight], [Repair Cost], [Material Burden], [% Material Burden], [Rotable Depreciation], [% Core Dep], [Net Spend], Margin, Item_Cost, [Total Material Burden], [Total Rotables Dep], SVO_Direct_Labor, SVO_Labor_OH )


    SELECT [New ZSPWAR Merge].Payer, [New ZSPWAR Merge].AI, [New ZSPWAR Merge].Bill_doc, [New ZSPWAR Merge].Sqwak, [New ZSPWAR Merge].Site, [New ZSPWAR Merge].Plnt, [New ZSPWAR Merge].Billing_Dt, [New ZSPWAR Merge].ProfitCntr, [New ZSPWAR Merge].Customer, [New ZSPWAR Merge].Equipment, [New ZSPWAR Merge].Issued_Material, [New ZSPWAR Merge].Material_Description, [New ZSPWAR Merge].SLoc, [New ZSPWAR Merge].Serial_No, [New ZSPWAR Merge].ItCC, [New ZSPWAR Merge].Mtrl_Grp, [New ZSPWAR Merge].Manuf_No, [New ZSPWAR Merge].Manuf_Name, [New ZSPWAR Merge].RTN, [New ZSPWAR Merge].Warranty, [New ZSPWAR Merge].PPC, [New ZSPWAR Merge].SVO_No, [New ZSPWAR Merge].Ref_No, [New ZSPWAR Merge].Notification_Subject, [New ZSPWAR Merge].AC_C, [New ZSPWAR Merge].Corrective_Action, [New ZSPWAR Merge].SB_Data, [New ZSPWAR Merge].AC_R, [New ZSPWAR Merge].Corr_Action_R, [New ZSPWAR Merge].SaleDebCre, [New ZSPWAR Merge].Sales_Ord, [New ZSPWAR Merge].SO_Date, [New ZSPWAR Merge].SU, [New ZSPWAR Merge].Returned_Material, [New ZSPWAR Merge].Returned_SNR, [New ZSPWAR Merge].[Reason for Rejection], [New ZSPWAR Merge].SLCo, [New ZSPWAR Merge].Canel_Doc, [New ZSPWAR Merge].Can_Doc_Dt, [New ZSPWAR Merge].Tail, [New ZSPWAR Merge].ItCa, [New ZSPWAR Merge].Sold_to, [New ZSPWAR Merge].Sold_to_Name, [New ZSPWAR Merge].Customer_PO_No, [New ZSPWAR Merge].User, [New ZSPWAR Merge].Comp_Date, [New ZSPWAR Merge].OB_Del_NO, [New ZSPWAR Merge].OB_Del_DT, [New ZSPWAR Merge].Ret_Delv, [New ZSPWAR Merge].Rt_SO_It, [New ZSPWAR Merge].Ret_Date, [New ZSPWAR Merge].Ret_EQUI, [New ZSPWAR Merge].Ret_Note, [New ZSPWAR Merge].NoteCR_DT, [New ZSPWAR Merge].NoteCM_DT, [New ZSPWAR Merge].Ret_Note_DESC, [New ZSPWAR Merge].WACD, [New ZSPWAR Merge].Wty_Prog_Code_Desc, [New ZSPWAR Merge].PO_NO, [New ZSPWAR Merge].PO_DT, [New ZSPWAR Merge].PO_PI, [New ZSPWAR Merge].VR_Amount, [New ZSPWAR Merge].INV_PT_DT, [New ZSPWAR Merge].ZZ07, [New ZSPWAR Merge].Freight_Amt, [New ZSPWAR Merge].[ME23N PO_Owner], [New ZSPWAR Merge].VDCD, [New ZSPWAR Merge].Ven_Den_Code_Desc, [New ZSPWAR Merge].DISP, [New ZSPWAR Merge].[Disp code Descr], [New ZSPWAR Merge].PRI, [New ZSPWAR Merge].HVNE, [New ZSPWAR Merge].[Approve By], [New ZSPWAR Merge].[MM Vendor Number], [New ZSPWAR Merge].[Sourcing Vendor Number], [New ZSPWAR Merge].[Sourcing Vendor Name], [New ZSPWAR Merge].[Vendor Contract], [New ZSPWAR Merge].[FFF Class], [New ZSPWAR Merge].[Ref Contract], [New ZSPWAR Merge].[Company Code], [New ZSPWAR Merge].[BUSAC TYPE], [New ZSPWAR Merge].[Warr Typ], [New ZSPWAR Merge].[Aircraft Material], [New ZSPWAR Merge].[Aircraft Serial Number], [New ZSPWAR Merge].[Aircraft Program], [New ZSPWAR Merge].[GL Date], [New ZSPWAR Merge].[Obj Type], [New ZSPWAR Merge].[ID Number], [New ZSPWAR Merge].[MD04 Purchasing Group], [New ZSPWAR Merge].[MD04 PurG Name], [New ZSPWAR Merge].[RO Number], [New ZSPWAR Merge].[RO Creation Date], [New ZSPWAR Merge].[RO Contact Name], [New ZSPWAR Merge].[RO Vendor Code], [New ZSPWAR Merge].[RO Vendor Name], [New ZSPWAR Merge].[ODC Amount], [New ZSPWAR Merge].[Issue_New/Rotable], [New ZSPWAR Merge].Issue_Core, [New ZSPWAR Merge].[Recovery_New/Rotable], [New ZSPWAR Merge].Recovery_Core, [New ZSPWAR Merge].Labor_Hours, [New ZSPWAR Merge].Rate, [New ZSPWAR Merge].Labor_Cost, [New ZSPWAR Merge].Mtrl_Qty_Billed, [New ZSPWAR Merge].Mtrl_ea_Cost, [New ZSPWAR Merge].Mtrl_ttl_Cost, [New ZSPWAR Merge].Total_Cost, [New ZSPWAR Merge].MAUC_STD, [New ZSPWAR Merge].Core_cost, [New ZSPWAR Merge].Cat_Price, [New ZSPWAR Merge].Discount, [New ZSPWAR Merge].[% Discount], [New ZSPWAR Merge].[ODC Freight], [New ZSPWAR Merge].[Repair Cost], [New ZSPWAR Merge].[Material Burden], [New ZSPWAR Merge].[% Material Burden], [New ZSPWAR Merge].[Rotable Depreciation], [New ZSPWAR Merge].[% Core Dep], [New ZSPWAR Merge].[Net Spend], [New ZSPWAR Merge].Margin, [New ZSPWAR Merge].Item_Cost, [New ZSPWAR Merge].[Total Material Burden], [New ZSPWAR Merge].[Total Rotables Dep], [New ZSPWAR Merge].SVO_Direct_Labor, [New ZSPWAR Merge].SVO_Labor_OH


    FROM [New ZSPWAR Merge]


    WHERE ((([New ZSPWAR Merge].Bill_doc) Not Like "1*") AND (([New ZSPWAR Merge].ItCC) Like "ZCOM") AND (([New ZSPWAR Merge].ItCa) Not Like "ZRN*")) OR ((([New ZSPWAR Merge].Bill_doc) Not Like "1*") AND (([New ZSPWAR Merge].ItCC) Like "ZCOM") AND (([New ZSPWAR Merge].ItCa) Not Like "I*"));

    I tried to add space to make it a bit more readable.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    SQL Formatted for readability with poorSql:

    Code:
    INSERT INTO [New ZSPWAR Prime] (
    	Payer
    	,AI
    	,Bill_doc
    	,Sqwak
    	,Site
    	,Plnt
    	,Billing_Dt
    	,ProfitCntr
    	,Customer
    	,Equipment
    	,Issued_Material
    	,Material_Description
    	,SLoc
    	,Serial_No
    	,ItCC
    	,Mtrl_Grp
    	,Manuf_No
    	,Manuf_Name
    	,RTN
    	,Warranty
    	,PPC
    	,SVO_No
    	,Ref_No
    	,Notification_Subject
    	,AC_C
    	,Corrective_Action
    	,SB_Data
    	,AC_R
    	,Corr_Action_R
    	,SaleDebCre
    	,Sales_Ord
    	,SO_Date
    	,SU
    	,Returned_Material
    	,Returned_SNR
    	,[Reason for Rejection]
    	,SLCo
    	,Canel_Doc
    	,Can_Doc_Dt
    	,Tail
    	,ItCa
    	,Sold_to
    	,Sold_to_Name
    	,Customer_PO_No
    	,[User]
    	,Comp_Date
    	,OB_Del_NO
    	,OB_Del_DT
    	,Ret_Delv
    	,Rt_SO_It
    	,Ret_Date
    	,Ret_EQUI
    	,Ret_Note
    	,NoteCR_DT
    	,NoteCM_DT
    	,Ret_Note_DESC
    	,WACD
    	,Wty_Prog_Code_Desc
    	,PO_NO
    	,PO_DT
    	,PO_PI
    	,VR_Amount
    	,INV_PT_DT
    	,ZZ07
    	,Freight_Amt
    	,[ME23N PO_Owner]
    	,VDCD
    	,Ven_Den_Code_Desc
    	,DISP
    	,[Disp code Descr]
    	,PRI
    	,HVNE
    	,[Approve By]
    	,[MM Vendor Number]
    	,[Sourcing Vendor Number]
    	,[Sourcing Vendor Name]
    	,[Vendor Contract]
    	,[FFF Class]
    	,[Ref Contract]
    	,[Company Code]
    	,[BUSAC TYPE]
    	,[Warr Typ]
    	,[Aircraft Material]
    	,[Aircraft Serial Number]
    	,[Aircraft Program]
    	,[GL Date]
    	,[Obj Type]
    	,[ID Number]
    	,[MD04 Purchasing Group]
    	,[MD04 PurG Name]
    	,[RO Number]
    	,[RO Creation Date]
    	,[RO Contact Name]
    	,[RO Vendor Code]
    	,[RO Vendor Name]
    	,[ODC Amount]
    	,[Issue_New/Rotable]
    	,Issue_Core
    	,[Recovery_New/Rotable]
    	,Recovery_Core
    	,Labor_Hours
    	,Rate
    	,Labor_Cost
    	,Mtrl_Qty_Billed
    	,Mtrl_ea_Cost
    	,Mtrl_ttl_Cost
    	,Total_Cost
    	,MAUC_STD
    	,Core_cost
    	,Cat_Price
    	,Discount
    	,[% Discount]
    	,[ODC Freight]
    	,[Repair Cost]
    	,[Material Burden]
    	,[% Material Burden]
    	,[Rotable Depreciation]
    	,[% Core Dep]
    	,[Net Spend]
    	,Margin
    	,Item_Cost
    	,[Total Material Burden]
    	,[Total Rotables Dep]
    	,SVO_Direct_Labor
    	,SVO_Labor_OH
    	)
    SELECT [New ZSPWAR Merge].Payer
    	,[New ZSPWAR Merge].AI
    	,[New ZSPWAR Merge].Bill_doc
    	,[New ZSPWAR Merge].Sqwak
    	,[New ZSPWAR Merge].Site
    	,[New ZSPWAR Merge].Plnt
    	,[New ZSPWAR Merge].Billing_Dt
    	,[New ZSPWAR Merge].ProfitCntr
    	,[New ZSPWAR Merge].Customer
    	,[New ZSPWAR Merge].Equipment
    	,[New ZSPWAR Merge].Issued_Material
    	,[New ZSPWAR Merge].Material_Description
    	,[New ZSPWAR Merge].SLoc
    	,[New ZSPWAR Merge].Serial_No
    	,[New ZSPWAR Merge].ItCC
    	,[New ZSPWAR Merge].Mtrl_Grp
    	,[New ZSPWAR Merge].Manuf_No
    	,[New ZSPWAR Merge].Manuf_Name
    	,[New ZSPWAR Merge].RTN
    	,[New ZSPWAR Merge].Warranty
    	,[New ZSPWAR Merge].PPC
    	,[New ZSPWAR Merge].SVO_No
    	,[New ZSPWAR Merge].Ref_No
    	,[New ZSPWAR Merge].Notification_Subject
    	,[New ZSPWAR Merge].AC_C
    	,[New ZSPWAR Merge].Corrective_Action
    	,[New ZSPWAR Merge].SB_Data
    	,[New ZSPWAR Merge].AC_R
    	,[New ZSPWAR Merge].Corr_Action_R
    	,[New ZSPWAR Merge].SaleDebCre
    	,[New ZSPWAR Merge].Sales_Ord
    	,[New ZSPWAR Merge].SO_Date
    	,[New ZSPWAR Merge].SU
    	,[New ZSPWAR Merge].Returned_Material
    	,[New ZSPWAR Merge].Returned_SNR
    	,[New ZSPWAR Merge].[Reason for Rejection]
    	,[New ZSPWAR Merge].SLCo
    	,[New ZSPWAR Merge].Canel_Doc
    	,[New ZSPWAR Merge].Can_Doc_Dt
    	,[New ZSPWAR Merge].Tail
    	,[New ZSPWAR Merge].ItCa
    	,[New ZSPWAR Merge].Sold_to
    	,[New ZSPWAR Merge].Sold_to_Name
    	,[New ZSPWAR Merge].Customer_PO_No
    	,[New ZSPWAR Merge].User
    	,[New ZSPWAR Merge].Comp_Date
    	,[New ZSPWAR Merge].OB_Del_NO
    	,[New ZSPWAR Merge].OB_Del_DT
    	,[New ZSPWAR Merge].Ret_Delv
    	,[New ZSPWAR Merge].Rt_SO_It
    	,[New ZSPWAR Merge].Ret_Date
    	,[New ZSPWAR Merge].Ret_EQUI
    	,[New ZSPWAR Merge].Ret_Note
    	,[New ZSPWAR Merge].NoteCR_DT
    	,[New ZSPWAR Merge].NoteCM_DT
    	,[New ZSPWAR Merge].Ret_Note_DESC
    	,[New ZSPWAR Merge].WACD
    	,[New ZSPWAR Merge].Wty_Prog_Code_Desc
    	,[New ZSPWAR Merge].PO_NO
    	,[New ZSPWAR Merge].PO_DT
    	,[New ZSPWAR Merge].PO_PI
    	,[New ZSPWAR Merge].VR_Amount
    	,[New ZSPWAR Merge].INV_PT_DT
    	,[New ZSPWAR Merge].ZZ07
    	,[New ZSPWAR Merge].Freight_Amt
    	,[New ZSPWAR Merge].[ME23N PO_Owner]
    	,[New ZSPWAR Merge].VDCD
    	,[New ZSPWAR Merge].Ven_Den_Code_Desc
    	,[New ZSPWAR Merge].DISP
    	,[New ZSPWAR Merge].[Disp code Descr]
    	,[New ZSPWAR Merge].PRI
    	,[New ZSPWAR Merge].HVNE
    	,[New ZSPWAR Merge].[Approve By]
    	,[New ZSPWAR Merge].[MM Vendor Number]
    	,[New ZSPWAR Merge].[Sourcing Vendor Number]
    	,[New ZSPWAR Merge].[Sourcing Vendor Name]
    	,[New ZSPWAR Merge].[Vendor Contract]
    	,[New ZSPWAR Merge].[FFF Class]
    	,[New ZSPWAR Merge].[Ref Contract]
    	,[New ZSPWAR Merge].[Company Code]
    	,[New ZSPWAR Merge].[BUSAC TYPE]
    	,[New ZSPWAR Merge].[Warr Typ]
    	,[New ZSPWAR Merge].[Aircraft Material]
    	,[New ZSPWAR Merge].[Aircraft Serial Number]
    	,[New ZSPWAR Merge].[Aircraft Program]
    	,[New ZSPWAR Merge].[GL Date]
    	,[New ZSPWAR Merge].[Obj Type]
    	,[New ZSPWAR Merge].[ID Number]
    	,[New ZSPWAR Merge].[MD04 Purchasing Group]
    	,[New ZSPWAR Merge].[MD04 PurG Name]
    	,[New ZSPWAR Merge].[RO Number]
    	,[New ZSPWAR Merge].[RO Creation Date]
    	,[New ZSPWAR Merge].[RO Contact Name]
    	,[New ZSPWAR Merge].[RO Vendor Code]
    	,[New ZSPWAR Merge].[RO Vendor Name]
    	,[New ZSPWAR Merge].[ODC Amount]
    	,[New ZSPWAR Merge].[Issue_New/Rotable]
    	,[New ZSPWAR Merge].Issue_Core
    	,[New ZSPWAR Merge].[Recovery_New/Rotable]
    	,[New ZSPWAR Merge].Recovery_Core
    	,[New ZSPWAR Merge].Labor_Hours
    	,[New ZSPWAR Merge].Rate
    	,[New ZSPWAR Merge].Labor_Cost
    	,[New ZSPWAR Merge].Mtrl_Qty_Billed
    	,[New ZSPWAR Merge].Mtrl_ea_Cost
    	,[New ZSPWAR Merge].Mtrl_ttl_Cost
    	,[New ZSPWAR Merge].Total_Cost
    	,[New ZSPWAR Merge].MAUC_STD
    	,[New ZSPWAR Merge].Core_cost
    	,[New ZSPWAR Merge].Cat_Price
    	,[New ZSPWAR Merge].Discount
    	,[New ZSPWAR Merge].[% Discount]
    	,[New ZSPWAR Merge].[ODC Freight]
    	,[New ZSPWAR Merge].[Repair Cost]
    	,[New ZSPWAR Merge].[Material Burden]
    	,[New ZSPWAR Merge].[% Material Burden]
    	,[New ZSPWAR Merge].[Rotable Depreciation]
    	,[New ZSPWAR Merge].[% Core Dep]
    	,[New ZSPWAR Merge].[Net Spend]
    	,[New ZSPWAR Merge].Margin
    	,[New ZSPWAR Merge].Item_Cost
    	,[New ZSPWAR Merge].[Total Material Burden]
    	,[New ZSPWAR Merge].[Total Rotables Dep]
    	,[New ZSPWAR Merge].SVO_Direct_Labor
    	,[New ZSPWAR Merge].SVO_Labor_OH
    FROM [New ZSPWAR Merge]
    WHERE (
    		(([New ZSPWAR Merge].Bill_doc) NOT LIKE "1*")
    		AND (([New ZSPWAR Merge].ItCC) LIKE "ZCOM")
    		AND (([New ZSPWAR Merge].ItCa) NOT LIKE "ZRN*")
    		)
    	OR (
    		(([New ZSPWAR Merge].Bill_doc) NOT LIKE "1*")
    		AND (([New ZSPWAR Merge].ItCC) LIKE "ZCOM")
    		AND (([New ZSPWAR Merge].ItCa) NOT LIKE "I*")

  7. #7
    sgtdetritus's Avatar
    sgtdetritus is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    19
    Thanks. Like I said, I don't work directly with the SQL, I use the regular interface. I hope that helps. Still flummoxed

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What values are in
    [New ZSPWAR Merge].ItCC?

    Your SQL says 'LIKE "ZCOM"'
    It may not have anything to do with your issue, but...

  9. #9
    sgtdetritus's Avatar
    sgtdetritus is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    19
    "ZCOM" is a class of part. Other values in that particular column are HVNE or Misc. They are just text values. This particular query is just looking for ones with the value "ZCOM" I have another nearly identical query that looks for a different value in the same column.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you are working with the exact string "ZCOM", then you could use "=" rather than 'Like', but I don't think that is the issue.
    My guess is that it is something within the RPA team's set up. Since Access thinks the table already exists, is it possible that the RPA team is sharing a copy of the front end rather than a copy on each member's PC?

  11. #11
    sgtdetritus's Avatar
    sgtdetritus is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    19
    The thing is that Access is complaining about a table that does not exist. It's referencing the name of an append query, not a table.
    The RPA team does not have a front end to the DB, their scope was strictly with automating the back end daily update process. IN the office where I am, my users have a front end .accdr file on their individual PC's.
    I'm also wondering if it might have something to do with a laggy connection. We have had to insert artificial pauses at other parts of the process because the Blue Prism software will error if an expected dialog box is not immediately there.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have not seen an Append query being identified as a "table". Perhaps others have experienced something along this line??
    Is the RPA team working on the same backend when you say "their scope was strictly with automating the back end daily update process"? Are you talking about the RPA working on the "production/operational system" or is their activity on a separate "test/maintenance" copy?

    Do you run compact and repair on this backend database?

    What are the "processes" that could attempt to run the append query more than once?
    Is/are members of the RPA team running the "automation code" from more than one area?

  13. #13
    sgtdetritus's Avatar
    sgtdetritus is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    19
    I run a Compact and repair every time I run into an error I'm unfamiliar with, and on a regular basis twice a week. I routinely check for any data corruption issues as well.

    As far as the RPA team, as far as I know they are working from one virtual machine. I gave them a test copy of the DB but this came up while they were trying to get things going on the production one. I'm taking full backups everyday in addition to the table backups that run every day, just in case.

    There isn't anything that would cause it to run more than once at a time that I know of. Since the RPA team is half a world away, they are working while it's the middle of the night here.

    Something has occurred to me, but I have no idea if it's what is really going on. When you run an Append query, does access create a temporary table and then tack that on to the end of the destination table? If it did, then this might make sense. I can see it happening very quickly, so that MS never saw the need to document it as part of the inner workings. Obviously I'm spitballing here and don't know what is really going on.

  14. #14
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    You cannot use the name of any existing query as a table name. We know they're different but Access does not allow it.

  15. #15
    sgtdetritus's Avatar
    sgtdetritus is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    19
    I know that. The query does not have the same name of any table at all. It's one of the sources of the problem

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  3. Replies: 4
    Last Post: 04-21-2014, 11:44 AM
  4. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  5. Replies: 7
    Last Post: 07-21-2011, 01:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums