[HELP] Import Spreadsheet to Access Table (1 Viewer)

[Pipo]

Good day everyone, Im pretty new when it comes to VBA and Access so please bear with me.

I have this code that I found in the internet and modified it to fit my needs but it doesnt seem to work properly. It does import the excel spreadsheet to the "ImportTable" but it wont INSERT INTO the "Tbl_FundsData" and Im not really so sure about the WHERE NOT EXIST part.

What Im trying to accomplish is that a user can import an Excel Spreadsheet to Access on the temp table(ImportTable) first then to the main table(Tbl_FundsData). The WHERE NOT EXIST part is there to prevent duplicate data from inserting to the main table.

I really need help on this. Thank you in advance.

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, RecData FROM Tbl_FundsData WHERE Tbl_FundsData.Reg And Tbl_FundsData.Prov And Tbl_FundsData.Mun" & _
        "And Tbl_FundsData.Brgy And Tbl_FundsData.SubProjTit And Tbl_FundsData.RecData = Tbl_ImportTable.Reg And Tbl_ImportTable.Prov" & _
        "And ImportTable.Mun And Tbl_ImportTable.Brgy And Tbl_ImportTable.SubProjTit And Tbl_ImportTable.RecData)"
        

DoCmd.RunSQL qrImp

 

[Ranman256]

Have you tried this without using code, but by using a query?
The query will show you where the errors are or show you your results (or lack thereof)

 

[CJ_London]

looks like you are missing a space here

"[CommCls], [BalCls], [PhysAcc], [FinAcc] FROM ImportTable" & _
"WHERE NOT

tip for the future - use debug.print qrImp to see the generated sql then copy and paste to the query sql window to see the errors

also consider using

currentdb.execute(qrImp, dbfailonerror)

rather than docmd.runsql

dbfailonerror will display the sql error messages rather than a general error message

 

[Pipo]

Thank you so much for the reply guys, really appreciate it.

I followed CJ's advice and this is what happened.

Runtime error '3075':

Syntax error in query expression

NOT EXISTS(SELECT Reg, Prov, Mun, Brgy, SubProjTit, RecData FROM Tbl_FundsData WHERE Tbl_FundsData.Reg And Tbl_FundsData.Prov And Tbl_FundsData.Mun" & _
        "And Tbl_FundsData.Brgy And Tbl_FundsData.SubProjTit And Tbl_FundsData.RecData = Tbl_ImportTable.Reg And Tbl_ImportTable.Prov" & _
        "And ImportTable.Mun And Tbl_ImportTable.Brgy And Tbl_ImportTable.SubProjTit And Tbl_ImportTable.RecData)

 

[Pipo]

"WHERE NOT EXISTS(SELECT FROM Reg, Prov, Mun, Brgy, SubProjTit, RecData ImportTable 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.RecData = ImportTable.RecData)"), dbFailOnError

This is my new code still not working tho.

 

[CJ_London]

if that is your whole code then all you have is the critieria. As suggested use debug to generate the sql statement, copy and paste to the sql window and try to run that. If you still can't see the issue, post the generated sql, not your code that creates it.

however I do not understand your exists function - you are not selecting anything and you have 7 unjoined tables (a cartesian query), yet you only refer to one of them and tbl_fundsdata is not brought through at all.

 

[Pipo]

CurrentDb.Execute ("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 ImportTable " & _
        "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)"), dbFailOnError

This is my entire code and I only have two tables ImportTable and Tbl_FundsData. Now I get this error.

Run-time error '3061':

Too few parameters. Expected 6.

 

[CJ_London]

If you still can't see the issue, post the generated sql, not your code that creates it.

 

[Pipo]

I dont really get it but there is no generated sql, I did use the Debug.Print but it doesnt shows anything. I somehow fix the error but the WHERE NOT EXIST is still not working since its still copying duplicate records.

 

[Cronk]

What you are being asked to do is copy the code

CurrentDb.Execute ("INSERT INTO Tbl_FundsData([Reg],.......
just above the code and replace the Currentdb.execute with debug.print ie
Debug.print ("INSERT INTO Tbl_FundsData([Reg],......
After the code runs, the generated sql will be in the Immediate window and you can copy/paste that into a new query to see what is being generated.

 

[Pipo]

This is what the Immediate Window displays. Im really sorry guys as Ive said Im really new with this and have no idea about whats happening most of the time.

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], [Bal
UtiRef], [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)

 

[Cronk]

I don't see anything obviously wrong. However with something non trivial, the thing to do next is to create a query based on that Sql.

Create a new query, on Home, select View | SQL and paste the code, then try to run the query so see what error results. That will help indentify whether the issue is with the code, or maybe with the data, or field type being different.

Incidentally, I do notice the field in the insert part is [FONT=&quot][BalUtiRef] with the select field being [/FONT][FONT=&quot][Bal UtiRef] with a space in the middle.[/FONT]

 

[Pipo]

Thank you Cronk its kinda working now.

Regarding the database that Im working on. I kinda have a problem with the data types I hope you dont mind if ask. In the excel file there are several types of data most are numbers(Integers) and text I got those covered, I think but beside those are also percents and date.

Instead of displaying 1/07/2016 it becomes 42376
and Instead of 35% it becomes 0 when I import it to Access is there any way to solve this. Thank you in advance.

 

[Cronk]

The field types in your tables should be set in Design mode, to Date/Time and Double.

Just like in Excel, percentages are stored as numbers (decimals) and need to have the appropriate setting adjusted to display the value as a percentage.

 

[Pipo]

Thank you that does it. I have some questions regarding the importing, but I think I should just create another thread for it. Thank you so much.