Help! Column accidentally deleted

[Gijs van Swaaij]

When I tried to insert a new column in one of the tables in my Design
Master, I accidentally overwrote another field. Of course, it asked me
whether I wanted to keep this change, I clicked "Yes", and I
synchronised this version of the database with our main database. I
soon found out, that the field was gone. No panic, because all the data
has been properly backed up. However, I tried to recreate a column with
the same name in that table (remember, the old one is definitely gone),
but Access tells me something that translates to "you can only define a
field once". Compact & Repair didn't help. How can I solve this? I
still need to be able to synchronise the database! Giving the field
another name isn't really an option, because that would require going
through the entire frontend to change that name, which would be a pain.
All help would be greatly appreciated.

Sincerely,
Gijs van Swaaij

 

[Gijs van Swaaij]

Thanks for your response! I tried this, and I noticed some *very*
strange behaviour. It allows me to insert the field abcd in the table,
but then it doesn't display the field in any of the GUI features
(forms, GUI query creator, etc.). However, if I run "SELECT abcd FROM
tblName;", it actually does work, and displays a list of empty values!
Furthermore, if I run "SELECT numDecs FROM tblName" (numDecs is the
column that I thought I had deleted), then it gives me all the data!
Additionally, when I run "SELECT * FROM tblName", it also gives the
columns numDecs and abcd.

So Access hadn't actually deleted this column as I thought, but instead
it made the column 'hidden' for the GUI's. The other tables in the same
database don't have this behaviour. Have you encountered this problem
before, and do you know how to solve it?

Sincerely,
Gijs van Swaaij

 

[Gijs van Swaaij]

Thank you for your help, the problem seems to have solved itself in a
way... I found that apparently, there cannot be more than two hidden
columns, and if I just keep defining new columns with random column
names, then the new ones will become hidden and the old ones show up!
So now I have two useless, hidden columns in my table that do nothing
but eat space. But at least it works, and it's not a big table anyway.
Sorry if I wasted your time on this strange bug.

Sincerely,
Gijs van Swaaij


Wayne-I-M schreef:

 

[John Vinson]

Thank you for your help, the problem seems to have solved itself in a
way... I found that apparently, there cannot be more than two hidden
columns, and if I just keep defining new columns with random column
names, then the new ones will become hidden and the old ones show up!
So now I have two useless, hidden columns in my table that do nothing
but eat space. But at least it works, and it's not a big table anyway.
Sorry if I wasted your time on this strange bug.

I'd suggest that you TURN OFF the very buggy "Name Autocorrect" (aka
Name Autocorrupt) misfeature. Open the database, and use Tools...
Options... General; uncheck the "Track Name Autocorrect" checkbox.
Compact and repair the database to remove the tracking information,
and see if these ghost columns go away.

John W. Vinson[MVP]