ACCESS FEED: Re: [MS_AccessPros] auto number ID in table

Rabu, 23 Oktober 2013

Re: [MS_AccessPros] auto number ID in table

I'll give it a try

currently adding a records seems to be fine, just one of the old ones has a duplicate

On Wednesday, October 23, 2013 9:08:57 AM, John Viescas <JohnV@msn.com> wrote:

Daniel-

What happens when you open one of these tables in Datasheet view, go to a new row, and start typing something? Does Access assign a number to the AutoNumber field that already exists? If so, you can try to fix it by figuring out what the current highest number is, add 1, then go to the Immediate window (CTRL+G) and executing:

CurrentDb.Execute "ALTER TABLE TableName COLUMN FieldName COUNTER(NewHighNumber, 1)"

Where TableName is the name of your table (enclose in brackets [] if the name contains spaces), FieldName is the name of your AutoNumber column (same rule about brackets), and NewHighNumber is the current highest value plus 1.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dan Abc
Sent: Wednesday, October 23, 2013 5:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] auto number ID in table

John,

I got same errors when importing

actually 3 -1017 errors in one table, and 2 -1605 errors in another table and 3rd table nothing

info on the -1017 error, each one has a Chinese character in the ErrorRecid

Daniel,

On Wednesday, October 23, 2013 8:14:14 AM, John Viescas <JohnV@msn.com> wrote:

Daniel-

Your table has clearly become corrupted. It might be that the "next" value for the AutoNumber is screwed up. See if importing all objects into a new database fixes it. If not, I have some other ideas.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dan Abc
Sent: Wednesday, October 23, 2013 5:00 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] auto number ID in table

First I tried again the compact and repair

error codes I got

-1605, The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

-1017, Record is deleted

just not sure how that can happen when my ID is a primary key and set as AutoNumber

Daniel,

On , Dan Abc <daniel2478a@yahoo.ca> wrote:

Hi John,

I'l try that, I got couple backup copies

Thanks,

Daniel

On Tuesday, October 22, 2013 11:29:47 PM, John Viescas <JohnV@msn.com> wrote:

Daniel-

I would first try creating a new empty database and attempting to import all the objects from the old one. Do you have a copy of the records in another database?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dan Abc
Sent: Wednesday, October 23, 2013 6:31 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] auto number ID in table

Hi Duane,

would making a copy of database, delete all records and repair

and then copy records back in help?

Daniel

On Tuesday, October 22, 2013 6:38:06 PM, Duane Hookom <duanehookom@hotmail.com> wrote:

Daniel,

If I got errors like deletions and duplicate primary key values etc. I would restore a recent backup.

Duane Hookom MVP
MS Access

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: daniel2478a@yahoo.ca
> Date: Tue, 22 Oct 2013 18:14:11 -0700
> Subject: Re: [MS_AccessPros] auto number ID in table
>
>
>
> Hi Duane,
>
> I don't remember the error, but it says some records got deleted after
> (compact and repair) or (ranaming and adding columns)
> It also creates a small table with releted info, some 5 digit numbers
>
> The strange part is that I noticed a duplicate record
> The ID and data is on 2 rows
> also some rows don't got a value for the ID and have "#######" in other
> fields
>
>
>
> Thanks,
> d
>
>
>
>
>
> On Tuesday, October 22, 2013 5:58:37 PM, Duane Hookom
> <duanehookom@hotmail.com> wrote:
>
> Daniel,
>
> What is the error? Records are stored like marbles in a bag. There is
> no sort order unless you apply one through a query or other means. As
> long as the records are all still there, I wouldn't consider the
> behavior to be an error.
>
> Duane Hookom MVP
> MS Access
>
> ________________________________
>> To: MS_Access_Professionals@yahoogroups.com
>> From: daniel2478a@yahoo.ca
>> Date: Tue, 22 Oct 2013 17:29:34 -0700
>> Subject: [MS_AccessPros] auto number ID in table
>>
>>
>>
>> Hi All,
>>
>>
>> my database is split in to front and back end, my primary ID is an auto
>> number
>> I can delete records but wont get them back, (or that ID), thats ok
>> I noticed that when I do compact and repair then I end up with some
>> errors, not sure how those occur?
>>
>> after that I notice that my ID is not in order anymore
>> 1, 2, 3, 4, 5, 6, 7, 8, 9...
>> turn into
>> 6, 7, 8, 1, 2, 3, 4, 5, 9...
>>
>> what can cause this?
>>
>> also is it safe to add and rename columns later to a database? (not
>> changing data types)
>>
>>
>> thanks,
>> Daniel
>>
>>
>>
>>
>
>
>
>
>