Creating a empty table from an existing table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?=
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.
Click to expand...

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
Click to expand...

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
Click to expand...

// 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.....
 
=?Utf-8?B?QmlsbCBCcmlua3dvcnRo?=
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.....
Click to expand...

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