Emptying database (1 Viewer)

[hfsitumo2001]

Now I want to emptying testing data and I want the Transaction number to start from T-1. Last time the way I do it was like this: I unlink the relationship of the tables, then I go to each table and delete all data, after all data in the tables have been deleted, I relink again then I do compact and repair, and it works like that I want.

My question is this: instead of there would be possibility of my fault in relinking and every thing, can I do it like this?: I create new database, then I import all objects: table, query, form, report and modules, but on table and query I import only the structure without data, then I do compact and repair. Can I do it like that and the transaction will start from no.1?

Thank you

 

[theDBguy]

Maybe you could give it a try and let us know what you find out.

 

[Isaac]

I'm not sure I 100% understand your question, but I have a feeling that there is one more technique you ought to at least consider, in case it helps you: Copy/Paste. That is, select the local table, hit Ctrl+C to copy, hit Ctrl+V to paste, and note that it will ask you how you want to paste.....There are some very useful options there, at least one of which may well re-start numbering (though I've never tested). If I remember right pasting a table as structure only does a pretty good job of preserving all table design elements?

 

[gemma-the-husky]

don't unlink the tables. delete the data in a logical order.

if you have tables or customers, orders, order lines, you have to delete the order lines, then the orders, then the customers. You might need to leave some basic records in some of the tables to run your database.

 

[hfsitumo2001]

don't unlink the tables. delete the data in a logical order.

if you have tables or customers, orders, order lines, you have to delete the order lines, then the orders, then the customers. You might need to leave some basic records in some of the tables to run your database.

Yes Gemmal, I do not want to delete data like inventory it is actual data, because I took it from excel the one that used by the previous incumbent. Also supplier I will not delete. But do you think the transaction no will be starting from 1 again?

 

[isladogs]

can I do it like this?: I create new database, then I import all objects: table, query, form, report and modules, but on table and query I import only the structure without data, then I do compact and repair. Can I do it like that and the transaction will start from no.1?

Yes you can do the alternative method suggested.
However, do remember that autonumbers have no meaning other than a unique identifier. It really doesn't matter whether ID values start at 1

 

[CJ_London]

not sure why you feel you need to do this, from your description, it is an autonumber field you want to 'reset'. But the values should not have any meaning

 

[hfsitumo2001]

Yes you can do the alternative method suggested.
However, do remember that autonumbers have no meaning other than a unique identifier. It really doesn't matter whether ID values start at 1

Ok so I just delete the data of order details and pruchase order

 

[Pat Hartman]

1. Autonumbers have no meaning. Their purpose is to supply a unique identifier. Their actual value is irrelevant so it makes no sense to insist that they restart at 1 after you delete the contents of a temp table.
2. Constantly adding/deleting records from temp tables leads to bloat and should be handled in a template database rather than either the FE or BE.

 

[bastanu]

Feel free to have a look and use my data cleaner (make a copy of the back-end and run the cleaner on it):

FBA Data Cleaner - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,
Vlad

 

[The_Doc_Man]

Everyone else has told why not to bother, but there is a simple answer as well. IF you erase the contents of all tables but leave the structures in place, do a Compact & Repair before using the DB with your "live" data. As it happens, the C&R will reset the hidden counters used for autonumbering.

 

[Pat Hartman]

I think the seed reset is version dependent. I think the current version does it but a few releases ago didn't.

 

[isladogs]

I think the seed reset is version dependent. I think the current version does it but a few releases ago didn't.

The autonumber reset has worked since at least Access 97. I haven't tested it on earlier versions.

Similarly if you remove selected records but not all, compacting will cause the seed to be reset from the largest remaining record.
There was a brief glitch with this in Access 2003 but it was fixed
Reset Microsoft Access 2003 AutonNumber | Database Solutions for Microsoft Access | databasedev.co.uk

 

[Isaac]

If the real reason you want to do this is because your users see the autonumber, and you'd like it to be something that makes more sense to them, then you probably need to create a column designed to be sequential, even embed some business meaning in the value if you like, and this column will be user-facing. You can do whatever you want with this column, you can make it look nice and tidy and start with T1 or whatever you want, even "T-1-2020-02" or anything that users like to look at. A simple example of this would be a Quotes table, where you 1) have an autonumber as the real primary key, 2) also have a Quote number, with all kinds of business significance and feel-good qualities to it.

It will not replace or be a substitute for the auto number, which you'll still use for the "real" primary key, joining, etc.

I think your goal is not unusual, but just come to agree that your users like a separate column with some value that makes sense to them - there is nothing wrong with that - as long as you thoroughly understand that it has nothing to do with joining tables, will not replace nor function like your auto number quite, and you still need the auto number. A bit more banter & discussion on this whole issue here, among many other places.

 

[hfsitumo2001]

If the real reason you want to do this is because your users see the autonumber, and you'd like it to be something that makes more sense to them, then you probably need to create a column designed to be sequential, even embed some business meaning in the value if you like, and this column will be user-facing. You can do whatever you want with this column, you can make it look nice and tidy and start with T1 or whatever you want, even "T-1-2020-02" or anything that users like to look at. A simple example of this would be a Quotes table, where you 1) have an autonumber as the real primary key, 2) also have a Quote number, with all kinds of business significance and feel-good qualities to it.

It will not replace or be a substitute for the auto number, which you'll still use for the "real" primary key, joining, etc.

I think your goal is not unusual, but just come to agree that your users like a separate column with some value that makes sense to them - there is nothing wrong with that - as long as you thoroughly understand that it has nothing to do with joining tables, will not replace nor function like your auto number quite, and you still need the auto number. A bit more banter & discussion on this whole issue here, among many other places.

Yes Isaac, mine also is like yours, actually the number shown is not auto number, but I-1 (Issuance 1)

 

[Pat Hartman]

Then do NOT use an autonumber for this purpose. The PK of the table should be the autonumber. You can easily generate a sequence number as the rows are added to the table. I'll even attach a sample of how to do this.