Import spreadsheet from Excel to Access VBA

[MagicalKoala]

Good day everyone. Im pretty new to MS Access and VBA so please bear with me. Ive managed to come up with an import code from googling the topic but I have not been able to make it work properly. What Im trying to accomplish is that for the User to import a file from Excel to Access first to a temp table (ImporTable) then to the Main Table(Tbl_FundsData) and the WHERE NOT EXIST is there to prevent duplicates from inserting into the Tbl_FundsData. It works fine except the WHERE NOT EXIST PART and Im not getting any error btw.
Thank you in advance.
This is my code.

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ImportTable", selectFile, True
DoCmd.SetWarnings True

Dim qrImp

qrImp = "INSERT INTO Tbl_FundsData([Reg], [Prov], [Mun], [Brgy], [NoSubProj], [SubProjTit], [FundSrc], " & _
"[Modal], [Cycle], [RecDate], [SubProj], [AppComGrants], [Rehab], [Taf], [LCCcashSPI], " & _
"[TotLCC], [TotProjCost], [GrantRel], [Trnch1], [Trnch2], [Trnch3], [Othrs], [BalRel], " & _
"[WvdExc], [BalRelExc], [GrantUti], [GrantBalUti], [LCCDel], [LCCUti], [LCCBalUti], [LCCDelUti], " & _
"[TotUti], [BalUti], [OthrRBPS], [RefRBPS], [BalRBPS], [RefDSWD], [RefLGU], [BalUtiRef], " & _
"[DVSubCOA], [NonDV], [BalSubCOA], [DVrpmo_srpmo], [DVact_bspmc], [PCFEst], [PCFLiq], [PCFBalLiq], " & _
"[CommOpn], [CommCls], [BalCls], [PhysAcc], [FinAcc]) " & _
"SELECT [Reg], [Prov], [Mun], [Brgy], [NoSubProj], " & _
"[SubProjTit], [FundSrc], [Modal], [Cycle], [RecDate], [SubProj], [AppComGrants], [Rehab], [Taf], " & _
"[LCCcashSPI], [TotLCC], [TotProjCost], [GrantRel], " & _
"[Trnch1], [Trnch2], [Trnch3], [Othrs], [BalRel], " & _
"[WvdExc], [BalRelExc], [GrantUti], [GrantBalUti], [LCCDel], " & _
"[LCCUti], [LCCBalUti], [LCCDelUti], [TotUti], [BalUti], " & _
"[OthrRBPS], [RefRBPS], [BalRBPS], [RefDSWD], [RefLGU], " & _
"[BalUtiRef], [DVSubCOA], [NonDV], [BalSubCOA], [DVrpmo_srpmo], " & _
"[DVact_bspmc], [PCFEst], [PCFLiq], [PCFBalLiq], [CommOpn], " & _
"[CommCls], [BalCls], [PhysAcc], [FinAcc] FROM ImportTable " & _
"WHERE NOT EXISTS " & _
"(SELECT Reg, Prov, Mun, Brgy, SubProjTit, RecDate FROM Tbl_FundsData " & _
"WHERE Tbl_FundsData.Reg = ImportTable.Reg " & _
"AND Tbl_FundsData.Prov = ImportTable.Prov " & _
"AND Tbl_FundsData.Mun = ImportTable.Mun " & _
"AND Tbl_FundsData.Brgy = ImportTable.Brgy " & _
"AND Tbl_FundsData.SubProjTit = ImportTable.SubProjTit " & _
"AND Tbl_FundsData.RecDate = ImportTable.RecDate)"


Debug.Print qrImp
DoCmd.RunSQL (qrImp)

[jonh]

Hi

A join would be more efficient I think e.g.

SELECT a.*
FROM Table2 a
LEFT JOIN Table1 b ON a.x = b.x AND a.y = b.y AND a.z = b.z
WHERE b.id Is Null

[MagicalKoala]

Hi

A join would be more efficient I think e.g.

SELECT a.*
FROM Table2 a
LEFT JOIN Table1 b ON a.x = b.x AND a.y = b.y AND a.z = b.z
WHERE b.id Is Null


Thanks for the reply jonh. I will try that method and see if it works.