changing field names

[Michael]

Hi everyone,

I am trying to change the field names for a table that is being
exported via Excel. Its a spreadsheet that our National Office sends
us but even after promise after promise they keep changing the field
names which makes it nearly impossible to run any pre-generated
queries etc.

The solution really is to get someone to check the field names before
importing the spreadsheet but I want to do it in code. I have written
a utility to display the table fields on a form and using a series of
combo boxes allows the users to select the correct names for the
fields. On a button on the form I detect the fields that are going to
change and send the values to two global variables (oldList and
newList) which are two lists telling what the old field name is and
one telling what to change it to. I then close the form (because I
don't want any objects referencing the originating table to be open)
and open a new dummy form with the following code on the onload event
handler:
Set td = db.TableDefs("geocodeInput")
For i = 0 To UBound(oldListArr)
td.Fields(oldListArr(i)).NAME = newListArr(i)
Next i
Set td = Nothing
db.close

I get an error that says the table is already being used by another
user or process but I have checked to make sure all database objects
that use the table are closed.

Any ideas?

[Mike Storr]

On 16 Feb 2004 18:51:26 -0800, Michael wrote:

Hi everyone,

I am trying to change the field names for a table that is being
exported via Excel. Its a spreadsheet that our National Office sends
us but even after promise after promise they keep changing the field
names which makes it nearly impossible to run any pre-generated
queries etc.

The solution really is to get someone to check the field names before
importing the spreadsheet but I want to do it in code. I have written
a utility to display the table fields on a form and using a series of
combo boxes allows the users to select the correct names for the
fields. On a button on the form I detect the fields that are going to
change and send the values to two global variables (oldList and
newList) which are two lists telling what the old field name is and
one telling what to change it to. I then close the form (because I
don't want any objects referencing the originating table to be open)
and open a new dummy form with the following code on the onload event
handler:
Set td = db.TableDefs("geocodeInput")
For i = 0 To UBound(oldListArr)
td.Fields(oldListArr(i)).NAME = newListArr(i)
Next i
Set td = Nothing
db.close

I get an error that says the table is already being used by another
user or process but I have checked to make sure all database objects
that use the table are closed.

Any ideas?

Are any relationships defined using this field?
--
Mike Storr
www.veraccess.com

[Michael West]

Thanks for your quick reply.
The table isn't involved in any relationships.

The problem seems to be stemming from the fact that I execute the code
from a form that has the source table as a recordsource even though I
have closed the form before running it. I solved the problem by passing
the user to a new form with a "click this button to complete" message on
it , where the code executes no problem.

This is really clunky though but seemingly necessary.

:)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mike Storr]

If the execution of the new code comes from a form, then the form can't be
closed before that new code runs (unless the new code closes the form). For
instance if a button click event you do this

Docmd.Close .....
Set td = .....

Then the code must finish running before the form can actually destroy
itself. Even if you branch out to external sub or function from the button
event, execution must return to the form to be completed. You might try in
this external function, making the form invisible, changing it's
recordsource to something else (like "" to make it unbound), then perform
your actions on the TableDef. After changing the recordsource, the form
could be closed at any point.

Mike Storr
www.veraccess.com
"Michael West" <we****@hotmail.com> wrote in message
news:40*********************@news.frii.net...

Thanks for your quick reply.
The table isn't involved in any relationships.

The problem seems to be stemming from the fact that I execute the code
from a form that has the source table as a recordsource even though I
have closed the form before running it. I solved the problem by passing
the user to a new form with a "click this button to complete" message on
it , where the code executes no problem.

This is really clunky though but seemingly necessary.