Solved Using VBA To Delete Data From Multiple Tables (1 Viewer)

EzGoingKev

Registered User.
Local time
Today, 10:08
Joined
Nov 8, 2019
Messages
178
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:

Code:
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

Enthusiastic Amateur
Local time
Today, 14:08
Joined
Sep 21, 2011
Messages
14,042
So does the execute line ever get executed?
 

GUIDO22

Registered User.
Local time
Today, 14:08
Joined
Nov 2, 2003
Messages
515
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

Immoderate Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 28, 2001
Messages
26,999
So... if you build a query

Code:
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

Immoderate Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 28, 2001
Messages
26,999
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

Registered User.
Local time
Today, 10:08
Joined
Nov 8, 2019
Messages
178
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:

Code:
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

Registered User.
Local time
Today, 10:08
Joined
Nov 8, 2019
Messages
178
I ran the Compact and Repair database function and tried running it again. Now I am getting "Too few parameters. Expected 1."
 

EzGoingKev

Registered User.
Local time
Today, 10:08
Joined
Nov 8, 2019
Messages
178
So... if you build a query

Code:
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

Enthusiastic Amateur
Local time
Today, 14:08
Joined
Sep 21, 2011
Messages
14,042
But does the sql run????
Walk through your code line by line.
 

EzGoingKev

Registered User.
Local time
Today, 10:08
Joined
Nov 8, 2019
Messages
178
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%.
 

Attachments

  • ClearPartNos_test_06_14_2022.accdb
    436 KB · Views: 171

MarkK

bit cruncher
Local time
Today, 07:08
Joined
Mar 17, 2004
Messages
8,178
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

Registered User.
Local time
Today, 10:08
Joined
Nov 8, 2019
Messages
178
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:
Code:
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:08
Joined
Feb 19, 2002
Messages
42,970
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

Registered User.
Local time
Today, 10:08
Joined
Nov 8, 2019
Messages
178
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

Super Moderator
Staff member
Local time
Today, 10:08
Joined
Feb 19, 2002
Messages
42,970
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

Registered User.
Local time
Today, 10:08
Joined
Nov 8, 2019
Messages
178
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

Super Moderator
Staff member
Local time
Today, 10:08
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom