Update Query deleting Data!

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

Can I use an Update Query to delete data out of my tables listed below?

tblHorseInfo.HorseID
tblHorseInfo.HorseName
tblHorseInfo.HorseSire
tblHorseInfo.HorseDam
tblClientInfo.ClientID
tblClientInfo.ClientName
tblClientInfo.ClientEmail
tblClientInfo.ClientAddress
tblTrackInfo.Location
tblTrackInfo.Size
 
Can I use an Update Query to delete data out of my tables listed below?

tblHorseInfo.HorseID
tblHorseInfo.HorseName
tblHorseInfo.HorseSire
tblHorseInfo.HorseDam
tblClientInfo.ClientID
tblClientInfo.ClientName
tblClientInfo.ClientEmail
tblClientInfo.ClientAddress
tblTrackInfo.Location
tblTrackInfo.Size
Click to expand...

You can use an Update query to update at least some of these fields to NULL,
replacing HorseName "Pegasus" with Null (visible as blank), or ClientName
"Bellerophon" by the same.

However you cannot delete autonumber primary keys or any required fields in
this way.

If you want to actually delete RECORDS out of the table, instead of leaving
the record in the table with blank data, then don't use an Update query - use
a Delete query instead.
 
John W. Vinson said:
You can use an Update query to update at least some of these fields to
NULL,
replacing HorseName "Pegasus" with Null (visible as blank), or ClientName
"Bellerophon" by the same.

However you cannot delete autonumber primary keys or any required fields
in
this way.

If you want to actually delete RECORDS out of the table, instead of
leaving
the record in the table with blank data, then don't use an Update query -
use
a Delete query instead.
Click to expand...
Thanks John but Delete Query will only let me delete 1 table in each query
and that is a lot less than 255
Regards Bob
 
If you have saved the necessary data into new, valid tables, consider
creating a new empty database and importing all the valid data and database
objects into the new database, but not the 255 tables you want to be rid of.
If you have declared relationships between those 255 tables and others, you
may have to delete the relationships first before you can import the tables
that were related.

That seems an unusual number of tables... did you start off with a separate
table for each horse, or something? Sounds as if the structure was not
"relational". I'm not sure why you would be better off with an Update Query
than with a Delete Query.

If you were experienced with VBA, you might handle such a situation (after
carefully backing up multiple copies) with VBA code that would generate and
execute Delete Queries, but, even so, you'd need to carefully Compact and
Repair the resulting database.

Larry Linson
Microsoft Office Access MVP
 
Larry Linson said:
If you have saved the necessary data into new, valid tables, consider
creating a new empty database and importing all the valid data and
database objects into the new database, but not the 255 tables you want to
be rid of. If you have declared relationships between those 255 tables and
others, you may have to delete the relationships first before you can
import the tables that were related.

That seems an unusual number of tables... did you start off with a
separate table for each horse, or something? Sounds as if the structure
was not "relational". I'm not sure why you would be better off with an
Update Query than with a Delete Query.

If you were experienced with VBA, you might handle such a situation (after
carefully backing up multiple copies) with VBA code that would generate
and execute Delete Queries, but, even so, you'd need to carefully Compact
and Repair the resulting database.

Larry Linson
Microsoft Office Access MVP
Click to expand...

Thanks Larry, I have about 15 Tables in my database and friends of mine that
are using copies of my database. I am always improving my DB thats why I
need to copy my DB and delete my tables and import my friends tables into my
copy. I always keep a record if I add a field to a table so as to update
their DB. I am now creating 15 Delete Queries for the tables as it will not
delete me delete Multiple at once
Regards Bob
 
Bob Vance said:
Thanks Larry, I have about 15 Tables in my database and friends of mine
that are using copies of my database. I am always improving my DB thats
why I need to copy my DB and delete my tables and import my friends tables
into my copy. I always keep a record if I add a field to a table so as to
update their DB. I am now creating 15 Delete Queries for the tables as it
will not delete me delete Multiple at once
Regards Bob
Click to expand...
Oops Sorry so I can create a blank (Fields) DB to give to my fiends to use
but using my latest version
Regards Bob
 
Bob Vance said:
Oops Sorry so I can create a blank (Fields) DB to give to my fiends to use
but using my latest version
Regards Bob
Click to expand...

Can I suggest that you visit MVP Tony Toews' site
http://www.granite.ab.ca/accsmstr, and read the information on splitting
your database. Few changes to most databases require modifying the data
structure, so you should be able to just distribute a revised front-end /
user interface database (queries, forms, reports, macros, and modules) and
they should be able to link it to their own data tables.

If you do make changes requiring modification of the data structure, that's
a little more trouble... most professional developers will distribute a
separate database that uses VBA code to modify the table and relationship
objects... or write out detailed instructions if you have a little more
trust in the local users to follow them exactly.

Larry Linson
Microsoft Office Access MVP
 
Thanks John but Delete Query will only let me delete 1 table in each query
and that is a lot less than 255
Click to expand...

A Delete query deletes records out of a table; it does not delete tables.

And if you've got 255 tables in your database you have an EXTRAORDINARILY
complicated database.

And if you're routinely deleting (or even emptying) tables, you're probably on
the wrong track.

I'll look at the rest of this thread, but perhaps you could explain what
you're trying to accomplish.
 
Back
Top