Solved Using VBA To Delete Data From Multiple Tables

[EzGoingKev]

I have eleven tables that have data appended via queries. All the table names start with "LOAD_". All the tables have a field named "Part Number".

The queries build data for all our part numbers. Sometimes I need data for all part number and sometimes I only want data for specific part numbers. I made a PN_Keep table I add the part numbers I want to keep when dealing with only select part numbers.

I wanted to use this code to run through all the tables and delete the data where the part numbers do not match what I have in my PN_Keep table:

Function ClearPartNos()

On Error GoTo ErrorHandler

    Dim T As TableDef
        For Each T In CurrentDb.TableDefs
            If T.Name Like "LOAD_*" Then
                CurrentDb.Execute "DELETE FROM " & T.Name & " WHERE [Part Number] NOT IN (SELECT [PartNo] FROM PN_Keep)"
             End If
        Next T

Error_Exit:
    Exit Function

ErrorHandler:
    MsgBox Error$
    Resume Error_Exit

End Function

I run it and it does not do anything. I have tested the SQL statement using a query and putting in one of the table names and it works fine.

As always, any assistance is greatly appreciated.

 

[Gasman]

So does the execute line ever get executed?

 

[GUIDO22]

I have tried to replicate using a dummy DB and it works as expected... have you stepped through the code to make sure it is entering the conditional part of your code?

 

[The_Doc_Man]

So... if you build a query

SELECT * FROM T.Name WHERE [Part Number] NOT IN ( SELECT [PartNo] FROM PN_Keep ;

It should show you the records you would have deleted using that DELETE query. Does it show you anything meaningful? I.e. Is that what you meant by "I have tested the SQL statement using a query" ??

 

[The_Doc_Man]

The other question suddenly comes to mind... is this a native Access BE or an SQL Server BE case - because Access and SQL Server use different characters as wildcards.

 

[EzGoingKev]

I.e. Is that what you meant by "I have tested the SQL statement using a query" ??

I went Create -> Query Design -> SQL view and typed in:

DELETE FROM LOAD_Asset WHERE [Part Number] NOT IN (SELECT [PartNo] FROM PN_Keep)

When I opened the LOAD_Asset table the only data still in the table were for the numbers that matched what I had in the PartNo field of the PN_Keep table.

 

[EzGoingKev]

I ran the Compact and Repair database function and tried running it again. Now I am getting "Too few parameters. Expected 1."

 

[EzGoingKev]

So... if you build a query

SELECT * FROM T.Name WHERE [Part Number] NOT IN ( SELECT [PartNo] FROM PN_Keep ;

It should show you the records you would have deleted using that DELETE query. Does it show you anything meaningful?

It shows all the records I want deleted.

 

[Gasman]

But does the sql run????
Walk through your code line by line.

 

[EzGoingKev]

I have tried to replicate using a dummy DB and it works as expected... have you stepped through the code to make sure it is entering the conditional part of your code?

I just setup a dummy db and the code works 100%.

 

[MarkK]

This solution has what I've heard referred to as code smell.

In computer programming, a code smell is any characteristic in the source code of a program that possibly indicates a deeper problem.
- https://en.wikipedia.org/wiki/Code_smell

You only need one table. Add a flag or status field to this table, and maybe a date field. Then, when things change, update the status of the rows in your single table, maybe change the date. Then write a query on your data that returns valid rows. That is a normal data management process.

This approach you have of an array of like-named tables that you poll in a loop, and that trigger the addition/update/deletion of rows in some other master table smells like a poorly structured approach to me.

 

[EzGoingKev]

I figured it out. Not all the LOAD_* tables have the field "Part Number" in them.

I found this thread here and added gemma-the-husky's code to mine:

Function ClearPartNos()

On Error GoTo ErrorHandler

    Dim T As TableDef
        For Each T In CurrentDb.TableDefs
            If T.Name Like "LOAD_*" And fieldexists(T.Name, "Part Number") Then
                CurrentDb.Execute "DELETE FROM " & T.Name & " WHERE [Part Number] NOT IN (SELECT [f1] FROM kmf_list1)"
             End If
        Next T

Error_Exit:
    Exit Function

ErrorHandler:
    MsgBox Error$
    Resume Error_Exit

End Function

Function fieldexists(tablename As String, fieldname As String) As Boolean
Dim exists As Boolean

   exists = False
   On Error Resume Next
   exists = CurrentDb.TableDefs(tablename).Fields(fieldname).Name = fieldname

   fieldexists = exists
End Function

That works 100%. If there is a better way to do it please let me know.

 

[Pat Hartman]

1. Don't use a subselect when a join will do the job. Jet/ACE do not optimize subselects well so then can be very slow in large tables.
2. This add/delete process is suspect
3. Having 11 tables involved is suspect

So, I'm pretty sure that there are lots of better solutions.

 

[EzGoingKev]

3. Having 11 tables involved is suspect

I love seeing comments like this.

We pay a company to convert our data into a specific XML format. They require the data be loaded using (11) sheets.

It is what it is. It is not my circus, not my monkeys. There's the way it ought to be and there's the way it is.

 

[Pat Hartman]

Just because you need to provide 11 files to upload data doesn't mean you need to store your data that way. You store the data in the most efficient way. Then you create the outputs from that. You are letting the tail wag the dog.

 

[EzGoingKev]

Just because you need to provide 11 files to upload data doesn't mean you need to store your data that way. You store the data in the most efficient way. Then you create the outputs from that. You are letting the tail wag the dog.

I work in the automotive aftermarket. The entire industry is based on the tail wagging the dog.

 

[Pat Hartman]

So, you think that is right. OK. Good luck. I've created multiple types of EDI transactions. It isn't that hard. Your exchange partner will tell you what they need so you can get to the substance of the problem. You could easily get rid of the intermediary if you wanted to.