CODEWITHSUNDEEP
sqlvbams-accessms-access-2010
davedigerati
davedigerati

Reputation: 23

How to delete records from linked tables in vba

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.

Upvotes: 2

Views: 1871

Answers (1)

pteranodon
pteranodon

Reputation: 2059

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.

Upvotes: 5

Related Questions