Accidentally deleted a record. Anyway to get it back?
If not, I know the ID number - which is an autonumber field. Because
of the related data from other tables, would I be able to create a new
record and make its ID number, that of the deleted record?
Thanks!
magmike
On Fri, 12 Oct 2007 14:34:26 -0000, magmike
wrote:
If you haven't done anything else with the db, a data recovery service
may still be able to get it back for you. Of course you can recover it
from last night's backup as well.
Sounds like you violated rule #1 of database design, and did not have
referential integrity enforcement selected in the table relationships.
That would have prevented the accidental delete. Fix that!
Yes, you can create an append query and specify the value of your ID
field.
-Tom.
>Accidentally deleted a record. Anyway to get it back?
>
>If not, I know the ID number - which is an autonumber field. Because
>of the related data from other tables, would I be able to create a new
>record and make its ID number, that of the deleted record?
>
>Thanks!
>
>magmike
got an example of the append query for changing the ID #?
If I had referential integrity setup (i'll need to research that),
what would have happened when I selected Delete Record?
mike
On Oct 12, 10:01 am, Tom van Stiphout
> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
> wrote:
>
> If you haven't done anything else with the db, a data recovery service
> may still be able to get it back for you. Of course you can recover it
> from last night's backup as well.
>
> Sounds like you violated rule #1 of database design, and did not have
> referential integrity enforcement selected in the table relationships.
> That would have prevented the accidental delete. Fix that!
>
> Yes, you can create an append query and specify the value of your ID
> field.
>
> -Tom.
>
>
>
> >Accidentally deleted a record. Anyway to get it back?
>
> >If not, I know the ID number - which is an autonumber field. Because
> >of the related data from other tables, would I be able to create a new
> >record and make its ID number, that of the deleted record?
>
> >Thanks!
>
> >magmike- Hide quoted text -
>
> - Show quoted text -
got an example of the append query for changing the ID #?
On Fri, 12 Oct 2007 15:55:24 -0000, magmike
wrote:
INSERT INTO SomeTable ( ID, SomeOtherField )
SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
It would have said "Yo! Can't do that. There are related records in
other tables."
Just try it for yourself.
-Tom.
>got an example of the append query for changing the ID #?
>
>If I had referential integrity setup (i'll need to research that),
>what would have happened when I selected Delete Record?
>
>mike
>
>On Oct 12, 10:01 am, Tom van Stiphout
>> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
>> wrote:
>>
>> If you haven't done anything else with the db, a data recovery service
>> may still be able to get it back for you. Of course you can recover it
>> from last night's backup as well.
>>
>> Sounds like you violated rule #1 of database design, and did not have
>> referential integrity enforcement selected in the table relationships.
>> That would have prevented the accidental delete. Fix that!
>>
>> Yes, you can create an append query and specify the value of your ID
>> field.
>>
>> -Tom.
>>
>>
>>
>> >Accidentally deleted a record. Anyway to get it back?
>>
>> >If not, I know the ID number - which is an autonumber field. Because
>> >of the related data from other tables, would I be able to create a new
>> >record and make its ID number, that of the deleted record?
>>
>> >Thanks!
>>
>> >magmike- Hide quoted text -
>>
>> - Show quoted text -
>
>got an example of the append query for changing the ID #?
On Oct 12, 9:27 pm, Tom van Stiphout
> On Fri, 12 Oct 2007 15:55:24 -0000, magmike
> wrote:
>
> INSERT INTO SomeTable ( ID, SomeOtherField )
> SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
>
> It would have said "Yo! Can't do that. There are related records in
> other tables."
> Just try it for yourself.
>
> -Tom.
>
>
>
> >got an example of the append query for changing the ID #?
>
> >If I had referential integrity setup (i'll need to research that),
> >what would have happened when I selected Delete Record?
>
> >mike
>
> >On Oct 12, 10:01 am, Tom van Stiphout
> >> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
> >> wrote:
>
> >> If you haven't done anything else with the db, a data recovery service
> >> may still be able to get it back for you. Of course you can recover it
> >> from last night's backup as well.
>
> >> Sounds like you violated rule #1 of database design, and did not have
> >> referential integrity enforcement selected in the table relationships.
> >> That would have prevented the accidental delete. Fix that!
>
> >> Yes, you can create an append query and specify the value of your ID
> >> field.
>
> >> -Tom.
>
> >> >Accidentally deleted a record. Anyway to get it back?
>
> >> >If not, I know the ID number - which is an autonumber field. Because
> >> >of the related data from other tables, would I be able to create a new
> >> >record and make its ID number, that of the deleted record?
>
> >> >Thanks!
>
> >> >magmike- Hide quoted text -
>
> >> - Show quoted text -
>
> >got an example of the append query for changing the ID #?- Hide quoted text -
>
> - Show quoted text -
How could I change the ID (autonumber) of a current record?
On Oct 12, 9:27 pm, Tom van Stiphout
> On Fri, 12 Oct 2007 15:55:24 -0000, magmike
> wrote:
>
> INSERT INTO SomeTable ( ID, SomeOtherField )
> SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
>
> It would have said "Yo! Can't do that. There are related records in
> other tables."
> Just try it for yourself.
>
> -Tom.
>
>
>
> >got an example of the append query for changing the ID #?
>
> >If I had referential integrity setup (i'll need to research that),
> >what would have happened when I selected Delete Record?
>
> >mike
>
> >On Oct 12, 10:01 am, Tom van Stiphout
> >> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
> >> wrote:
>
> >> If you haven't done anything else with the db, a data recovery service
> >> may still be able to get it back for you. Of course you can recover it
> >> from last night's backup as well.
>
> >> Sounds like you violated rule #1 of database design, and did not have
> >> referential integrity enforcement selected in the table relationships.
> >> That would have prevented the accidental delete. Fix that!
>
> >> Yes, you can create an append query and specify the value of your ID
> >> field.
>
> >> -Tom.
>
> >> >Accidentally deleted a record. Anyway to get it back?
>
> >> >If not, I know the ID number - which is an autonumber field. Because
> >> >of the related data from other tables, would I be able to create a new
> >> >record and make its ID number, that of the deleted record?
>
> >> >Thanks!
>
> >> >magmike- Hide quoted text -
>
> >> - Show quoted text -
>
> >got an example of the append query for changing the ID #?- Hide quoted text -
>
> - Show quoted text -
What kind of WHERE statement could I use in the append query, to use a
current record, and change it's autonumber to a different (not already
existing) one?
On Oct 19, 12:18 pm, magmike
> On Oct 12, 9:27 pm, Tom van Stiphout
>
>
>
>
>
> > On Fri, 12 Oct 2007 15:55:24 -0000, magmike
> > wrote:
>
> > INSERT INTO SomeTable ( ID, SomeOtherField )
> > SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
>
> > It would have said "Yo! Can't do that. There are related records in
> > other tables."
> > Just try it for yourself.
>
> > -Tom.
>
> > >got an example of the append query for changing the ID #?
>
> > >If I had referential integrity setup (i'll need to research that),
> > >what would have happened when I selected Delete Record?
>
> > >mike
>
> > >On Oct 12, 10:01 am, Tom van Stiphout
> > >> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
> > >> wrote:
>
> > >> If you haven't done anything else with the db, a data recovery service
> > >> may still be able to get it back for you. Of course you can recover it
> > >> from last night's backup as well.
>
> > >> Sounds like you violated rule #1 of database design, and did not have
> > >> referential integrity enforcement selected in the table relationships.
> > >> That would have prevented the accidental delete. Fix that!
>
> > >> Yes, you can create an append query and specify the value of your ID
> > >> field.
>
> > >> -Tom.
>
> > >> >Accidentally deleted a record. Anyway to get it back?
>
> > >> >If not, I know the ID number - which is an autonumber field. Because
> > >> >of the related data from other tables, would I be able to create a new
> > >> >record and make its ID number, that of the deleted record?
>
> > >> >Thanks!
>
> > >> >magmike- Hide quoted text -
>
> > >> - Show quoted text -
>
> > >got an example of the append query for changing the ID #?- Hide quoted text -
>
> > - Show quoted text -
>
> What kind of WHERE statement could I use in the append query, to use a
> current record, and change it's autonumber to a different (not already
> existing) one?- Hide quoted text -
>
> - Show quoted text -
If you are using an autonumber field for ANYTHING meaningful in your
data structure you are messing up BIG time. Autonumber is NOT
designed to be a 'record number' or ANY meaningful what so ever. It
is ONLY used as a way to INTERANLLY identify a unique record. You
should NOT use autonumber to be things such as Record Number, PO
Number, Item Number, etc. It sounds like you are doing something of
the sort.
On Oct 19, 11:35 am, DavidB
> On Oct 19, 12:18 pm, magmike
>
>
>
>
>
> > On Oct 12, 9:27 pm, Tom van Stiphout
>
> > > On Fri, 12 Oct 2007 15:55:24 -0000, magmike
> > > wrote:
>
> > > INSERT INTO SomeTable ( ID, SomeOtherField )
> > > SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
>
> > > It would have said "Yo! Can't do that. There are related records in
> > > other tables."
> > > Just try it for yourself.
>
> > > -Tom.
>
> > > >got an example of the append query for changing the ID #?
>
> > > >If I had referential integrity setup (i'll need to research that),
> > > >what would have happened when I selected Delete Record?
>
> > > >mike
>
> > > >On Oct 12, 10:01 am, Tom van Stiphout
> > > >> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
> > > >> wrote:
>
> > > >> If you haven't done anything else with the db, a data recovery service
> > > >> may still be able to get it back for you. Of course you can recover it
> > > >> from last night's backup as well.
>
> > > >> Sounds like you violated rule #1 of database design, and did not have
> > > >> referential integrity enforcement selected in the table relationships.
> > > >> That would have prevented the accidental delete. Fix that!
>
> > > >> Yes, you can create an append query and specify the value of your ID
> > > >> field.
>
> > > >> -Tom.
>
> > > >> >Accidentally deleted a record. Anyway to get it back?
>
> > > >> >If not, I know the ID number - which is an autonumber field. Because
> > > >> >of the related data from other tables, would I be able to create a new
> > > >> >record and make its ID number, that of the deleted record?
>
> > > >> >Thanks!
>
> > > >> >magmike- Hide quoted text -
>
> > > >> - Show quoted text -
>
> > > >got an example of the append query for changing the ID #?- Hide quoted text -
>
> > > - Show quoted text -
>
> > What kind of WHERE statement could I use in the append query, to use a
> > current record, and change it's autonumber to a different (not already
> > existing) one?- Hide quoted text -
>
> > - Show quoted text -
>
> If you are using an autonumber field for ANYTHING meaningful in your
> data structure you are messing up BIG time. Autonumber is NOT
> designed to be a 'record number' or ANY meaningful what so ever. It
> is ONLY used as a way to INTERANLLY identify a unique record. You
> should NOT use autonumber to be things such as Record Number, PO
> Number, Item Number, etc. It sounds like you are doing something of
> the sort.- Hide quoted text -
>
> - Show quoted text -
Some justification would have been (and still would be) nice. I'm not
sure why using an autonumber would be "messing up BIG time" but am
anxious to find out. However, the autonumber field (titled ID in my
tables) is neccesary for table to table relations. How else am I
supposed to identify the proper record being related in a seperate
table?
hello,
Recovering a deleted record is a manual process. If you have the source
paper of that record, then that data needs to be re-entered, or if
related tables contains parts of the data from the deleted record, then
you need to find those parts and re-enter them into the respective
table.
To prevent accidental delete - you need to set up referential integrity.
This is done by creating a relationship between this table and a
subordinate table -- usually a detail table. You need to de-select
Cascade Delete otherwise you will not only delete the record, but also
delete the related records in the detail table.
Note: If you want to use Autonumber as an ID field, you must ensure
that no deletes are possible in your master table. To do this -- add a
relationship between your Master and detail table you can add an
additional table that would record each new autonumber from the master
table into this new table (the "Anchor" table) and relate the tables
based on the Autonum columb in the master table and whatever you name
the column in your "Anchor" table (which would not be an autonumber
column). In the relationship between the Master and Anchor table make
sure you de-select cascade delete and cascade update. The Anchor table
would be the equivalent of a detail If you need to delete a record from
your master table, you would have to first delete the respective Autonum
value in the Anchor table. Then you could delete the record in the
master table.
To use the Anchor table, you need to add a procedure that will record
the Autonumber value of every new record in your master table into the
Anchor table.
Here is a way to retrieve that Autonum value from your master table
after adding a new record. This sample uses ADODB. I don't think you
can do this with DAO.
Sub testIdentity()
Dim conn As New ADODB.Connection, RS As New ADODB.Recordset
Dim strSql As String
Set conn = CurrentProject.Connection
strSql = "Insert Into tblB(fname, lName) Select 'Bill', 'Smith'"
conn.Execute strSql, , adCmdText + adExecuteNoRecords
strSql = "Select @@Identity"
RS.Open strSql, conn, adOpenForwardOnly, adLockReadOnly, adCmdText
Debug.Print RS(0)
End Sub
Rich
*** Sent via Developersdex http://www.developersdex.com ***
On Oct 19, 2:23 pm, magmike
> On Oct 19, 11:35 am, DavidB
>
>
>
>
>
> > On Oct 19, 12:18 pm, magmike
>
> > > On Oct 12, 9:27 pm, Tom van Stiphout
>
> > > > On Fri, 12 Oct 2007 15:55:24 -0000, magmike
> > > > wrote:
>
> > > > INSERT INTO SomeTable ( ID, SomeOtherField )
> > > > SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
>
> > > > It would have said "Yo! Can't do that. There are related records in
> > > > other tables."
> > > > Just try it for yourself.
>
> > > > -Tom.
>
> > > > >got an example of the append query for changing the ID #?
>
> > > > >If I had referential integrity setup (i'll need to research that),
> > > > >what would have happened when I selected Delete Record?
>
> > > > >mike
>
> > > > >On Oct 12, 10:01 am, Tom van Stiphout
> > > > >> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
> > > > >> wrote:
>
> > > > >> If you haven't done anything else with the db, a data recovery service
> > > > >> may still be able to get it back for you. Of course you can recover it
> > > > >> from last night's backup as well.
>
> > > > >> Sounds like you violated rule #1 of database design, and did not have
> > > > >> referential integrity enforcement selected in the table relationships.
> > > > >> That would have prevented the accidental delete. Fix that!
>
> > > > >> Yes, you can create an append query and specify the value of your ID
> > > > >> field.
>
> > > > >> -Tom.
>
> > > > >> >Accidentally deleted a record. Anyway to get it back?
>
> > > > >> >If not, I know the ID number - which is an autonumber field. Because
> > > > >> >of the related data from other tables, would I be able to create a new
> > > > >> >record and make its ID number, that of the deleted record?
>
> > > > >> >Thanks!
>
> > > > >> >magmike- Hide quoted text -
>
> > > > >> - Show quoted text -
>
> > > > >got an example of the append query for changing the ID #?- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > What kind of WHERE statement could I use in the append query, to use a
> > > current record, and change it's autonumber to a different (not already
> > > existing) one?- Hide quoted text -
>
> > > - Show quoted text -
>
> > If you are using an autonumber field for ANYTHING meaningful in your
> > data structure you are messing up BIG time. Autonumber is NOT
> > designed to be a 'record number' or ANY meaningful what so ever. It
> > is ONLY used as a way to INTERANLLY identify a unique record. You
> > should NOT use autonumber to be things such as Record Number, PO
> > Number, Item Number, etc. It sounds like you are doing something of
> > the sort.- Hide quoted text -
>
> > - Show quoted text -
>
> Some justification would have been (and still would be) nice. I'm not
> sure why using an autonumber would be "messing up BIG time" but am
> anxious to find out. However, the autonumber field (titled ID in my
> tables) is neccesary for table to table relations. How else am I
> supposed to identify the proper record being related in a seperate
> table?- Hide quoted text -
>
> - Show quoted text -
Well of COURSE it is required for relationshiops. I didnt say dont
use auto number I said dont use it for anything thats meaningful
data. The autonumber value for a given record should have no meaning
(for example) to any user of the database in a report etc. Users
should not be able to identify a given Customer, Order, Site, or
whatever else based on the autonumber value. Every 'real' data
element in your table should have a field of its own and none of them
shoudl be the autonumber field. It should ONLY be used as the unique
record identifier internally. Serach the newsgroup and you will see
volumes of data about why it is considered very poor programming to
use it for anything else.
Just one reason (this one ought to be enough, and it's at the base of all
the other reasons most people are likely to quote as though they were
'different') that AutoNumbers should be used only for the internal
identification (surrogate keys) and relating records in one table to records
in another: you may be used to seeing AutoNumbers that are monotonically
increasing and assume that they always will be -- but that is not so, they
are intended only to be _unique_, not in any numeric sequence. If you move
to replication you'll also move to GUID autonumbers, and those look strange
enough to make your users wig out.
In many circumstances, you can skip a few, or more than a few, numbers in
the sequence. If you have a green-eye-shade-and-sleeve-garter-type
accountant / bookkeeper reviewing/auditing your information, those missing
numbers will send them into fits of apoplexy. (And many accountants of more
modern bent will also be "concerned.") Also, if you start to create a new
record and cancel it, that number will be "lost and gone forever like
Clementine of song and story".
Larry Linson
Microsoft Access MVP
"magmike"
news:1192818214.033917.314600@v29g2000prd.googlegroups.com.. .
> On Oct 19, 11:35 am, DavidB
>> On Oct 19, 12:18 pm, magmike
>>
>>
>>
>>
>>
>> > On Oct 12, 9:27 pm, Tom van Stiphout
>>
>> > > On Fri, 12 Oct 2007 15:55:24 -0000, magmike
>> > > wrote:
>>
>> > > INSERT INTO SomeTable ( ID, SomeOtherField )
>> > > SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
>>
>> > > It would have said "Yo! Can't do that. There are related records in
>> > > other tables."
>> > > Just try it for yourself.
>>
>> > > -Tom.
>>
>> > > >got an example of the append query for changing the ID #?
>>
>> > > >If I had referential integrity setup (i'll need to research that),
>> > > >what would have happened when I selected Delete Record?
>>
>> > > >mike
>>
>> > > >On Oct 12, 10:01 am, Tom van Stiphout
>> > > >wrote:
>> > > >> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
>> > > >> wrote:
>>
>> > > >> If you haven't done anything else with the db, a data recovery
>> > > >> service
>> > > >> may still be able to get it back for you. Of course you can
>> > > >> recover it
>> > > >> from last night's backup as well.
>>
>> > > >> Sounds like you violated rule #1 of database design, and did not
>> > > >> have
>> > > >> referential integrity enforcement selected in the table
>> > > >> relationships.
>> > > >> That would have prevented the accidental delete. Fix that!
>>
>> > > >> Yes, you can create an append query and specify the value of your
>> > > >> ID
>> > > >> field.
>>
>> > > >> -Tom.
>>
>> > > >> >Accidentally deleted a record. Anyway to get it back?
>>
>> > > >> >If not, I know the ID number - which is an autonumber field.
>> > > >> >Because
>> > > >> >of the related data from other tables, would I be able to create
>> > > >> >a new
>> > > >> >record and make its ID number, that of the deleted record?
>>
>> > > >> >Thanks!
>>
>> > > >> >magmike- Hide quoted text -
>>
>> > > >> - Show quoted text -
>>
>> > > >got an example of the append query for changing the ID #?- Hide
>> > > >quoted text -
>>
>> > > - Show quoted text -
>>
>> > What kind of WHERE statement could I use in the append query, to use a
>> > current record, and change it's autonumber to a different (not already
>> > existing) one?- Hide quoted text -
>>
>> > - Show quoted text -
>>
>> If you are using an autonumber field for ANYTHING meaningful in your
>> data structure you are messing up BIG time. Autonumber is NOT
>> designed to be a 'record number' or ANY meaningful what so ever. It
>> is ONLY used as a way to INTERANLLY identify a unique record. You
>> should NOT use autonumber to be things such as Record Number, PO
>> Number, Item Number, etc. It sounds like you are doing something of
>> the sort.- Hide quoted text -
>>
>> - Show quoted text -
>
> Some justification would have been (and still would be) nice. I'm not
> sure why using an autonumber would be "messing up BIG time" but am
> anxious to find out. However, the autonumber field (titled ID in my
> tables) is neccesary for table to table relations. How else am I
> supposed to identify the proper record being related in a seperate
> table?
>
>
On Oct 21, 1:10 am, "Larry Linson"
> Also, if you start to create a new
> record and cancel it, that number will be "lost and gone forever like
> Clementine of song and story"
Khalil Gibran wrote a poem about autonumbers.
I try to keep its theme in mind when working with them:
Your autonumbers are not your autonumbers.
They are the sons and daughters of Data's longing for uniqueness.
They come through you but not from you,
And though they are with you, yet they belong not to you.
"lyle"
> Khalil Gibran wrote a poem about autonumbers.
> I try to keep its theme in mind when working with them:
>
> Your autonumbers are not your autonumbers.
> They are the sons and daughters of Data's longing for uniqueness.
> They come through you but not from you,
> And though they are with you, yet they belong not to you.
Very perceptive, that Khalil.
On Oct 21, 1:10 am, "Larry Linson"
> Just one reason (this one ought to be enough, and it's at the base of all
> the other reasons most people are likely to quote as though they were
> 'different') that AutoNumbers should be used only for the internal
> identification (surrogate keys) and relating records in one table to records
> in another: you may be used to seeing AutoNumbers that are monotonically
> increasing and assume that they always will be -- but that is not so, they
> are intended only to be _unique_, not in any numeric sequence. If you move
> to replication you'll also move to GUID autonumbers, and those look strange
> enough to make your users wig out.
>
> In many circumstances, you can skip a few, or more than a few, numbers in
> the sequence. If you have a green-eye-shade-and-sleeve-garter-type
> accountant / bookkeeper reviewing/auditing your information, those missing
> numbers will send them into fits of apoplexy. (And many accountants of more
> modern bent will also be "concerned.") Also, if you start to create a new
> record and cancel it, that number will be "lost and gone forever like
> Clementine of song and story".
>
> Larry Linson
> Microsoft Access MVP
>
> "magmike"
>
> news:1192818214.033917.314600@v29g2000prd.googlegroups.com.. .
>
>
>
> > On Oct 19, 11:35 am, DavidB
> >> On Oct 19, 12:18 pm, magmike
>
> >> > On Oct 12, 9:27 pm, Tom van Stiphout
>
> >> > > On Fri, 12 Oct 2007 15:55:24 -0000, magmike
> >> > > wrote:
>
> >> > > INSERT INTO SomeTable ( ID, SomeOtherField )
> >> > > SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
>
> >> > > It would have said "Yo! Can't do that. There are related records in
> >> > > other tables."
> >> > > Just try it for yourself.
>
> >> > > -Tom.
>
> >> > > >got an example of the append query for changing the ID #?
>
> >> > > >If I had referential integrity setup (i'll need to research that),
> >> > > >what would have happened when I selected Delete Record?
>
> >> > > >mike
>
> >> > > >On Oct 12, 10:01 am, Tom van Stiphout
> >> > > >wrote:
> >> > > >> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
> >> > > >> wrote:
>
> >> > > >> If you haven't done anything else with the db, a data recovery
> >> > > >> service
> >> > > >> may still be able to get it back for you. Of course you can
> >> > > >> recover it
> >> > > >> from last night's backup as well.
>
> >> > > >> Sounds like you violated rule #1 of database design, and did not
> >> > > >> have
> >> > > >> referential integrity enforcement selected in the table
> >> > > >> relationships.
> >> > > >> That would have prevented the accidental delete. Fix that!
>
> >> > > >> Yes, you can create an append query and specify the value of your
> >> > > >> ID
> >> > > >> field.
>
> >> > > >> -Tom.
>
> >> > > >> >Accidentally deleted a record. Anyway to get it back?
>
> >> > > >> >If not, I know the ID number - which is an autonumber field.
> >> > > >> >Because
> >> > > >> >of the related data from other tables, would I be able to create
> >> > > >> >a new
> >> > > >> >record and make its ID number, that of the deleted record?
>
> >> > > >> >Thanks!
>
> >> > > >> >magmike- Hide quoted text -
>
> >> > > >> - Show quoted text -
>
> >> > > >got an example of the append query for changing the ID #?- Hide
> >> > > >quoted text -
>
> >> > > - Show quoted text -
>
> >> > What kind of WHERE statement could I use in the append query, to use a
> >> > current record, and change it's autonumber to a different (not already
> >> > existing) one?- Hide quoted text -
>
> >> > - Show quoted text -
>
> >> If you are using an autonumber field for ANYTHING meaningful in your
> >> data structure you are messing up BIG time. Autonumber is NOT
> >> designed to be a 'record number' or ANY meaningful what so ever. It
> >> is ONLY used as a way to INTERANLLY identify a unique record. You
> >> should NOT use autonumber to be things such as Record Number, PO
> >> Number, Item Number, etc. It sounds like you are doing something of
> >> the sort.- Hide quoted text -
>
> >> - Show quoted text -
>
> > Some justification would have been (and still would be) nice. I'm not
> > sure why using an autonumber would be "messing up BIG time" but am
> > anxious to find out. However, the autonumber field (titled ID in my
> > tables) is neccesary for table to table relations. How else am I
> > supposed to identify the proper record being related in a seperate
> > table?- Hide quoted text -
>
> - Show quoted text -
EXACTLY the reason that I always use RANDOM for my autonumebr fields
rather than sequential. This way I am rid of even the appearance that
the numbers 'mean' anything.
On Oct 22, 11:37 am, DavidB
> On Oct 21, 1:10 am, "Larry Linson"
>
>
>
>
>
> > Just one reason (this one ought to be enough, and it's at the base of all
> > the other reasons most people are likely to quote as though they were
> > 'different') that AutoNumbers should be used only for the internal
> > identification (surrogate keys) and relating records in one table to records
> > in another: you may be used to seeing AutoNumbers that are monotonically
> > increasing and assume that they always will be -- but that is not so, they
> > are intended only to be _unique_, not in any numeric sequence. If you move
> > to replication you'll also move to GUID autonumbers, and those look strange
> > enough to make your users wig out.
>
> > In many circumstances, you can skip a few, or more than a few, numbers in
> > the sequence. If you have a green-eye-shade-and-sleeve-garter-type
> > accountant / bookkeeper reviewing/auditing your information, those missing
> > numbers will send them into fits of apoplexy. (And many accountants of more
> > modern bent will also be "concerned.") Also, if you start to create a new
> > record and cancel it, that number will be "lost and gone forever like
> > Clementine of song and story".
>
> > Larry Linson
> > Microsoft Access MVP
>
> > "magmike"
>
> >news:1192818214.033917.314600@v29g2000prd.googlegroups.com. ..
>
> > > On Oct 19, 11:35 am, DavidB
> > >> On Oct 19, 12:18 pm, magmike
>
> > >> > On Oct 12, 9:27 pm, Tom van Stiphout
>
> > >> > > On Fri, 12 Oct 2007 15:55:24 -0000, magmike
> > >> > > wrote:
>
> > >> > > INSERT INTO SomeTable ( ID, SomeOtherField )
> > >> > > SELECT [Give ID:] AS Expr1, [Give SomeOtherField:] AS Expr2;
>
> > >> > > It would have said "Yo! Can't do that. There are related records in
> > >> > > other tables."
> > >> > > Just try it for yourself.
>
> > >> > > -Tom.
>
> > >> > > >got an example of the append query for changing the ID #?
>
> > >> > > >If I had referential integrity setup (i'll need to research that),
> > >> > > >what would have happened when I selected Delete Record?
>
> > >> > > >mike
>
> > >> > > >On Oct 12, 10:01 am, Tom van Stiphout
> > >> > > >wrote:
> > >> > > >> On Fri, 12 Oct 2007 14:34:26 -0000, magmike
> > >> > > >> wrote:
>
> > >> > > >> If you haven't done anything else with the db, a data recovery
> > >> > > >> service
> > >> > > >> may still be able to get it back for you. Of course you can
> > >> > > >> recover it
> > >> > > >> from last night's backup as well.
>
> > >> > > >> Sounds like you violated rule #1 of database design, and did not
> > >> > > >> have
> > >> > > >> referential integrity enforcement selected in the table
> > >> > > >> relationships.
> > >> > > >> That would have prevented the accidental delete. Fix that!
>
> > >> > > >> Yes, you can create an append query and specify the value of your
> > >> > > >> ID
> > >> > > >> field.
>
> > >> > > >> -Tom.
>
> > >> > > >> >Accidentally deleted a record. Anyway to get it back?
>
> > >> > > >> >If not, I know the ID number - which is an autonumber field.
> > >> > > >> >Because
> > >> > > >> >of the related data from other tables, would I be able to create
> > >> > > >> >a new
> > >> > > >> >record and make its ID number, that of the deleted record?
>
> > >> > > >> >Thanks!
>
> > >> > > >> >magmike- Hide quoted text -
>
> > >> > > >> - Show quoted text -
>
> > >> > > >got an example of the append query for changing the ID #?- Hide
> > >> > > >quoted text -
>
> > >> > > - Show quoted text -
>
> > >> > What kind of WHERE statement could I use in the append query, to use a
> > >> > current record, and change it's autonumber to a different (not already
> > >> > existing) one?- Hide quoted text -
>
> > >> > - Show quoted text -
>
> > >> If you are using an autonumber field for ANYTHING meaningful in your
> > >> data structure you are messing up BIG time. Autonumber is NOT
> > >> designed to be a 'record number' or ANY meaningful what so ever. It
> > >> is ONLY used as a way to INTERANLLY identify a unique record. You
> > >> should NOT use autonumber to be things such as Record Number, PO
> > >> Number, Item Number, etc. It sounds like you are doing something of
> > >> the sort.- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > Some justification would have been (and still would be) nice. I'm not
> > > sure why using an autonumber would be "messing up BIG time" but am
> > > anxious to find out. However, the autonumber field (titled ID in my
> > > tables) is neccesary for table to table relations. How else am I
> > > supposed to identify the proper record being related in a seperate
> > > table?- Hide quoted text -
>
> > - Show quoted text -
>
> EXACTLY the reason that I always use RANDOM for my autonumebr fields
> rather than sequential. This way I am rid of even the appearance that
> the numbers 'mean' anything.- Hide quoted text -
>
> - Show quoted text -
If I select RANDOM and say that duplicates are okay, will it ever
reuse an id or create a new record with an already existing id?
"DavidB"
> EXACTLY the reason that I always use RANDOM
> for my autonumebr fields rather than sequential. This
> way I am rid of even the appearance that the numbers
> 'mean' anything.
Appearance to whom? Surely not to the users... the occasion would truly be
a rare one for me to expose an Autonumber field. They are for internal use,
surrogate keys, and linking and none of those functions requires the user to
see them.
It's not neccessarily, always true, but it is a reasonable assumption that
after paying my rate, a client will likely get a competent contractor to
follow me, and a competent Access contractor should know that Autonumber
fields have no intrinsic meaning.
And, I certainly don't need a reminder. My first encounter with a client's
high-tempered-green-eyeshade-and-sleeve-garter accounting/bookkeeping type
over missing numbers in a sequence of AutoNumbers was quite enough to
impress on my memory forever: even if you think the person is knowledgeable
and won't be misled, you can and should avoid showing them Autonumbers.
Larry Linson
Microsoft Access MVP
Larry Linson
Microsoft Access MVP
On Oct 22, 9:09 pm, "Larry Linson"
> "DavidB"
>
> > EXACTLY the reason that I always use RANDOM
> > for my autonumebr fields rather than sequential. This
> > way I am rid of even the appearance that the numbers
> > 'mean' anything.
>
> Appearance to whom? Surely not to the users... the occasion would truly be
> a rare one for me to expose an Autonumber field. They are for internal use,
> surrogate keys, and linking and none of those functions requires the user to
> see them.
>
> It's not neccessarily, always true, but it is a reasonable assumption that
> after paying my rate, a client will likely get a competent contractor to
> follow me, and a competent Access contractor should know that Autonumber
> fields have no intrinsic meaning.
>
> And, I certainly don't need a reminder. My first encounter with a client's
> high-tempered-green-eyeshade-and-sleeve-garter accounting/bookkeeping type
> over missing numbers in a sequence of AutoNumbers was quite enough to
> impress on my memory forever: even if you think the person is knowledgeable
> and won't be misled, you can and should avoid showing them Autonumbers.
>
> Larry Linson
> Microsoft Access MVP
>
> Larry Linson
> Microsoft Access MVP
Agreed that end users should never see the autonumber. Indeed in my
work they never do. Also having been a green visor type in a previou
incarnation (for some reason i did audit work for 9 years), I know all
to well what they infer fro a 'missing' number. It is my normal
practice to assume nothing. So I don't assume that whoever takes over
my databases when I get hit by a bus is at any given level. In
addition since there (IMHO) is no specific benefit in using
incremental or a detriment to using sequential, I opt for the latter.
Probably more a matter of personal choice than anything else.
On Oct 23, 2:00 pm, DavidB
> On Oct 22, 9:09 pm, "Larry Linson"
>
>
>
>
>
> > "DavidB"
>
> > > EXACTLY the reason that I always use RANDOM
> > > for my autonumebr fields rather than sequential. This
> > > way I am rid of even the appearance that the numbers
> > > 'mean' anything.
>
> > Appearance to whom? Surely not to the users... the occasion would truly be
> > a rare one for me to expose an Autonumber field. They are for internal use,
> > surrogate keys, and linking and none of those functions requires the user to
> > see them.
>
> > It's not neccessarily, always true, but it is a reasonable assumption that
> > after paying my rate, a client will likely get a competent contractor to
> > follow me, and a competent Access contractor should know that Autonumber
> > fields have no intrinsic meaning.
>
> > And, I certainly don't need a reminder. My first encounter with a client's
> > high-tempered-green-eyeshade-and-sleeve-garter accounting/bookkeeping type
> > over missing numbers in a sequence of AutoNumbers was quite enough to
> > impress on my memory forever: even if you think the person is knowledgeable
> > and won't be misled, you can and should avoid showing them Autonumbers.
>
> > Larry Linson
> > Microsoft Access MVP
>
> > Larry Linson
> > Microsoft Access MVP
>
> Agreed that end users should never see the autonumber. Indeed in my
> work they never do. Also having been a green visor type in a previou
> incarnation (for some reason i did audit work for 9 years), I know all
> to well what they infer fro a 'missing' number. It is my normal
> practice to assume nothing. So I don't assume that whoever takes over
> my databases when I get hit by a bus is at any given level. In
> addition since there (IMHO) is no specific benefit in using
> incremental or a detriment to using sequential, I opt for the latter.
> Probably more a matter of personal choice than anything else.- Hide quoted text -
>
> - Show quoted text -
I have been using auto numbers to create an output file to import into
a relational database. These numbers are used to batch file and create
tickets to pull material. I am still creating the database and was
reading your info and I might have duplicate values. I have created a
table with a number of "00001" and I do an append into the auto number
field of the primary table prior to importing the data and appending
the primary table with some queries which then is exported in the
proper format. I compact the database when it is opened so the numbers
will start over. This will create duplicate values if more than one
file is imported and the database has been closed. Is there a way to
loop the numbers from "00001" thru "1998" or even have a letter at
the begining.
Thanks.
Wayne
On Oct 24, 6:44 am, Wayne
> On Oct 23, 2:00 pm, DavidB
>
>
>
>
>
> > On Oct 22, 9:09 pm, "Larry Linson"
>
> > > "DavidB"
>
> > > > EXACTLY the reason that I always use RANDOM
> > > > for my autonumebr fields rather than sequential. This
> > > > way I am rid of even the appearance that the numbers
> > > > 'mean' anything.
>
> > > Appearance to whom? Surely not to the users... the occasion would truly be
> > > a rare one for me to expose an Autonumber field. They are for internal use,
> > > surrogate keys, and linking and none of those functions requires the user to
> > > see them.
>
> > > It's not neccessarily, always true, but it is a reasonable assumption that
> > > after paying my rate, a client will likely get a competent contractor to
> > > follow me, and a competent Access contractor should know that Autonumber
> > > fields have no intrinsic meaning.
>
> > > And, I certainly don't need a reminder. My first encounter with a client's
> > > high-tempered-green-eyeshade-and-sleeve-garter accounting/bookkeeping type
> > > over missing numbers in a sequence of AutoNumbers was quite enough to
> > > impress on my memory forever: even if you think the person is knowledgeable
> > > and won't be misled, you can and should avoid showing them Autonumbers.
>
> > > Larry Linson
> > > Microsoft Access MVP
>
> > > Larry Linson
> > > Microsoft Access MVP
>
> > Agreed that end users should never see the autonumber. Indeed in my
> > work they never do. Also having been a green visor type in a previou
> > incarnation (for some reason i did audit work for 9 years), I know all
> > to well what they infer fro a 'missing' number. It is my normal
> > practice to assume nothing. So I don't assume that whoever takes over
> > my databases when I get hit by a bus is at any given level. In
> > addition since there (IMHO) is no specific benefit in using
> > incremental or a detriment to using sequential, I opt for the latter.
> > Probably more a matter of personal choice than anything else.- Hide quoted text -
>
> > - Show quoted text -
>
> I have been using auto numbers to create an output file to import into
> a relational database. These numbers are used to batch file and create
> tickets to pull material. I am still creating the database and was
> reading your info and I might have duplicate values. I have created a
> table with a number of "00001" and I do an append into the auto number
> field of the primary table prior to importing the data and appending
> the primary table with some queries which then is exported in the
> proper format. I compact the database when it is opened so the numbers
> will start over. This will create duplicate values if more than one
> file is imported and the database has been closed. Is there a way to
> loop the numbers from "00001" thru "1998" or even have a letter at
> the begining.
> Thanks.
> Wayne- Hide quoted text -
>
> - Show quoted text -
"00001" is a string not a number.