SQL Insert Into New Table from Old Table

I’ve got two table which are near identical, the only difference is the newer one has different field names. Basically the old table was outdated and needed to be re-worked; I’m trying to extract all the field where the VendorNumber = Vendor_ID all the field that are being copied to are guaranteed empty.

The below SQL code if very large, but it’s the best I could use for it…

SQL = "INSERT INTO tbleVendorData (EDIContact,EDIPhone,EDIEmail,EDIPlatform,EDIStatus,EDIMigrationStatus,EDIMigrationDate,TesterName,TestStartDate,LastContactDate," & _
                  "CompletionStatus850,CompletionStatus856,CompletionStatus810C,CompletionStatus810F,CompletionStatusMH10,CompletionStatusPTicket,TestingNotes," & _
                  "TestingStatus850,TestingStatus855,TestingStatus856,TestingStatus810C,TestingStatus810F,GoLiveDate850,GoLiveDate855,GoLiveDate856," & _
                  "GoLiveDate810C,GoLiveDate810F,VendorKickOffDate,Exemption,ExemptionStartDate,ExemptionEndDate,ExemptionReason," & _
                  "ExemptionDescription,Ownership,NECC2500,NECC3500,NECC5000,NECC10000,NECCStartDate,NECCEndDate,SPSCertificate,ProductionStatus850," & _
                  "ProductionStatus855,ProductionStatus856,ProductionStatus810C,ProductionStatus810F,PlatformFI850,PlatformFI855,PlatformFI856," & _
                  "PlatformFI810C,PlatformFI810F,ProductionDate850,ProductionDate855,ProductionDate856,ProductionDate810C,ProductionDate810F," & _
                  "ProductionPrepack,ProductionCrossDock,ProductionMultiStyle,ProductionProductAttribute,ProductionMasterPack,ProductionCrossDockMix," & _
                  "ProductionStandAloneMix,ProductionLegalEntity,FirstASNDate,FirstINVDate,SALeadDays,CrossDockLeadDays,CommunicationNotes,ProductionNotes," & _
                  "QU1,QU2,ISA_ID1,ISA_ID2,GS_ID1,GS_ID2,VAN,VAN2,HYBRID) " & _
           "SELECT EDI_Contact_Name,EDI_Contact_Phone,EDI_Contact_Email,Platform,Status,Migration_Status,Migration_Date,Tester,Testing_Start_Date,Last_Contact," & _
                  "Status_850,Status_856,Status_810C,Status_810F,Status_MH10_Label,Status_PTicket,Tnotes,TS_850,TS_855,TS_856,TS_810C,TS_810F,GLD_850,GLD_855," & _
                  "GLD_856,GLD_810C,GLD_810F,Kick_Off_Mtg,Exemption,Exemption_Start_Date,Exemption_End_Date,Exemption_Text,Exemption_Reason,Ownership," & _
                  "NECC_2500,NECC_3500,NECC_5000,NECC_10000,NECC_Start_Date,NECC_End_Date,SPS_Certificate,PS_850,PS_855,PS_856,PS_810C,PS_810F,P850_FI,P855_FI," & _
                  "P856_FI,P810C_FI,P810F_FI,PDTS_850,PDTS_855,PDTS_856,PDTS_810C,PDTS_810F,Prepack,Cross_Dock,[M-style],PCA,Master_Pack,[XD-Mix],[SA-Mix],Legal_Entity," & _
                  "First_ASN,First_INV,Lead_Days,Cross_Dock_Lead_Days,CNotes,Info,QU1,QU2,ISA_ID1,ISA_ID2,GS_ID1,GS_ID2,VAN,VAN2,HYBRID " & _
             "FROM tbleVendorRecord " & _
            "WHERE tbleVendorRecord.Vendor_ID = VendorNumber "

I am using MS Access in VBA and trying to run the above script I get the “Enter Paramter Value” for VendorNumber any idea why this is?

EDIT:

Answer

Since the destination table is already loaded with the IDs of the vendors whose records you want to store there, you need to update those records rather than insert new records. Therefore, your query must select rows from the source table joined to the destination table on the vendor ID.

SQL = "UPDATE tbleVendorData " & _
      "SET EDIContact = s.EDI_Contact_Name, EDIPhone = s.EDI_Contact_Phone, EDIEmail = EDI_Contact_Email, EDIPlatform = s.Platform, EDIStatus = s.Status," & _
          "EDIMigrationStatus = s.Migration_Status, EDIMigrationDate = s.Migration_Date, TesterName = s.Tester, TestStartDate = s.Testing_Start_Date, " & _
          "LastContactDate = s.Last_Contact, CompletionStatus850 = s.Status_850, CompletionStatus856 = s.Status_856, CompletionStatus810C = s.Status_810C, " & _
          "CompletionStatus810F = s.Status_810F, CompletionStatusMH10 = s.Status_MH10_Label, CompletionStatusPTicket = s.Status_PTicket, TestingNotes = s.Tnotes, " & _
          "TestingStatus850 = s.TS_850, TestingStatus855 = s.TS_855, TestingStatus856 = s.TS_856, TestingStatus810C = s.TS_810C, TestingStatus810F = s.TS_810F, " & _
          "GoLiveDate850 = s.GLD_850, GoLiveDate855 = s.GLD_855, GoLiveDate856 = s.GLD_856, GoLiveDate810C = s.GLD_810C, GoLiveDate810F = s.GLD_810F, " & _
          "VendorKickOffDate = s.Kick_Off_Mtg, Exemption = s.Exemption, ExemptionStartDate = s.Exemption_Start_Date, ExemptionEndDate = s.Exemption_End_Date, " & _
          "ExemptionReason = s.Exemption_Text, ExemptionDescription = s.Exemption_Reason, Ownership = s.Ownership, NECC2500 = s.NECC_2500, NECC3500 = s.NECC_3500, " & _
          "NECC5000 = s.NECC_5000, NECC10000 = s.NECC_10000, NECCStartDate = s.NECC_Start_Date, NECCEndDate = s.NECC_End_Date, SPSCertificate = s.SPS_Certificate, " & _
          "ProductionStatus850 = s.PS_850, ProductionStatus855 = s.PS_855, ProductionStatus856 = s.PS_856, ProductionStatus810C = s.PS810C, " & _
          "ProductionStatus810F = s.PS_810F, PlatformFI850 = s.P850_FI, PlatformFI855 = s.P855_FI, PlatformFI856 = s.P856_FI, PlatformFI810C = s.P810C_FI, " & _
          "PlatformFI810F = s.P810F_FI, ProductionDate850 = s.PDTS_850,ProductionDate855 = s.PDTS_855,ProductionDate856 = s.PDTS_856, ProductionDate810C = s.PDTS_810C, " & _
          "ProductionDate810F = s.PDTS_810F, ProductionPrepack = s.Prepack, ProductionCrossDock = s.Cross_Dock, ProductionMultiStyle = s.[M-style], " & _
          "ProductionProductAttribute = s.PCA, ProductionMasterPack = s.Master_Pack, ProductionCrossDockMix = s.[XD-Mix], ProductionStandAloneMix = s.[SA_Mix], " & _
          "ProductionLegalEntity = s.Legal_Entity, FirstASNDate = s.First_ASN, FirstINVDate = s.First_INV, SALeadDays = s.Lead_Days, CrossDockLeadDays = s.Cross_Dock_Lead_Days, " & _
          "CommunicationNotes = s.CNotes, ProductionNotes = s.Info," & _
          "QU1 = s.QU1, QU2 = s.QU2, ISA_ID1 = s.ISA_ID1, ISA_ID2 = s.ISA_ID2, GS_ID1 = s.GS_ID1, GS_ID2 = s.GS_ID2, VAN = s.VAN, VAN2 = s.VAN2, HYBRID = s.HYBRID " & _
       "FROM " & _
          "(SELECT EDI_Contact_Name,EDI_Contact_Phone,EDI_Contact_Email,Platform,Status,Migration_Status,Migration_Date,Tester,Testing_Start_Date,Last_Contact," & _
                  "Status_850,Status_856,Status_810C,Status_810F,Status_MH10_Label,Status_PTicket,Tnotes,TS_850,TS_855,TS_856,TS_810C,TS_810F,GLD_850,GLD_855," & _
                  "GLD_856,GLD_810C,GLD_810F,Kick_Off_Mtg,Exemption,Exemption_Start_Date,Exemption_End_Date,Exemption_Text,Exemption_Reason,Ownership," & _
                  "NECC_2500,NECC_3500,NECC_5000,NECC_10000,NECC_Start_Date,NECC_End_Date,SPS_Certificate,PS_850,PS_855,PS_856,PS_810C,PS_810F,P850_FI,P855_FI," & _
                  "P856_FI,P810C_FI,P810F_FI,PDTS_850,PDTS_855,PDTS_856,PDTS_810C,PDTS_810F,Prepack,Cross_Dock,[M-style],PCA,Master_Pack,[XD-Mix],[SA-Mix],Legal_Entity," & _
                  "First_ASN,First_INV,Lead_Days,Cross_Dock_Lead_Days,CNotes,Info,QU1,QU2,ISA_ID1,ISA_ID2,GS_ID1,GS_ID2,VAN,VAN2,HYBRID " & _
             "FROM tbleVendorRecord JOIN tbleVendorData ON tbleVendorRecord.VendorID = tbleVendorData.VendorNumber) s"