Delete All Records

[jcbhydro]

Good Morning,

With the generous assistance of Forum Members I have created a fairly complex Membership Database using Access 2010 and incorporating 5 Tables, 4 Forms, about 10 Queries & Reports and a couple of Macros. Other societies within our UK National network are interested in using the database, so I will obviously need to delete all records to make an 'empty' database available.

Before I embark on this exercise, I have a number of queries to consider;

i) What is the preferred method of deleting all records?
Would it be advisable to leave a handful of records in place for demonstration purposes?

ii) Am I correct in assuming that deleting records from my main Table will automatically result in records also being deleted from 'related' Tables.?
Can I expect to receive multiple error messages as a result of zero records?

iii) I assume that unrelated Tables will require separate deletion.

iv) I use the Dmax + 1 procedure to automatically generate a 'Members ID'. Will an empty database generate a new series starting at 1?

Any suggestions to alleviate my concerns would be much appreciated.

Regards,

jcbhydro

 

[spikepl]

Make a copy and do it - see what happens.

i. Open each table and delete all records - that's the fastest. Whether your DB will work properly afterwards depends on how you made it. This is why you should try it on a copy. Leaving example records? Maybe -all depends. If DB will work without them then they just might confuse new users.

ii No you aren't. Depends on whether you set Cascade Delete on in the relation or not. Cascade Delete on is handy when deleting a parent record because it wipes all the child records. Cascade Delete on is dangerous, for the exactly same reason. It should therefore only be used with forethought.
ii second question - not understood

iii No noooo - the great Accesss Gnome wil read you mind and delete the desired tabels for you

iv Try it. My best bet is that your code will crash. Check the error message and use the Nz function. If it did not crash then good for you.

 

[vbaInet]

i. Through a series of Delete queries and as for leaving a few records for demonstration that's up to you.

ii. If you've set Cascade Delete in the Relationships Designer then yes otherwise no. If you want to know about error messages you'll have to test it out on a copy of your db.

iii. Standalone tables will need their own Delete queries.

iv. If the code is there and working it will.

You'll have to try it out on a backup copy to know.

 

[jcbhydro]

Hi there.

My concerns were ill-founded.
I have deleted all but 6 of the records from the Main Table of a copy of the database, and these were automatically deleted from the related tables, at least partially. a small amount of selective deletion was also requtred.
I have run each of the queries, forms and reports without experiencing any nasty error messages.
I find that my Dmax + 1 numbering system continues to work perfectly from the maximum ID number remaining.

Thank you folks for your reassuring comments.

Regards,

jcbhydro

 

[vbaInet]

No thanks for spikepl?

I find that my Dmax + 1 numbering system continues to work perfectly from the maximum ID number remaining.

Yes it will continue to work unless there are no records in the table in which case it will need an Nz():

=Nz(DMax(), 0) + 1