Trying To Delete Spam From Database

[Woolfcom]

The Access database for my site was hi-jacked by spammers and is now full of enormous amounst of unwanted records.

I have tried to maually remove all the spam. There remains a large amount of data to delete, which through up an error message:

"The search key was not found in any records."

I m seeking advice on how I can remove these records safely so that I dont corrupt the good data etc.

My initial help research has pointed to making a Delete Statement. Now this is something I have never tackled previously and since someone else built the database, could not repair any mess-ups. It is used in a live site which I have taken responsibility for.

I am completely new to handling Access [or any other types of database first hand].

Any advice?

W

 

[stopher]

Welcome to the forum

First take a backup of your database (very very important!). I would also suggest taking a copy of the database and work on the copy until you have convinced yourself that you are happy with what you are doing. Make sure you are not copying the front end if your data is split. It's the backend you need to backup.

Then you need to identify what it is about the spam records that identify them as spam. This will be your criteria for your query. There are lots of methods for use in criteria so post here if you need help.

You are right about using a delete query but before doing that, construct a SELECT query and work out what criteria will list just the spam. By using a SELECT query you can test the criteria to make sure it’s targeting only the records you want.

Next you construct a delete query and use the same criteria.

Good luck

Chris

PS Don't forget to back-up!!!

 

[Woolfcom]

Thanks Stopher,

• Back up done
• Not sure what you mean by "back" and "Front" end [data and forms maybe?]. Its a fairly simple contruct.
• For simplicity in identifying the spam, I have grouped the good records like 1 - 500 [say]. The spam is 501 to 350,000 [I kid u not].
• I assume that this renders a SELECT query unnecessary? But in any case as part of my learning I amy well persue this option.....

As far as contructing the DELETE query, I have looked at the help text and because it is a completely new programme, I am concerned that I dont corrupt the structure by running commands that create problems.

Would appreciate sort of idiot guide please

 

[DCrake]

Right, lets look at the structure of the table that contains the spam data. The first question you need to answer is:

Does the table contain an autonumber field (Sequential self populating numeric field)? if it does then open your table in datasheet view and perform an a-z sort on this number.

Next scroll down your table until you come across the first record that you think is a spammed record, and make a note of the autonumber value.


Keep on scrolling down until you get to either the end of the table or the next good record.

If you establish that there are no more good records you can create a delete query that states

DELETE * FROM YourTableName WHERE YourAutoNumberField > LastGoodAutonumberValue.


If you have a block of spammed data then the syntax will look like this

DELETE * FROM YourTableName WHERE YourAutoNumberField BETWEEN FirstBadAutonumberValue AND LastBadAutonumberValue.

Repeat until you have removed all spammed records.

You can then do a Compact and Repair and then make a backup again.

David

 

[Woolfcom]

Right, lets look at the structure of the table that contains the spam data. The first question you need to answer is:

Does the table contain an autonumber field (Sequential self populating numeric field)? if it does then open your table in datasheet view and perform an a-z sort on this number.

Next scroll down your table until you come across the first record that you think is a spammed record, and make a note of the autonumber value.


Keep on scrolling down until you get to either the end of the table or the next good record.

If you establish that there are no more good records you can create a delete query that states

DELETE * FROM YourTableName WHERE YourAutoNumberField > LastGoodAutonumberValue.


If you have a block of spammed data then the syntax will look like this

DELETE * FROM YourTableName WHERE YourAutoNumberField BETWEEN FirstBadAutonumberValue AND LastBadAutonumberValue.

Repeat until you have removed all spammed records.

You can then do a Compact and Repair and then make a backup again.

David

After playing around with the tables aimlessly. I performed a compact and repair.

For reasons I have yet to fathom I was then able to delete the offending records manually without the inital error message appearing!

Furthermore, the process compacted the database from over 2 gigs to 700k.

I'd now like to change the auto-numbering pattern, should I start a new thread?

tnx

 

[DCrake]

Why do you want to change the pattern? Autonumbers are what they say autonumbers...

Your first priority is detecting how the spammed records got there in the first place.

 

[Woolfcom]

Why do you want to change the pattern? Autonumbers are what they say autonumbers...

Your first priority is detecting how the spammed records got there in the first place.

The reason for the spam, is that there was no apt CAPTCHA script on the data form in place until recently. This has been fixed.

The autonumbers start at 1, I would like to start at 10001152 [and then increment from there] for our records.

I should say that I have never looked at much less programmed databases [its turning out to be quite fun/rewarding and have only just taken over programming of several sites [which I commissioned a n other to develop some years ago and am trying to fix and improve their work.] I intend to use MySql for future projects [for scaling up and because I am learning PHP scripting], but would like to get the older ASP sites to a better standard.

Such as possibly: converting to newer versions of Access [my research suggests that Access is quite resource intensive, compared to other dbases formats....you might correct me]

The sites in question have good but older content which i need to dress up as a start point for expanding them with new scripts etc.

w

 

[Atomic Shrimp]

You should be really careful trying to change an existing autonumber field in a database designed by someone else - those autonumbers might be used as foreign keys in additional tables - if you renumber the main table, the foreign keys in all the other tables will be pointing to the wrong records.

Autonumbers are not really intended to be used for anything visible or meaningful to the user - they're just a way to uniquely identify a record.

 

[Woolfcom]

You should be really careful trying to change an existing autonumber field in a database designed by someone else - those autonumbers might be used as foreign keys in additional tables - if you renumber the main table, the foreign keys in all the other tables will be pointing to the wrong records.

Autonumbers are not really intended to be used for anything visible or meaningful to the user - they're just a way to uniquely identify a record.

Noted.....its a fairly simple database structure and the numbering applies to one table only.....am fairly ceratin that there are no cross linking. Howver, I do take your point on board.

The record I want to use needs to match an accounting standard...hence needing to re-number.

w

 

[neileg]

An autonumber is not guaranteed to be sequential, only unique. It is easy to get gaps in autonumbers

 

[Woolfcom]

An autonumber is not guaranteed to be sequential, only unique. It is easy to get gaps in autonumbers

I have noticed from the years of spamming, there are indeed plentry of gaps. My primary concern is that the numbers increment from my targetted start point.

ta
w

 

[raskew]

The record I want to use needs to match an accounting standard...hence needing to re-number.

If your PK autonumber is somehow tied to an accounting standard, there's a structure problem. Please provide more detail.

Bob

 

[neileg]

I have noticed from the years of spamming, there are indeed plentry of gaps. My primary concern is that the numbers increment from my targetted start point.

You can't guarantee that either. Usually autonumbers increment, but they can jump around. If it is vital that they increment, then you need to construct your own method. Two or three methods have been covered in these forums.

 

[Woolfcom]

If your PK autonumber is somehow tied to an accounting standard, there's a structure problem. Please provide more detail.

Bob

Its not tied.....I just want it that way....to suit my own requirements egL

instead of 1 - 500

i'd like to alter it to : 10001152 to 10001652 etc

An alternative would be to see the output string refelcting this pattern.

dyou see?

w

 

[Rabbie]

Its not tied.....I just want it that way....to suit my own requirements egL

instead of 1 - 500

i'd like to alter it to : 10001152 to 10001652 etc

An alternative would be to see the output string refelcting this pattern.

dyou see?

w

As has already been said autonumbers do suddenly jump so you cannot rely on them so while you might have them running in sequence from 1 to 499 you might find the next was 501 or what ever.

If it is really important for you to have your number like 10001152 etc then I would calculate each new one separately by adding 1 to the current max value in your table.

 

[neileg]

As has already been said autonumbers do suddenly jump so you cannot rely on them so while you might have them running in sequence from 1 to 499 you might find the next was 501 or what ever.

Or even
498
499
22567
22568
Seen that before. Can't tell you why but it does happen.

 

[Woolfcom]

As has already been said autonumbers do suddenly jump so you cannot rely on them so while you might have them running in sequence from 1 to 499 you might find the next was 501 or what ever.

If it is really important for you to have your number like 10001152 etc then I would calculate each new one separately by adding 1 to the current max value in your table.

In which case, how do I go about altering the field with its existing 1- 500 numbering?

Given my novice abillities, is there an simple command I would use to change the existing sequence.....I believe this is the primary key and apllicable only to the one table.

w

 

[Rabbie]

If the existing 1-500 numbering is not used in any relationships/queries etc then you could change it with an update query. It would be best to add a new field to your table for the new value and then once you are sure it has worked correctly you can delete the old field and use the new field renaming it if necessary.

 

[Woolfcom]

If the existing 1-500 numbering is not used in any relationships/queries etc then you could change it with an update query. It would be best to add a new field to your table for the new value and then once you are sure it has worked correctly you can delete the old field and use the new field renaming it if necessary.

will have a go and let you know.....

 

[neileg]

You can't use an update query to change an autonumber. You can change the autonumber to a number field and update it, but then you can't change it back. You can, however, create a new table with an autonumber and use an append query to add the data. Providing your IDs are integer and unique, the autonumber field will accept them.

What I would do, though, is keep the autonumber as a PK and add another field for your own number and increment that as suggested.