At the end of the year i want the user to press a button, and it will take
the records out of the table and reset the autonumber file to #1, but i
haven't been able to do it. Because one of the tables has to have one
beginning record in it i thought i would create a dummy table and when the
button is pressed it would delete the existing table and copy the dummy
(predesigned table) and rename it properly - But i need some help in doing
so. I don't know how to do either function in code, although i've looked in
the knowledge base, manuals, etc. Can you help?

Thanks in advance...you've helped me out of the ditch before.

=?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
<(E-Mail Removed)> wrote in
news:84EC2EE3-5FAD-4115-AC4F-(E-Mail Removed):

> At the end of the year i want the user to press a button, and it will
> take the records out of the table and reset the autonumber file to #1,
> but i haven't been able to do it.

Compacting a database will set empty tables' autonumbers back to one.

But: If You Care What Value An Autonumber Has, You Probably Shouldn't Be
Using An Autonumber.

> Because one of the tables has to
> have one beginning record in it

Unless this record represents real data, I would be very suspicious of a
Design Problem here.

> I thought i would create a dummy table
> and when the button is pressed it would delete the existing table and
> copy the dummy (predesigned table) and rename it properly

// remove the old one
DROP TABLE MyOldTable

// and use a maketable query to copy the master table over
SELECT *
INTO MyOldTable
FROM MyMasterTable

Here is some example code:
' you can use a CurrentProject().Connection too if you
' prefer ADO
Set db = CurrentDB()

' the first command; you can only do one at a time
strSQL = "DROP TABLE MyOldTable;"
db.Execute strSQL, dbFailOnError

' the second command
strSQL = "SELECT * INTO MyOldTable FROM MyMasterTable;"
db.Execute strSQL, dbFailOnError

If MyOldTable is related to other ones, you have to tear down and rebuild
the relationships, indexes and so on. Before you spend a great deal of
time on this, though, I would question how badly you really need to rip
out this table. Why not just delete all but the first record?

HTH


Tim F

if i delete all but the first record, i won't be able to renumber them and
that is what i'm trying to accomplish: to sent autonumber to #1.

I tried docmd.deleteobject and docmd.copyobject but because the data is
linked to the program, it won't work. I'd have to relink with code and i
don't know how to do that either.....


"Tim Ferguson" wrote:

> =?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
> <(E-Mail Removed)> wrote in
> news:84EC2EE3-5FAD-4115-AC4F-(E-Mail Removed):
>
> > At the end of the year i want the user to press a button, and it will
> > take the records out of the table and reset the autonumber file to #1,
> > but i haven't been able to do it.
>
> Compacting a database will set empty tables' autonumbers back to one.
>
> But: If You Care What Value An Autonumber Has, You Probably Shouldn't Be
> Using An Autonumber.
>
> > Because one of the tables has to
> > have one beginning record in it
>
> Unless this record represents real data, I would be very suspicious of a
> Design Problem here.
>
> > I thought i would create a dummy table
> > and when the button is pressed it would delete the existing table and
> > copy the dummy (predesigned table) and rename it properly
>
> // remove the old one
> DROP TABLE MyOldTable
>
> // and use a maketable query to copy the master table over
> SELECT *
> INTO MyOldTable
> FROM MyMasterTable
>
> Here is some example code:
> ' you can use a CurrentProject().Connection too if you
> ' prefer ADO
> Set db = CurrentDB()
>
> ' the first command; you can only do one at a time
> strSQL = "DROP TABLE MyOldTable;"
> db.Execute strSQL, dbFailOnError
>
> ' the second command
> strSQL = "SELECT * INTO MyOldTable FROM MyMasterTable;"
> db.Execute strSQL, dbFailOnError
>
> If MyOldTable is related to other ones, you have to tear down and rebuild
> the relationships, indexes and so on. Before you spend a great deal of
> time on this, though, I would question how badly you really need to rip
> out this table. Why not just delete all but the first record?
>
> HTH
>
>
> Tim F
>
>
>

=?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
<(E-Mail Removed)> wrote in
news:B63804BC-5DCA-43C8-B6E6-(E-Mail Removed):

>
> if i delete all but the first record, i won't be able to renumber them
> and that is what i'm trying to accomplish: to sent autonumber to #1.

<yawn /> If this is important, then you probably have a Design Problem.
See DB commandment number 7 as quoted in my post.

> I tried docmd.deleteobject and docmd.copyobject but because the data
> is linked to the program, it won't work. I'd have to relink with code
> and i don't know how to do that either.....

Not sure what you mean by "linked to the program"...

One way to get round relationships, if there are any, would be to empty
the table and then put back your dummy row:-

DELETE FROM MyOldTable;

INSERT INTO MyOldTable (IDNum, FieldOne, FieldTwo)
SELECT ID, One, Two FROm MyMasterTable;

(if the dummy record is not going to change, you could even get rid of
the dummy table altogether by embedding the values sic:-

INSERT INTO MyOldTable (IDNum, FieldOne, FieldTwo)
VALUES (1, NULL, "This is a dummy record");

Of course, this will not reset the autonumber, but then again it
shouldn't matter. Seriously: I have severe misgivings that your project
needs a Design Revision. This is not what autonumbers are meant to do.

All the best


Tim F

Take another look at
DoCmd.CopyObject

Put a your "default" table into your front-end. You can then copy that to your backend.

DoCmd.CopyObject "C:\My Documents\Copy of NewsgroupAnswers.mdb", "faqtest",
acTable, "Faqsrc"

You will get a warning message that the old table already exists, but you can
still replace it.

Tim Ferguson wrote:
>
> =?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
> <(E-Mail Removed)> wrote in
> news:B63804BC-5DCA-43C8-B6E6-(E-Mail Removed):
>
> >
> > if i delete all but the first record, i won't be able to renumber them
> > and that is what i'm trying to accomplish: to sent autonumber to #1.
>
> <yawn /> If this is important, then you probably have a Design Problem.
> See DB commandment number 7 as quoted in my post.
>
> > I tried docmd.deleteobject and docmd.copyobject but because the data
> > is linked to the program, it won't work. I'd have to relink with code
> > and i don't know how to do that either.....
>
> Not sure what you mean by "linked to the program"...
>
> One way to get round relationships, if there are any, would be to empty
> the table and then put back your dummy row:-
>
> DELETE FROM MyOldTable;
>
> INSERT INTO MyOldTable (IDNum, FieldOne, FieldTwo)
> SELECT ID, One, Two FROm MyMasterTable;
>
> (if the dummy record is not going to change, you could even get rid of
> the dummy table altogether by embedding the values sic:-
>
> INSERT INTO MyOldTable (IDNum, FieldOne, FieldTwo)
> VALUES (1, NULL, "This is a dummy record");
>
> Of course, this will not reset the autonumber, but then again it
> shouldn't matter. Seriously: I have severe misgivings that your project
> needs a Design Revision. This is not what autonumbers are meant to do.
>
> All the best
>
> Tim F