By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

Change column name?

JustJim
Expert 100+
P: 407
Hi

If all you can do with ALTER TABLE....ALTER COLUMN is change the datatype/size then how do I change the name of a field?

I have several database files spread all over the state and some of the users (Darn users!) have changed a field from "Datum" to "MapDatum" and I need to change it back because it is causing problems with the front end.

I suspect that I'm going to have to determine if the field MapDatum exists and if it does, make a new column, copy all the data over and drop the old column.

Is there any way to just change the name in code? Otherwise it's probably going to be quicker (but nowhere near as elegant) to open each database, open the table in design view and change it there!

Jim
Jan 31 '08 #1
Share this Question
Share on Google+
7 Replies


Jim Doherty
Expert 100+
P: 897
Hi

If all you can do with ALTER TABLE....ALTER COLUMN is change the datatype/size then how do I change the name of a field?

I have several database files spread all over the state and some of the users (Darn users!) have changed a field from "Datum" to "MapDatum" and I need to change it back because it is causing problems with the front end.

I suspect that I'm going to have to determine if the field MapDatum exists and if it does, make a new column, copy all the data over and drop the old column.

Is there any way to just change the name in code? Otherwise it's probably going to be quicker (but nowhere near as elegant) to open each database, open the table in design view and change it there!

Jim
This will loop 'all' tables seeking the offending field and change it for the 'current' database Jim..... but I'm a little confused as to the 'all over the state' bit you speak about where is the table located obviously this code below does not target external databases in any logical sweep merely the current one?

Expand|Select|Wrap|Line Numbers
  1.  Dim db As DAO.Database 
  2. Dim tbl As DAO.TableDef
  3. Dim fld As DAO.Field
  4. Set db = CurrentDb()
  5. For Each tbl In db.TableDefs
  6. For Each fld In tbl.Fields
  7.      If fld.Name = "MapDatum" Then fld.Name = "Datum"
  8. Next
  9. Next
  10.  
Regards

Jim :)
Feb 1 '08 #2

JustJim
Expert 100+
P: 407
This will loop 'all' tables seeking the offending field and change it for the 'current' database Jim..... but I'm a little confused as to the 'all over the state' bit you speak about where is the table located obviously this code below does not target external databases in any logical sweep merely the current one?

Expand|Select|Wrap|Line Numbers
  1.  Dim db As DAO.Database 
  2. Dim tbl As DAO.TableDef
  3. Dim fld As DAO.Field
  4. Set db = CurrentDb()
  5. For Each tbl In db.TableDefs
  6. For Each fld In tbl.Fields
  7.      If fld.Name = "MapDatum" Then fld.Name = "Datum"
  8. Next
  9. Next
  10.  
Regards

Jim :)
That will do nicely thanks Jim. "All over the state" means just that, on computers all over the state of Victoria, Australia. It's a front end/back end setup where the back ends are located on each and every users C:\ drive!

Please, don't look at me like that, I didn't design it.

The back ends do get sent in to the head office periodically and this is coming up soon so I'm writing a little fixer-upper code for the poor woman who administers this mess.

Thanks for the code, I was actually getting pretty close. I can cut down one of the loops because I know what table I'm going to look at.

Jim
Feb 1 '08 #3

Jim Doherty
Expert 100+
P: 897
That will do nicely thanks Jim. "All over the state" means just that, on computers all over the state of Victoria, Australia. It's a front end/back end setup where the back ends are located on each and every users C:\ drive!

Please, don't look at me like that, I didn't design it.

The back ends do get sent in to the head office periodically and this is coming up soon so I'm writing a little fixer-upper code for the poor woman who administers this mess.

Thanks for the code, I was actually getting pretty close. I can cut down one of the loops because I know what table I'm going to look at.

Jim
Fine..merely interested kinda knew you'd have no problem overall

Jim :)
Feb 1 '08 #4

JustJim
Expert 100+
P: 407
Fine..merely interested kinda knew you'd have no problem overall

Jim :)
All installed, debugged and delivered. I get paid next Thursday and I hope it's the last I see of that application.

How come I never get the jobs where you get to design an application from scratch? You know, all the 1NF, 2NF, 3NF stuff I learned in uni?

Ah well...

Jim
Feb 1 '08 #5

Jim Doherty
Expert 100+
P: 897
All installed, debugged and delivered. I get paid next Thursday and I hope it's the last I see of that application.

How come I never get the jobs where you get to design an application from scratch? You know, all the 1NF, 2NF, 3NF stuff I learned in uni?

Ah well...

Jim
Hahaha yes seems a million miles away I guess
Feb 1 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
I get paid next Thursday and I hope it's the last I see of that application.
Can you say "Not a chance in the world!" JustJim?

I knew you could!

Linq ;0)>
Feb 1 '08 #7

JustJim
Expert 100+
P: 407
Can you say "Not a chance in the world!" JustJim?

I knew you could!

Linq ;0)>
Not even one tiny little chance in all the world? Sheesh!
Feb 2 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.