Creating a empty table from an existing table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Brinkworth - 21 Feb 2005 15:29 GMT

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.

Reply to this Message

Tim Ferguson - 21 Feb 2005 17:56 GMT

=?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
<BillBrinkworth@discussions.microsoft.com> wrote in
news:84EC2EE3-5FAD-4115-AC4F-B7F5A0660A88@microsoft.com:

> 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

Reply to this Message

Bill Brinkworth - 21 Feb 2005 18:21 GMT

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.....

> =?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
> <BillBrinkworth@discussions.microsoft.com> wrote in
[quoted text clipped - 50 lines]
>
>

Reply to this Message

Tim Ferguson - 22 Feb 2005 17:55 GMT

=?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
<BillBrinkworth@discussions.microsoft.com> wrote in
news:B63804BC-5DCA-43C8-B6E6-A0B8F4FA121D@microsoft.com:

> 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

Reply to this Message

John Spencer (MVP) - 25 Feb 2005 00:54 GMT

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.

> =?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
> <BillBrinkworth@discussions.microsoft.com> wrote in
[quoted text clipped - 33 lines]
>
> Tim F

Reply to this Message