dynamically delete a row from every table inside for loop in access vba

Question

I am trying to match excel files which is placed in a folder with tables which exist in access database (with the same name as my excel files) and trying to import data which is in excel to access.

Sub import()

Dim blnHasFieldNames As Boolean
Dim strWorksheet As String, strTable As String
Dim strPath As String, strPathFile As String

blnHasFieldNames = True


strPath = "D:\PersonalData\working_table\"

strWorksheet = "Sheet1"

' Import the data from each workbook file in the folder
strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      strTable = Left(strFile, InStrRev(strFile, ".xlsx") - 1)

      DoCmd.TransferSpreadsheet acImport, _
            acSpreadsheetTypeExcel9, strTable, strPathFile, _
            blnHasFieldNames, strWorksheet & "$"

      DoCmd.RunSQL ("DELETE * FROM " & strTable & " WHERE SPEC_ID = 'Specification ID'")



      strFile = Dir()
Loop



End Sub

I am successfully able to get the data into my access database tables, however my requirement is to delete the row from all such tables where SPEC_ID = 'Specification ID' . I am getting an error in line:

DoCmd.RunSQL ("DELETE * FROM " & strTable & " WHERE SPEC_ID = 'Specification ID'")

which states:

Run-time error: '3131' Syntax error in FROM clause.

Kindly guide me what I may have been doing wrong.

Thanks in advance.

does your database table name contain any spaces? if yes than add your database name in [ strTable ] (Square bracket). From where you get the Specification ID ? I cannot find any integer variable here .. — Tarun. P
– Tarun. P, Commented Sep 5, 2018 at 7:53
@VincentG : strTable is same as strFile except the .xlsx extension. — anky
– anky, Commented Sep 5, 2018 at 8:33
There are many reasons why an unbracketed table name might be invalid becides spaces. Just bracket it, like Tarun P said, that should avoid most (but not all) possible invalid table names. — Erik A
– Erik A, Commented Sep 5, 2018 at 8:54
try this DoCmd.RunSQL ("DELETE * FROM [" & strTable & "] WHERE SPEC_ID = 'Specification ID'") — Tarun. P
– Tarun. P, Commented Sep 5, 2018 at 8:57

HansUp · Accepted Answer · 2018-09-05 15:36:28Z

2

Using DoCmd.SetWarnings is a recipe for trouble. What if there is an error in the sql and your code exits without setting the warnings back to true? All sorts of weird stuff happens. Instead use the simpler and just as easy

CurrentDb.Execute "DELETE * FROM [" & strTable & "] WHERE SPEC_ID = 'Specification ID'"

You'll still need to check that strTable doesn't have [ or ] in the name (otherwise the bracketing won't be effective) and you'll need to either remove them or escape them (depending on how your actual table is named).

edited Sep 5, 2018 at 15:36

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

answered Sep 5, 2018 at 15:26

Brad

12.4k4 gold badges51 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

anky Over a year ago

Thanks @Brad. Would it not give me a pop-up before executing the delete in each table since there are almost 30 tables on which I am executing this on. Sorry but it's late night here and I am just being lazy in asking this question. ;) I have written a Python script post which I am calling this script so I am sure that no other table other than these would contain multiple rows containing 'Specification ID'

Brad Over a year ago

Using CurrentDb.Execute does not show a popup when inserting/deleting/updating data. Second advantage: you can also call CurrentDb.RecordsAffected immediately after to check how many records were deleted. It seems like you know there will only be one but you can use this to confirm.

Brad Over a year ago

You're calling this from Python?

anky Over a year ago

Wow that's cool if you can check logs with a command. Thanks a lot. I will check if this runs tomorrow early morning and let you know. :) Thanks a lot for taking your time out to reply. I don't use vba much I just search and edit whatever I find. Thanks for letting me know this I have a clearer concept. Yep I am executing this after I have some data from Python. Not exactly integrating it. However you can call a vba script via Python too. :)

anky Over a year ago

Hi @Brad I tested this and its working fine. :) Thanks a lot.

Tarun. P · Accepted Answer · 2018-09-05 09:00:49Z

0

do like this

Docmd.SetWarnings False

DoCmd.RunSQL ("DELETE * FROM [" & strTable & "] WHERE SPEC_ID = 'Specification ID'")

Docmd.SetWarnings True

answered Sep 5, 2018 at 9:00

Tarun. P

4421 gold badge8 silver badges17 bronze badges

2 Comments

anky Over a year ago

Thanks a lot. God bless you. :)

anky Over a year ago

the answer provided below is more safe to use , just incase any user wants to copy paste this code. Hence changing the accepted answer to the below one. But thanks for your help. :)

Collectives™ on Stack Overflow

dynamically delete a row from every table inside for loop in access vba

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related