Change Field Name in Table

[GrandMaMa]

We have a new software package from a vendor where we must change the Field
Name in a Table.

Two days ago we submitted this and never got the problem resolved. Because
of the field names (Example is HMDA - Interest (numeric)) we cannot use a
query to recreate the new table. The Query will take the above field name
and convert it to (HMDA-Interest(numeric)). This seems to be a problem with
Access 2002 and 2004 that we did not have with Access 2000.

The software firm is shocked that we even were able to delete the original
table because they used assembler to secure it.

Here is the code at this minute!

Dim TableName As String
Dim db As DAO.Database
Dim rst As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim cnt As Byte

Set db = CurrentDb

With db
For Each tdf In .TableDefs
If tdf.Name = "AlphaErrors" Then
TableName = Left(tdf.Name, 11)
db.TableDefs!TableName.Fields![NoFive].Name =
"NoSix" ' Here is the Error
MsgBox "Found Error Table"
End If

Next tdf
End With

The error is Item not found in this collection. Do not know if I have a
Microsoft problem or if my syntax is in-correct.

Thanks Again in Advance

Granny

 

[Marshall Barton]

We have a new software package from a vendor where we must change the Field
Name in a Table.

Two days ago we submitted this and never got the problem resolved. Because
of the field names (Example is HMDA - Interest (numeric)) we cannot use a
query to recreate the new table. The Query will take the above field name
and convert it to (HMDA-Interest(numeric)). This seems to be a problem with
Access 2002 and 2004 that we did not have with Access 2000.

The software firm is shocked that we even were able to delete the original
table because they used assembler to secure it.

Here is the code at this minute!

Dim TableName As String
Dim db As DAO.Database
Dim rst As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim cnt As Byte

Set db = CurrentDb

With db
For Each tdf In .TableDefs
If tdf.Name = "AlphaErrors" Then
TableName = Left(tdf.Name, 11)
db.TableDefs!TableName.Fields![NoFive].Name =
"NoSix" ' Here is the Error []
The error is Item not found in this collection. Do not know if I have a
Microsoft problem or if my syntax is in-correct.

[]

In this case, you are using the variable TableName and not
the name of the table so, yes, it is a syntax issue. In my
earlier post, is used tablename as a placeholder for the
real table name (which I did not know then) and fogot to
explain that you should replace it with the real name.

To use a variable with the table name as its value (as you
tried above), you need drop the bang and enclose it in
parenthesis:

db.TableDefs(TableName).Fields![NoFive].Name = "NoSix"

OTOH, since you know the name of the table, you can use it
directly:

db.TableDefs!AlphaErrors.Fields![NoFive].Name = "NoSix"

Either way should work.

BTW, there is no need for the For Each loop unless there is
a chance that the table might not exist.

 

[Douglas J. Steele]

Marsh seems to be providing you with appropriate answers to your questions.

I just wanted to point out that your declarations are a little "dangerous".

You've used the DAO keyword to qualify the declaration for db, which
technically isn't required, but you haven't qualified the declarations for
Recordset and Field, where it likely is required.

The reason for qualifying the declarations if you have references set to
both ADO and DAO, there are objects with names in common between the two
libraries. The list of objects with the same names in the 2 models is
Connection, Error, Errors, Field, Fields, Parameter, Parameters, Property,
Properties and Recordset

 

[GrandMaMa]

Marshall;

Your method might work but when I try it, it gives me a Table is
locked message.

We have tried all of the procedures we could think of. The
problem is also what they named the fields. The parentheses, dashes and
spaces really create a problem. No query will accept them. I tried creating
a file in SQL and moving data to that table field by field, but one field
name held that idea up.

I am not a half-bad Access programmer, but this one has me
going batty.

Thanks Again

Granny

We have a new software package from a vendor where we must change the Field
Name in a Table.

Two days ago we submitted this and never got the problem resolved. Because
of the field names (Example is HMDA - Interest (numeric)) we cannot use a
query to recreate the new table. The Query will take the above field name
and convert it to (HMDA-Interest(numeric)). This seems to be a problem with
Access 2002 and 2004 that we did not have with Access 2000.

The software firm is shocked that we even were able to delete the original
table because they used assembler to secure it.

Here is the code at this minute!

Dim TableName As String
Dim db As DAO.Database
Dim rst As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim cnt As Byte

Set db = CurrentDb

With db
For Each tdf In .TableDefs
If tdf.Name = "AlphaErrors" Then
TableName = Left(tdf.Name, 11)
db.TableDefs!TableName.Fields![NoFive].Name =
"NoSix" ' Here is the Error []
The error is Item not found in this collection. Do not know if I have a
Microsoft problem or if my syntax is in-correct.

[]

In this case, you are using the variable TableName and not
the name of the table so, yes, it is a syntax issue. In my
earlier post, is used tablename as a placeholder for the
real table name (which I did not know then) and fogot to
explain that you should replace it with the real name.

To use a variable with the table name as its value (as you
tried above), you need drop the bang and enclose it in
parenthesis:

db.TableDefs(TableName).Fields![NoFive].Name = "NoSix"

OTOH, since you know the name of the table, you can use it
directly:

db.TableDefs!AlphaErrors.Fields![NoFive].Name = "NoSix"

Either way should work.

BTW, there is no need for the For Each loop unless there is
a chance that the table might not exist.

 

[Douglas J. Steele]

Why do you say "no query will accept them"? Have you tried enclosing the
names in square brackets?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Marshall;

Your method might work but when I try it, it gives me a Table
is
locked message.

We have tried all of the procedures we could think of. The
problem is also what they named the fields. The parentheses, dashes and
spaces really create a problem. No query will accept them. I tried
creating
a file in SQL and moving data to that table field by field, but one field
name held that idea up.

I am not a half-bad Access programmer, but this one has me
going batty.

Thanks Again

Granny

We have a new software package from a vendor where we must change the
Field
Name in a Table.

Two days ago we submitted this and never got the problem resolved.
Because
of the field names (Example is HMDA - Interest (numeric)) we cannot use
a
query to recreate the new table. The Query will take the above field
name
and convert it to (HMDA-Interest(numeric)). This seems to be a problem
with
Access 2002 and 2004 that we did not have with Access 2000.

The software firm is shocked that we even were able to delete the
original
table because they used assembler to secure it.

Here is the code at this minute!

Dim TableName As String
Dim db As DAO.Database
Dim rst As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim cnt As Byte

Set db = CurrentDb

With db
For Each tdf In .TableDefs
If tdf.Name = "AlphaErrors" Then
TableName = Left(tdf.Name, 11)
db.TableDefs!TableName.Fields![NoFive].Name =
"NoSix" ' Here is the Error []
The error is Item not found in this collection. Do not know if I have a
Microsoft problem or if my syntax is in-correct.

[]

In this case, you are using the variable TableName and not
the name of the table so, yes, it is a syntax issue. In my
earlier post, is used tablename as a placeholder for the
real table name (which I did not know then) and fogot to
explain that you should replace it with the real name.

To use a variable with the table name as its value (as you
tried above), you need drop the bang and enclose it in
parenthesis:

db.TableDefs(TableName).Fields![NoFive].Name = "NoSix"

OTOH, since you know the name of the table, you can use it
directly:

db.TableDefs!AlphaErrors.Fields![NoFive].Name = "NoSix"

Either way should work.

BTW, there is no need for the For Each loop unless there is
a chance that the table might not exist.

 

[Len]

Sorry I did not inform you that I changed the declarations some time ago at
your recomendation.

They are no:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Still do not know how to unlock the table however.

Thanks Again

Granny

Marsh seems to be providing you with appropriate answers to your questions.

I just wanted to point out that your declarations are a little "dangerous".

You've used the DAO keyword to qualify the declaration for db, which
technically isn't required, but you haven't qualified the declarations for
Recordset and Field, where it likely is required.

The reason for qualifying the declarations if you have references set to
both ADO and DAO, there are objects with names in common between the two
libraries. The list of objects with the same names in the 2 models is
Connection, Error, Errors, Field, Fields, Parameter, Parameters, Property,
Properties and Recordset


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

We have a new software package from a vendor where we must change the
Field
Name in a Table.

Two days ago we submitted this and never got the problem resolved.
Because
of the field names (Example is HMDA - Interest (numeric)) we cannot use a
query to recreate the new table. The Query will take the above field name
and convert it to (HMDA-Interest(numeric)). This seems to be a problem
with
Access 2002 and 2004 that we did not have with Access 2000.

The software firm is shocked that we even were able to delete the original
table because they used assembler to secure it.

Here is the code at this minute!

Dim TableName As String
Dim db As DAO.Database
Dim rst As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim cnt As Byte

Set db = CurrentDb

With db
For Each tdf In .TableDefs
If tdf.Name = "AlphaErrors" Then
TableName = Left(tdf.Name, 11)
db.TableDefs!TableName.Fields![NoFive].Name =
"NoSix" ' Here is the Error
MsgBox "Found Error Table"
End If

Next tdf
End With

The error is Item not found in this collection. Do not know if I have a
Microsoft problem or if my syntax is in-correct.

Thanks Again in Advance

Granny

 

[Marshall Barton]

If the table is protected from your changes, I think copying
the data to a new table may be a viable workaround. The
irritating field name can be changed in the Make Table
query.

SELECT thisfield,
thatfield,
otherfield,
[HMDA - Interest] As LoanNo,
. . .
INTO newtable
FROM AlphaErrors

If that has trouble getting the field types right in the new
table, you can use table design to create a new table with
all the field names, types, indexes, etc the way you want
them. Then use an Append query to copy the data.
--
Marsh
MVP [MS Access]

Your method might work but when I try it, it gives me a Table is
locked message.

We have tried all of the procedures we could think of. The
problem is also what they named the fields. The parentheses, dashes and
spaces really create a problem. No query will accept them. I tried creating
a file in SQL and moving data to that table field by field, but one field
name held that idea up.

I am not a half-bad Access programmer, but this one has me
going batty.

We have a new software package from a vendor where we must change the Field
Name in a Table.

Two days ago we submitted this and never got the problem resolved. Because
of the field names (Example is HMDA - Interest (numeric)) we cannot use a
query to recreate the new table. The Query will take the above field name
and convert it to (HMDA-Interest(numeric)). This seems to be a problem with
Access 2002 and 2004 that we did not have with Access 2000.

The software firm is shocked that we even were able to delete the original
table because they used assembler to secure it.

Here is the code at this minute!

Dim TableName As String
Dim db As DAO.Database
Dim rst As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim cnt As Byte

Set db = CurrentDb

With db
For Each tdf In .TableDefs
If tdf.Name = "AlphaErrors" Then
TableName = Left(tdf.Name, 11)
db.TableDefs!TableName.Fields![NoFive].Name =
"NoSix" ' Here is the Error []
The error is Item not found in this collection. Do not know if I have a
Microsoft problem or if my syntax is in-correct.

[]

In this case, you are using the variable TableName and not
the name of the table so, yes, it is a syntax issue. In my
earlier post, is used tablename as a placeholder for the
real table name (which I did not know then) and fogot to
explain that you should replace it with the real name.

To use a variable with the table name as its value (as you
tried above), you need drop the bang and enclose it in
parenthesis:

db.TableDefs(TableName).Fields![NoFive].Name = "NoSix"

OTOH, since you know the name of the table, you can use it
directly:

db.TableDefs!AlphaErrors.Fields![NoFive].Name = "NoSix"

Either way should work.

BTW, there is no need for the For Each loop unless there is
a chance that the table might not exist.