时间戳

首页 > 解决方案 > 在 Access 2010 中运行追加查询时出现“表已存在”错误

sql - 在 Access 2010 中运行追加查询时出现“表已存在”错误

问题描述

我的访问数据库中出现了一个奇怪的错误。

错误：

表“[在此处插入表名]”已存在。您创建或重命名了一个表，然后尝试保存它。在 Microsoft 访问可以保存表之前，另一个用户使用相同的名称创建或重命名了一个。

当 RPA 团队运行一个访问宏时，就会发生这种情况，该宏只是一个按顺序运行的查询列表。

该错误出现在从一个表中获取记录并将它们添加到作为工作环境核心的另一个表的附加查询期间。

这只会发生在 RPA 团队身上。当我从我的工作站运行相同的宏时，它运行完美。当其他 2 个被授权的人运行宏时，它运行完美。我无法复制它。在我花在谷歌或这里的时间里，在这种情况下，我也找不到任何关于此错误的参考。我难住了。我可能只是输入了错误的关键字搜索，但我不太清楚下一步该去哪里。

提前致谢。

编辑：这是有问题的追加查询的 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*"
                 ) );

抱歉，代码片段功能今天似乎不喜欢我。

标签： sqlms-accessappend

解决方案

如果这是一个 RPA 解决方案，我可以假设您已经创建了一个工作流，其中创建了一个表，然后将数据插入其中？

如果在使用后删除表时，可能会缺少步骤。或者您需要在尝试创建表之前检查表是否存在。

你可以试试这个

Public Function ifTableExists(tblName As String) As Boolean

    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then

        ifTableExists = True

    End If

End Function

推荐阅读