Re: Change Field Name in Table

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

"Marshall Barton" wrote:

GrandMaMa wrote:

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.

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: Change Field Name in Table
    ... Why do you say "no query will accept them"? ... Doug Steele, Microsoft Access MVP ... Dim TableName As String ...
    (microsoft.public.access.formscoding)
  • RE: Parse Nested Elements to Single DataGrid
    ... 'Dim MasterTable As New DataTable ... Dim TableName, TableNameInner As String ... 'Save the next and last row values for this parent ... 'Get LoopRowNum for table where TableName is the key ...
    (microsoft.public.dotnet.xml)
  • Re: Datenbank dynamisch anlegen/erweitern
    ... Datenbank die Sub "DBtest" auf, ... Dim cat As New ADOX.Catalog ... If Not FileExistsThen ... TableName As String) ...
    (microsoft.public.de.vb.datenbank)
  • Re: Change Field Name in Table
    ... The Query will take the above field name ... Dim TableName As String ... you are using the variable TableName and not ...
    (microsoft.public.access.formscoding)
  • Datenbank dynamisch anlegen/erweitern
    ... Ich erstelle mit unten stehenden Code eine Datenbank per ADOX und lege dort beispielsweise 2 Tabellen mit je 2 Feldern an. ... Dim cat As New ADOX.Catalog ... TableName As String) ... Dim Exists As Boolean ...
    (microsoft.public.de.vb.datenbank)
Loading