delete records from multiple linked tables access vba

Question

I have 100 linked tables in ms-Access with the Name "TBL*" and they have the same columns. I tried to create a module using vba that deletes some rows according to an sql query as follows:

Option Compare Database
Option Explicit

Sub DeleteRecords()

    Dim strSQL As String 'sql statement
    Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Set db = CurrentDb
   
For Each tdf In db.TableDefs
    DoCmd.SetWarnings False
    If Not (tdf.Name Like "MSys*") And tdf.Name Like "TBL*" Then
       db.Execute "DELETE  FROM " & tdf.Name & " WHERE " & tdf.Fields(2) & " NOT LIKE '%MUST NOT DEL%'", dbFailOnError
     End If
    'DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
   Next
End Sub

Which means I need only the rows that their second column is like '%MUST NOT DEL%' and delete the others. This code gives me an error of invalid operation. I tried a lot of changes but nothing. I think that maybe I have a syntax error on my query. Any ideas what's wrong?

I suggest you generate the DELETE FROM... SQL in a string variable first so you can see the completed statement (and check it for errors/issues), and then pass it to db.Execute. — Dai
– Dai, Commented Oct 7, 2022 at 12:35
@HansUp Worse, likely all rows are deleted, since it's NOT LIKE. — Erik A
– Erik A, Commented Oct 7, 2022 at 12:56

HansUp · Accepted Answer · 2022-10-10 13:41:36Z

The "Invalid operation" error happens because the code references just tdf.Fields(2) where you want to include the field name in your SQL statement. That error should go away if you explicitly ask for the field's name: tdf.Fields(2).Name

However, you mentioned the "second column" is the one which may contain text matching '%MUST NOT DEL%'. And, since the Fields collection is zero-based, you need Fields(1).Name instead of Fields(2).Name

There is another potential problem lurking. When executing a query from CurrentDb, Access expects * instead of % as the wild card unless you have set the Access option for "SQL Server Compatible Syntax (ANSI 92)". Since I don't know which case applies to you, I used ALIKE instead of LIKE ... which signals the db engine to expect the ANSI 92 % wildcard.

Test the WHERE clause with a SELECT query to make sure it targets only the rows you later want to delete.

For Each tdf In db.TableDefs
    If tdf.Name Like "TBL*" Then
        strSQL =  "DELETE  FROM [" & tdf.Name & "] WHERE [" & tdf.Fields(1).Name & "] NOT ALIKE '%MUST NOT DEL%'"
        Debug.Print strSQL ' you can inspect the completed statement in the Immediate window;
                           ' Ctrl + g will take you there
        db.Execute strSQL, dbFailOnError
      End If
Next

Notes:

Since you're not using DoCmd.RunSQL to run your query, I don't think you need DoCmd.SetWarnings False here.
Whenever tdf.Name Like "TBL*" is True, Not (tdf.Name Like "MSys*") must also be True. So you don't need both for your If ... Then condition; Like "TBL*" is sufficient.
I bracketed the table and field names to avoid problems if either of those names include spaces, punctuation, or match the names of functions or keywords.

Collectives™ on Stack Overflow

delete records from multiple linked tables access vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related