Deleting data from tables

  • Thread starter Thread starter C Tate
  • Start date Start date
C

C Tate

I have an Access database from which I occasionally need to completely
delete data. The data relates to various projects and I usually make up a
list of the projects whose data I wish to delete in Excel. I then import
that spreadsheet into Access as a table. I then use a query like the one
below to delete the relevant data.

DELETE * FROM yourtable
WHERE ProjectName NOT IN
(SELECT ProjectName FROM LinkedExcelTable);

My problem is this. As there are a lot of tables in the database, I wish to
somehow amend my query so that it will operate on ALL of the tables, not
just one at a time which is how I am doing it at present.
 
The best way to do this is to use some simple VBA code to automate it.
Your code could look something like:

Function Delete_Obsolete_Data()
Dim db As DAO.Database
Dim strSQL As String
Dim sTable(4) As String 'for 5 tables
Set db = CurrentDb
sTable(0) = "your table 1"
sTable(1) = "your other table"
sTable(2) = "your table B"
sTable(3) = "yet another table"
sTable(4) = "and another one"
For i = LBound(sTable) To UBound(sTable)
strSQL = "DELETE * FROM " & sTable(i) _
& "WHERE ProjectName NOT IN " _
& "(SELECT ProjectName FROM LinkedExcelTable)"
db.Execute strSQL, dbFailOnError
Next
Set db = Nothing
End Function

The above example is for 5 tables, but can easily be modifien for any
number. Just paste the code in a general module and run it. If you want,
the code can be run from a macro (RunCode action, argument
Delete_Obsolete_Data() ) or called by a command button on a switchboard
or other form.

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos
 
Very many thanks for your help. I tried this but I got an error message
saying something like error 9 - out of range. What am I doing wrong?
 
Most likely, an array bounds issue. Pls post your actual code so I can
have a look.

Nikos
 
Apologies for the delay; I've been off work ill.

Here is the code:

Function Delete_Obsolete_Data()

Dim db As DAO.Database

Dim strSQL As String

Dim sTable(4) As String 'for 13 tables

Set db = CurrentDb

sTable(0) = "AllOutcomes"

sTable(1) = "Table_Accommodation_Referrals"

sTable(2) = "Table_Case_Management"

sTable(3) = "Table_LOA_Other"

sTable(4) = "Table_LOA_Other_2"

sTable(5) = "Table_Planned_Support_Hours"

sTable(6) = "Table_Progress"

sTable(7) = "Table_Supporting_People"

sTable(8) = "tblAccommodationReferrals"

sTable(9) = "tblContacts"

sTable(10) = "tblIndBioAcc"

sTable(11) = "tblReferrals"

sTable(12) = "tblRentDeposit"

For i = LBound(sTable) To UBound(sTable)

strSQL = "DELETE * FROM " & sTable(i) _

& "WHERE ProjectName NOT IN " _

& "(SELECT ProjectName FROM LinkedExcelTable)"

db.Execute strSQL, dbFailOnError

Next

Set db = Nothing

End Function
 
Pls see my comments in line.

HTH,
Nikos

Function Delete_Obsolete_Data()

Dim db As DAO.Database

Dim strSQL As String

Dim sTable(4) As String 'for 13 tables
Click to expand...

Here is the array bounds issue; you are declaring an array of 5, and
then trying to assign 13 values! Change the declaration to:

Dim sTable(12) As String

Set db = CurrentDb

sTable(0) = "AllOutcomes"

sTable(1) = "Table_Accommodation_Referrals"

sTable(2) = "Table_Case_Management"

sTable(3) = "Table_LOA_Other"

sTable(4) = "Table_LOA_Other_2"

sTable(5) = "Table_Planned_Support_Hours"
Click to expand...
This is whre you should be getting the error with the previous array
declaration...

sTable(6) = "Table_Progress"

sTable(7) = "Table_Supporting_People"

sTable(8) = "tblAccommodationReferrals"

sTable(9) = "tblContacts"

sTable(10) = "tblIndBioAcc"

sTable(11) = "tblReferrals"

sTable(12) = "tblRentDeposit"

For i = LBound(sTable) To UBound(sTable)

strSQL = "DELETE * FROM " & sTable(i) _

& "WHERE ProjectName NOT IN " _
Click to expand...
My oversight here, there should be a space before the WHERE, so there is
one between the table name and the WHERE in the resulting SQL string:

& " WHERE ProjectName NOT IN " _
 
Many thanks, I'll give this a try.
Nikos Yannacopoulos said:
Pls see my comments in line.

HTH,
Nikos



Here is the array bounds issue; you are declaring an array of 5, and then
trying to assign 13 values! Change the declaration to:

Dim sTable(12) As String


This is whre you should be getting the error with the previous array
declaration...


My oversight here, there should be a space before the WHERE, so there is
one between the table name and the WHERE in the resulting SQL string:

& " WHERE ProjectName NOT IN " _
Click to expand...
 
I am still not getting it right. Now it says syntax error. I don't fully
understand what you are saying about the space before the where. Either that
or I am still doing it wrong! Here is the code:

Function Delete_Obsolete_Data()
Dim db As DAO.Database
Dim strSQL As String
Dim sTable(12) As String 'for 13 tables
Set db = CurrentDb
sTable(0) = "AllOutcomes"
sTable(1) = "Table_Accommodation_Referrals"
sTable(2) = "Table_Case_Management"
sTable(3) = "Table_LOA_Other"
sTable(4) = "Table_LOA_Other_2"
sTable(5) = "Table_Planned_Support_Hours"
sTable(6) = "Table_Progress"
sTable(7) = "Table_Supporting_People"
sTable(8) = "tblAccommodationReferrals"
sTable(9) = "tblContacts"
sTable(10) = "tblIndBioAcc"
sTable(11) = "tblReferrals"
sTable(12) = "tblRentDeposit"
For i = LBound(sTable) To UBound(sTable)
strSQL = "DELETE * FROM " & sTable(i)_
& "WHERE ProjectName NOT IN "_
& "(SELECT ProjectName FROM LinkedExcelTable)"
db.Execute strSQL, dbFailOnError
Next
Set db = Nothing
End Function
 
Pardon me for jumping in.

You need a space between the quote mark and the word WHERE
You need a space between sTable(i) and the _ (underscore mark)
You need a space between the quote mark and the _ on the ... NOT IN line

The continuation character for a VBA line is a space followed immediately by an underscore.

Hope this helps

Function Delete_Obsolete_Data()
Dim db As DAO.Database
Dim strSQL As String
Dim sTable(12) As String 'for 13 tables
Set db = CurrentDb
sTable(0) = "AllOutcomes"
sTable(1) = "Table_Accommodation_Referrals"
sTable(2) = "Table_Case_Management"
sTable(3) = "Table_LOA_Other"
sTable(4) = "Table_LOA_Other_2"
sTable(5) = "Table_Planned_Support_Hours"
sTable(6) = "Table_Progress"
sTable(7) = "Table_Supporting_People"
sTable(8) = "tblAccommodationReferrals"
sTable(9) = "tblContacts"
sTable(10) = "tblIndBioAcc"
sTable(11) = "tblReferrals"
sTable(12) = "tblRentDeposit"
For i = LBound(sTable) To UBound(sTable)

strSQL = "DELETE * FROM " & sTable(i) _
& " WHERE ProjectName NOT IN " _
& "(SELECT ProjectName FROM LinkedExcelTable)"

db.Execute strSQL, dbFailOnError
Next
Set db = Nothing
End Function
 
Excellent! Yes it does. It's working fine.

John Spencer (MVP) said:
Pardon me for jumping in.

You need a space between the quote mark and the word WHERE
You need a space between sTable(i) and the _ (underscore mark)
You need a space between the quote mark and the _ on the ... NOT IN line

The continuation character for a VBA line is a space followed immediately by an underscore.

Hope this helps

Function Delete_Obsolete_Data()
Dim db As DAO.Database
Dim strSQL As String
Dim sTable(12) As String 'for 13 tables
Set db = CurrentDb
sTable(0) = "AllOutcomes"
sTable(1) = "Table_Accommodation_Referrals"
sTable(2) = "Table_Case_Management"
sTable(3) = "Table_LOA_Other"
sTable(4) = "Table_LOA_Other_2"
sTable(5) = "Table_Planned_Support_Hours"
sTable(6) = "Table_Progress"
sTable(7) = "Table_Supporting_People"
sTable(8) = "tblAccommodationReferrals"
sTable(9) = "tblContacts"
sTable(10) = "tblIndBioAcc"
sTable(11) = "tblReferrals"
sTable(12) = "tblRentDeposit"
For i = LBound(sTable) To UBound(sTable)

strSQL = "DELETE * FROM " & sTable(i) _
& " WHERE ProjectName NOT IN " _
& "(SELECT ProjectName FROM LinkedExcelTable)"

db.Execute strSQL, dbFailOnError
Next
Set db = Nothing
End Function
Click to expand...
 
Glad it worked. Sorry for not getting back to you earlier, I haven't
been able to log on at all yesterday.

John,

Many thanks for helping out!
 
Back
Top