Is my table corrupt?... If so, how do I fix it?

[kjcmas]

Hello All,

Let me start by saying that I do not claim to be an expert database developer. I own a small company. I'm good with Access, but from time to time, it gets the best of me.

I've been reading alot of the posts so please forgive me if this topic has been covered. I couldn't find a thread to help me with my problem.

I have a table called 'projects'. This table has an autonumber field (you know where this is going, huh?) that serves as the primary key for the table. I realize that this is a big no-no now. Please go easy on me...

I have a form, also called 'projects'. This form has a comboBox that uses fields from the 'projects' table as its rowSource. The user (myself) selects a record from the comboBox and the afterUpdate finds the record in the table and bookmarks it. The corresponding fields on the form update. We all know the drill.

I'm calling any record entered prior to today, an 'old' record...
... anything from today is a 'new' record.

So now I can still pull up 'old' records, but not 'new' records. Even if I close the form and try to add it directly into the table, it's like access doesn't recognize it. It'll let me enter it, but I cannot pull it up via the comboBox.

My first instinct was to just copy the table (structure & data) into a new one and see if the problem goes away. Two things... the problem does not go away and it renumbers my primaray key and so even if the problem did go away it would give all of the related records in other tables new parents, leaving a few orphans. so much for no child left behind...

anyway, i tried copying the structure only into a new table. I manually added a record. I was able to pull it up using the form. I copied the 'old' records from the old table to the new table. I can pull up those... but now I can't pull up the record I entered manually. I imagine that I copied the corruption into the new table.

It's almost like that the table is corrupt in that it will not index new records properly. I'm not referring to my own indexing, I'm referring to something that I suspect microsoft has on the backside.

Can anybody help me?...

 

[kjcmas]

***Hangs head in shame***

Sorry people. My table is fine. Somehow, my one-to-many join got changed. I don't how, when, or why, but the form was unable to display the record becuase it wasn't in the recordset! the comboBox runs a slightly different query and so that's it was acting like it was available.

I feel like such a spaz.

Maybe I'll come back with a real issue in the future...

 

[tehNellie]

I have a table called 'projects'. This table has an autonumber field (you know where this is going, huh?) that serves as the primary key for the table. I realize that this is a big no-no now. Please go easy on me...

There's nothing wrong with using an autonumber field as the primary key, in some cases it's unavoidable.

Glad you got your other problem sorted.

 

[kjcmas]

Serious Table Problem

Well, if you've been following this short little thread, you'll find that I thought I was okay. Between yesterday afternoon and last night, my database has corrupted. I've managed to recover my phsyical data (from the corrupt db) and most of my VBA code from a backup.

Here's the thing. As stated in my first post, I've been using the autonumber fields as my primary keys. My db ahs around 52000 total records... all of which are dependant on one autonumber field or another. When I recovered the data from the corrupt db, I pasted it into a new db. Access doesn't allow you to paste the actual value of the autonumber fields into another autonumber field. I had to paste them into a number field so that I could retain my keys.

I'm able to access all the recovered data just fine. But I will not be able to add new records now, without programmatically entering a value into the primary key field. I'm just not sure of the best way to do that.

Does anyone know of a way to somehow force the autonumber field to accept my values. How about an access db recovery utility. when/if the utility recovers the data (which it shold be able to, since I could), will it reconstruct the table as it was? will it be a true recovery, or just a glorified copy and paste...

I'm screwed guys. Please help...

Kevin

 

[Dennisk]

Corrupted Autonumber in main table of a one to many relationship.

if the autonumber becomes corrupted then
1 copy the database
2) rename PK to oldPK, and convert to number
3) rename FK (in many table) to OldFK
4) add new PK (autonumber) to main table

5) create a query linked on the old PK/FK
6) convert to an Update query and set the new FK to the new PK

I hope you do not use the autonumber on any documents if so then concider converting the PK to a number and use a function (see forum) to increment each time a record is added

 

[kjcmas]

thanks dennisk. i'll give that a go. I was afraid that something like that would be my only option. i wasn't really sure of the best way to do it. A query sounds good though. it'll definately work. i was just dreading having to go that route because i have so many tables. how do other ACCESSers avoid this? or maybe they don't?

 

[kjcmas]

dennisk, thanks for the advice. i wasn't looking forward to the exercise, but it wasn't that bad. once i got i the groove, the conversions went quickly. i'd say almost 2 hours. not bad all things considered. I think it was good to do anyway. Now all I need to do is recover the VBA code from the corrupt db. I had backups of most of it. I just didn't get a chance to backup before the crash and burn. any advice on that?

 

[Dennisk]

KJCMAS,
I've never has to recover code, however have you tried opening your db in word. much is displayed as non printable characters but you can still see plain text and you might spot your code infact you could search for sub or function and see what turns up.
good luck