Access errors out and creates new column/field

[Ricardo]

Hi, hopefully a quick one.

Having a problem with a table in the database. The specifics are as follows.

-> Open a table in design view.
-> Rename some columns (with data in it) so that we can create a column of
the original name. e.g. - "Certificates January" would become "Certificates
2009" so that we can create a new "Certificates January" for 2010.
-> Upon trying to save the table and quit out of design view it comes up
with an error stating that "Access could not find field TempField0", and then
for some reason a TempField0 or Temp0 has been created in the table.
-> We are then unable to save at all because it keeps asking for the created
field, even though it is present and shouldn't be. This error has only
started happening today.

Any help would be much appreciated.

Regards,

Ricardo

 

[Ricardo]

A little bit more info - using access 2003, with a front end/back end situation

 

[John W. Vinson]

Hi, hopefully a quick one.

Having a problem with a table in the database. The specifics are as follows.

-> Open a table in design view.
-> Rename some columns (with data in it) so that we can create a column of
the original name. e.g. - "Certificates January" would become "Certificates
2009" so that we can create a new "Certificates January" for 2010.
-> Upon trying to save the table and quit out of design view it comes up
with an error stating that "Access could not find field TempField0", and then
for some reason a TempField0 or Temp0 has been created in the table.
-> We are then unable to save at all because it keeps asking for the created
field, even though it is present and shouldn't be. This error has only
started happening today.

The zero-level answer is that you should restore the backup from before you
made these changes; open the database; view Tools... Options from the menu;
and turn OFF "Name Autocorrect".

The real answer is that you're shooting yourself in the foot with your
database design! Storing data (such as a month or a year) in a fieldname is
simply WRONG DESIGN for a relational database; it's "committing spreadsheet"
in the jargon of the folks here. You should instead have a date/time field in
the table and store the date that a certificate is assigned should be stored
in that field, as a date/time field. If you want to see certificates assigned
in January, or 2009, or the 2000-2009 decade, or any other date range, you
would use a Query to select those records!

 

[Ricardo]

Hi, cheers for the reply. And yeah, I know it's committing a grievous error,
but that was just an example and the easiest way to explain the problem.

So that autonaming function is the problem, or could there be a more
convoluted answer?

 

[John W. Vinson]

Hi, cheers for the reply. And yeah, I know it's committing a grievous error,
but that was just an example and the easiest way to explain the problem.

So that autonaming function is the problem, or could there be a more
convoluted answer?

Without knowing more about the database, I can't possibly come up with a
diagnosis of the problem.

I'd try turning off Name Autocorrupt; Compacting the database; and redoing
your fieldname changes.

I have NO idea whether this will solve the problem, or perhaps introduce other
problems (it will make a big difference if the field name that you're changing
is referenced elsewhere in the database, and how). By all means KEEP BACKUPS
before any such change!

The need to do the fieldname changes is on beyond being a grievous error, into
positive maltreatment of your poor innocent database! If you don't watch out,
we'll sic the SPCA (Society for Prevention of Cruelty to Access) on ya! <bg>

 

[Ricardo]

Ok, little bit more information:-

It seems to be happening in the very last steps of the rename/delete cycle.
What I have left is 10 Columns, example below.

Action_Date
Action_Type
Job_No
Status
Status Comment
Action_Date D0101
Action_Type D0101
Job_No D0101
Status D0101
Status Comment D0101

With these columns, I am renaming the first five as "Column_Name D0102" and
removing the "D0101" from the bottom five in the same movement (without
saving in between). So in other words I am switching the columns round with
the exception of the number change. I've found that if I save between the two
steps that the table can be saved but if I don't it produces the error.

Could the problem be that when it's trying to save it is performing it in
steps, trying to rename the "Status Comment D0101" to "Status Comment" while
the original "Status Comment" still exists, before it's had a chance to
change it's name?

If so is there a workaround or way to fix this as it only appears to be
happening in this one table, whereas identical tables do not produce the same
result under the same circumstances.

Cheers,

Ricardo

 

[Ricardo]

Haha, thanks for your advice, I would love to ditch this database and start
again however it's not an option for me.

I'll stick with the workaround I found and see if I can begin to nudge the
higher ups for more creative control.

I'm kind to databases I make...best treatment ever.

Thanks John

Kind Regards,

Ricardo

 

[John W. Vinson]

Could the problem be that when it's trying to save it is performing it in
steps, trying to rename the "Status Comment D0101" to "Status Comment" while
the original "Status Comment" still exists, before it's had a chance to
change it's name?

That's certainly making the job much harder for Access!

If you're swapping names, I'd rename Status Comment D0101 to Status Comment
Temp, then rename Status Comment, then rename Status Comment Temp to Status
Comment, saving the table each time. In fact I'd compact the database each
time.