Can I use DoCmd.Rename to change the field names in a table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have imported a file that has no field names. The file is going to be
imported every month so I want to automate it so that the field names will be
changed with a rename function or something else that may work where they
won't have to be changed manually.
 
I think you're best off using DAO.

Something like:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDB()
Set tdfCurr = dbCurr.TableDefs("MyTableName")
tdfCurr.Fields("Field1").Name = "NewName1"
tdfCurr.Fields("Field2").Name = "NewName2"
tdfCurr.Fields("Field3").Name = "NewName3"
dbCurr.TableDefs.Refresh

Set tdfCurr = Nothing
Set dbCurr = Nothing
 
If the file you are importing is a text file, you can set up a TransferText
with a Specification name to import it with the field names you need.

To do this, go through the import manually one time. When you get the
Import Dialog box, click on advanced. You can then name your fields, choose
the appropriate data types, etc. Once that is done, click on Save As and
give it a name. This is the name you will use as the Specification Name
argument in the TransferText. It is the second argument. You can get more
detail in VB Help on TransferText.

This will work if the file is a text file and it will come in in the same
format every month.
 
Douglas,
Again thank you. I used this and it worked perfectly. I now have a
similar problem and I am enclosing the code because I am stuck. One of those
fields is being imported as a number and I want it to be text. This is the
code that I have written but I get the error message " wrong number of
arguments or invalid property assigned".

Dim dbCurr As DAO.Database
Dim tdefCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim typCurr As DAO.DataTypeEnum

Set dbCurr = CurrentDb()
Set tdefCurr = dbCurr.TableDefs("New_Billing_Temp")
Set fldCurr = tdefCurr.Fields("Mobile Number")
Set typCurr = fldCurr.Type(adChar)

dbCurr.TableDefs.Refresh

Set tdefCurr = Nothing
Set dbCurr = Nothing

Thank you in advance.
 
In DAO, you can't change field types once the field has been added to the
table.
 
So, do you have any other suggestions on how to go about doing this?? I had
thought about "alter table" but I have never used that before.
 
"Alter Table" is certainly one approach. Something like:

CurrentDb.Execute "ALTER TABLE New_Billing_Temp " & _
"ALTER COLUMN Mobile Number TEXT(25)", dbFailOnError

(replace 25 with how ever many characters you want the field to be able to
hold, to a maximum of 255)

The other alternative is to add a new text field, run an Update query to
populate the new field from the old field, delete the old field, then rename
the new field.

Either way, you'll want to Compact the database afterwards.
 
This was extremely helpful to me in a project I'm working on. I was able to
substitute the table/field names and use as is. I have one additional
question:

I need to change field names of 12 fields total. When I run this module, it
changes the first two and then tells me it "cannot define field more than
once". Each of the original field names are distinct (i.e. Expr1, Expr2,
Expr3). Any idea why I encounter this problem?
 
Sorry for the delay. The code is as follows:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("GRLDaily-2")
tdfCurr.Fields("1").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0)), "mm-yy")
tdfCurr.Fields("2").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 30), "mm-yy")
tdfCurr.Fields("3").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 60), "mm-yy")
tdfCurr.Fields("4").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 90), "mm-yy")
tdfCurr.Fields("5").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 120), "mm-yy")
tdfCurr.Fields("6").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 150), "mm-yy")
tdfCurr.Fields("7").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 180), "mm-yy")
tdfCurr.Fields("8").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 210), "mm-yy")
tdfCurr.Fields("9").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 240), "mm-yy")
tdfCurr.Fields("10").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 270), "mm-yy")
tdfCurr.Fields("11").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 300), "mm-yy")
tdfCurr.Fields("12").NAME = "Delq_" & Format((DateSerial(Year(Date),
Month(Date), 0) - 330), "mm-yy")
dbCurr.TableDefs.Refresh

Set tdfCurr = Nothing
Set dbCurr = Nothing

End Sub
 
PLEASE consider redesigning your table so that it's normalized! Having
fields named like that is a violation of database normalization principles.

The probable cause of your errors is that you're getting two dates that
equate to the same value of mm-yy (1st and 31st of the same month). However,
your design is just plain WRONG!

Jeff Conrad has some good resources on this topic at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
 
Thank you for helping me figure this out, I really and TRULY appreciate it.
I will also review the information in the resource you recommended.

I will say, however, that I am not a Db designer by education and I am
having to try to learn this on my own due to the evolution of my role at my
employer. I generally research all of my own issues and resolutions, only
conferring with other users when I feel I have no other choice so as not to
waste the time of others more experienced than myself. Perhaps I'm taking
your response the wrong way, but I found it extremely offensive. You should
keep in mind that some do not have the education or technical skill that you
may have and view such instances as an opportunity to truly help someone
rather than belittle them or make them ,frankly, feel stupid.

Additionally, the script I sent you was for a test table I created to study
the effect my coding would have on these particular fields. This is not an
actual table utilized in production. Believe it or not, I am by no means
that big of an idiot.
 
Sorry you felt insulted. Questions regarding repeating groups in tables,
like you have in your sample, occur extremely often in these groups, and
sometimes I (and the other MVPs) get frustrated by answering the same
questions so often.

Having said that, though, I would have thought that a basic piece of
debugging would have been to check what values would have been generated as
the field names, since the message "cannot define a field more than once"
does imply duplication.

Cobbling together a little routine like:

Sub TestRoutine()
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0)),
"mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
30), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
60), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
90), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
120), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
150), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
180), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
210), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
240), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
270), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
300), "mm-yy")
Debug.Print "Delq_" & Format((DateSerial(Year(Date), Month(Date), 0) -
330), "mm-yy")
End Sub

and running it from the immediate window would have shown the problem in a
few seconds:

TestRoutine
Delq_04-06
Delq_03-06
Delq_03-06
Delq_01-06
Delq_12-05
Delq_12-05
Delq_11-05
Delq_10-05
Delq_09-05
Delq_08-05
Delq_07-05
Delq_06-05
 
That's a process I've read some about, but didn't quite understand. Your
explanation was actually very clarifying and I appreciate it. I'll take your
advise into serious consideration in the future.

Thanks and have a good Memorial Weekend.
 
Back
Top