MS Access tables troubleshoot

[angelledagain]

Hi there i use MS access to manage a small amount of data but i'm getting annoyed by my basic table structure.

i recently replaced all the data in the table with new data, but now i am adding new records.

thing is, tables normally default back to their original settings and layout when you enter and exit, but for sme reason, new records are appearing at the TOP of the table, instead of at the bottom.

i can re sort the table (TableID = ascending) but as soon as i refresh the table, the new records go to the top and away from other more recent records. really annoying.

can anyone help. i DO NOT want to use a query, i have plenty of those. i want my basic table to default back to the original order.

cheers
alison

[bartonc]

Let's see what they have to say in the Access Forum, shall we...

[JustJim]

Hi there i use MS access to manage a small amount of data but i'm getting annoyed by my basic table structure.

i recently replaced all the data in the table with new data, but now i am adding new records.

thing is, tables normally default back to their original settings and layout when you enter and exit, but for sme reason, new records are appearing at the TOP of the table, instead of at the bottom.

i can re sort the table (TableID = ascending) but as soon as i refresh the table, the new records go to the top and away from other more recent records. really annoying.

can anyone help. i DO NOT want to use a query, i have plenty of those. i want my basic table to default back to the original order.

cheers
alison

Haven't seen that before, how are you adding records? It really shouldn't matter though because a table is just a bucket to hold data. Then you use queries or recordsets to gather and organise the data and forms or reports to present it to your users.

Sorry, just realised I was pontificating... How are you adding records? That may hold the key.

Jim

[MMcCarthy]

Hi there i use MS access to manage a small amount of data but i'm getting annoyed by my basic table structure.

i recently replaced all the data in the table with new data, but now i am adding new records.

thing is, tables normally default back to their original settings and layout when you enter and exit, but for sme reason, new records are appearing at the TOP of the table, instead of at the bottom.

i can re sort the table (TableID = ascending) but as soon as i refresh the table, the new records go to the top and away from other more recent records. really annoying.

can anyone help. i DO NOT want to use a query, i have plenty of those. i want my basic table to default back to the original order.

cheers
alison

What is the primary key of your table?

Also have a look at this tutorial, it may help?

Database Normalisation and Table Structures

[angelledagain]

Haven't seen that before, how are you adding records? It really shouldn't matter though because a table is just a bucket to hold data. Then you use queries or recordsets to gather and organise the data and forms or reports to present it to your users.

Sorry, just realised I was pontificating... How are you adding records? That may hold the key.

Jim

I'm adding the data in to the bottom of the table. Yesterday i had to copy all my data into a new table (whose structure i duplicated from the original table) because one of the records got corrupted and was crashing the file. so i duplicated the table structure and appended all my records to the table. but as i am adding other records, the new records appear at the TOP of the list instead of at the bottom, in the obvious place that the records go. And just now, i reproduced the problem (copied the file structure, pasted the records (did a paste this time, instead of an append)) and the new test record appears in the MIDDLE of the list. in what appears to be a random spot. strangely, when i remove the filter (ie refresh the table) the new record moves up to number 904 of 1297. for no reason. all subsequent records then apprear after this one. it's very odd.

Can you help. as mentioned i do not want to use a query, i want to know why this is behaving so oddly. thanks!
alison

[JustJim]

Bump

Sorry all - I'm away from work and away from home and can only get intermittent access to the 'Net. Can anyone field this for me?

[puppydogbuddy]

Bump

Sorry all - I'm away from work and away from home and can only get intermittent access to the 'Net. Can anyone field this for me?

Sounds like your table indexes are corrupted. Place the table in design view; click the index button on the toolbar;delete the indexes (including Primary Key);save; then re-add the indexes; save; test and see if that helped.

[angelledagain]

Sounds like your table indexes are corrupted. Place the table in design view; click the index button on the toolbar;delete the indexes (including Primary Key);save; then re-add the indexes; save; test and see if that helped.

OH DEAR

i just did that. and the table reorded itself completely randomly when appended to the new table. now the chronology i had in place due to it's sequence is destroyed. now i need to restore from backup tape.

and it seemed such a good idea (!!)

[puppydogbuddy]

OH DEAR

i just did that. and the table reorded itself completely randomly when appended to the new table. now the chronology i had in place due to it's sequence is destroyed. now i need to restore from backup tape.

and it seemed such a good idea (!!)

I told you to delete the primary key index, not the primary key itself. The autonumber primary key should still be there. All you have to do is sort by the key before you rebuild index. So delete indexes again; order the table by the key value using sort button, then rebuild indexes.

[FishVal]

Hi, Alison.

Just a silly question. Have you tried to sort the table by Autonumber field and then save table design?

[angelledagain]

I told you to delete the primary key index, not the primary key itself. The autonumber primary key should still be there. All you have to do is sort by the key before you rebuild index. So delete indexes again; order the table by the key value using sort button, then rebuild indexes.

did that, and it still filters randomly in a non sequential manner. forget it - it's clearly difficult to identify without seeing how oddly it's behaving in the flesh. i'll rebuild using an old format that didn't have this problem and update my data.

and yes, i've reorderd by autonumber and saved, that didn't help.

thanks everyone.
alison

[puppydogbuddy]

did that, and it still filters randomly in a non sequential manner. forget it - it's clearly difficult to identify without seeing how oddly it's behaving in the flesh. i'll rebuild using an old format that didn't have this problem and update my data.

and yes, i've reorderd by autonumber and saved, that didn't help.

thanks everyone.
alison

Alison,
the problem you are having could be a symtom of corruption. I would carefully read the following link and if possible try some of the preventive measures and remedies if they could apply to you.

http://allenbrowne.com/ser-47.html

[angelledagain]

none of these meausre seem to work. this same problem is happening to many number of tables, it's not a corruption it's a constant problem and in fact, even when there is a primary ID number autonumbered which has been created from scratch, changing the filter will do incredibly strange things to just those few records. you all sound so terribly knowledgable and full of bright ideas, any chance someone can replicate a new table in MS access, as i've indicated and see what they come up with? all these suggestions are pretty random.

after all, you're the experts!
thanks

[NeoPa]

Alison,
To do that we will need a more thorough description of your table. The following example can show how to post this info :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Use copy and paste after clicking on the Reply button to get the proper formatting.
You will also need to provide a small subset of the data that we could enter into our copies for this to prove useful. I suggest you test out with a small set of data (ensuring it displays the properties you're talking about) before posting the data here (in CODE tags for readability) for our attempts at reproducing the problem.

[MMcCarthy]

none of these meausre seem to work. this same problem is happening to many number of tables, it's not a corruption it's a constant problem and in fact, even when there is a primary ID number autonumbered which has been created from scratch, changing the filter will do incredibly strange things to just those few records. you all sound so terribly knowledgable and full of bright ideas, any chance someone can replicate a new table in MS access, as i've indicated and see what they come up with? all these suggestions are pretty random.

after all, you're the experts!
thanks

corruption of a database can appear in many guises.

To test try creating a brand new table in a new database and see if the behaviour is duplicated. If it is not then the answer lies in corruption.