How to delete records from linked tables in vba

Question

I've no problems when deleting * records from local tables in my 2010 access db, but as I loop through my list and hit linked tables (local db, this is a fe/be setup) it fails with: Run-time error '3131': Syntax error in FROM clause. code:

    If sanityCheck2 = vbYes Then
        DoCmd.SetWarnings False
       'list the tables to purge here
        dalist = "tblLocal1 tblRemote-2 tblRemote-3"
        darray = Split(dalist, " ")
        For i = LBound(darray) To UBound(darray)
           DoCmd.OpenTable darray(i)
           DoCmd.RunSQL squirrel
        Next
        DoCmd.SetWarnings True

code works fine on tblLocal1, just not on any of the remotes.

I can open the table in datasheet and delete records no prob, just not through here.

If there is a syntax error in an SQL command then show us this SQL command! We cannot fix syntax errors if no syntax is visible. — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Sep 29, 2014 at 15:20
When you are finished with the first item in your array (tblLocal1) are you 100% sure tblRemote-2 is executing the same way you are thinking? Is that your real table name? And of course, we need to see the SQL. — Mark C.
– Mark C., Commented Sep 29, 2014 at 15:27
@paulstock squirrel for the first table that throws the error is : DELETE * FROM tblRemote-2 pretty vanilla.... — davedigerati
– davedigerati, Commented Sep 29, 2014 at 18:50

pteranodon · Accepted Answer · 2014-09-29 17:41:28Z

5

I bet this is choking on the SQL you build around table names with - inside them. SQL will see DELETE * FROM tblRemote-2 WHERE ... as DELETE * FROM tblRemote - 2 WHERE ... (non-sensical subtraction) unless you wrap your table names with square brackets. Change that to DELETE * FROM [tblRemote-2] WHERE ... and you should be fine.

answered Sep 29, 2014 at 17:41

pteranodon

2,0591 gold badge13 silver badges20 bronze badges

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

1 Comment

Shawn V. Wilson Over a year ago

Applies to table names with spaces in them too!

Collectives™ on Stack Overflow

How to delete records from linked tables in vba

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related