Regular updates of records in a table

[Thorson]

I currently have my database split into a Backend and Frontend. I have a
table in the back end that consists of data that must be completely deleted
and replaced 3-4 times a year. The old records/data becomes obsolete and is
replaced by the new data. However the only way I know how to do this is to
go into the table, delete all records and then copy and paste the new records
in.

Is there a way I can create a form or something so that this can be done
from the FrontEnd?

It probably isn't a big deal because it is only done a few times a year...

Thanks,

 

[Douglas J. Steele]

The fact that your table's in a back-end makes no difference at all.
Whatever you're doing there can be done using the linked tables in your
front-end.

 

[John W. Vinson]

I currently have my database split into a Backend and Frontend. I have a
table in the back end that consists of data that must be completely deleted
and replaced 3-4 times a year. The old records/data becomes obsolete and is
replaced by the new data. However the only way I know how to do this is to
go into the table, delete all records and then copy and paste the new records
in.

Is there a way I can create a form or something so that this can be done
from the FrontEnd?

It probably isn't a big deal because it is only done a few times a year...

Thanks,

Queries can (and should!!!) be used for this operation. A Delete query will
let you delete records, using criteria (say on a date field to limit which
records are deleted); the replacement records would be added by an Append
query.

Get to know queries better... they're absolutely *basic* to any productive use
of Access!

You might find some useful references here:
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Thorson]

Thank you, I think that will work perfectly!

 

[Thorson]

Thanks for the help and the references. I'm not very familiar with Delete or
Append Queries, so I will have to do a little learning before I start
building.

Thanks!

 

[Thorson]

Just did that, thanks for the reminder

 

[Thorson]

How do I write the criteria for the where clause to delete records that have
data?

 

[John W. Vinson]

How do I write the criteria for the where clause to delete records that have
data?

ummmm....

what data?

You set the criteria by putting a criterion in the Criteria line of the query
grid. A criterion of

IS NOT NULL

will find records where that particular field contains data. A criterion on a
date field of

< DateAdd("yyyy", -1, Date())

will select records where that date is one year ago or older.

If you want to completely empty the table deleting all records, just use no
criterion at all.

If you have records that don't contain *any data at all*... why!?! and why
keep them if you do!?

 

[Thorson]

I do just want to empty the whole table, so I guess I don't need to use any
criteria.

Thanks,