Changing field names in a table through VBA


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Changing field names in a table through VBA

  1. #1
    Jeff Guest

    Changing field names in a table through VBA


    I have an Access 2000 table that I am trying to change the fieldnames through
    VBA code. I import a text file then run some queries on it. I then need to
    change some of the field names before I export it to a new database to be
    emailed out. Can anyone help me as to how I change the field names through
    VBA code.


  2. #2
    Arthur Wood Guest

    Re: Changing field names in a table through VBA


    Jeff,

    I may be totally wrong, and I am sure that another "expert" will jump all
    over me if I am, but I don't think there is any way to change the names of
    fields in a Table. The closest you can come is to rename fields in a Query
    as :

    Select Field1 as Name, Field2 as Address from Table"

    which then produces a recordset with the Field Names in the REecord appearing
    as "Name" and "Address", but that DOES NOT change, in any way, the underlying
    Table.

    the SQL-DDL statement "ALTER TABLE" only allows you to ADD/DROP Fields
    or ADD/DROP INDEXES and ADD/DROP Constraints.

    Arthur Wood


    "Jeff" <Jgreen@tmgsolutionsinc.com> wrote:
    >
    >I have an Access 2000 table that I am trying to change the fieldnames through
    >VBA code. I import a text file then run some queries on it. I then need

    to
    >change some of the field names before I export it to a new database to be
    >emailed out. Can anyone help me as to how I change the field names through
    >VBA code.
    >



  3. #3
    Jeff Guest

    Re: Changing field names in a table through VBA


    I thought about that way. I was hoping there was an way to change just a single
    field name without having to create a query to it with all the other fields
    in the table because using a make table query will slow down the process.
    Thanks for your help I appreciate it.

    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Jeff,
    >
    > I may be totally wrong, and I am sure that another "expert" will jump

    all
    >over me if I am, but I don't think there is any way to change the names

    of
    >fields in a Table. The closest you can come is to rename fields in a Query
    >as :
    >
    > Select Field1 as Name, Field2 as Address from Table"
    >
    >which then produces a recordset with the Field Names in the REecord appearing
    >as "Name" and "Address", but that DOES NOT change, in any way, the underlying
    >Table.
    >
    >the SQL-DDL statement "ALTER TABLE" only allows you to ADD/DROP Fields
    >or ADD/DROP INDEXES and ADD/DROP Constraints.
    >
    >Arthur Wood
    >
    >
    >"Jeff" <Jgreen@tmgsolutionsinc.com> wrote:
    >>
    >>I have an Access 2000 table that I am trying to change the fieldnames through
    >>VBA code. I import a text file then run some queries on it. I then need

    >to
    >>change some of the field names before I export it to a new database to

    be
    >>emailed out. Can anyone help me as to how I change the field names through
    >>VBA code.
    >>

    >



  4. #4
    Paul Clement Guest

    Re: Changing field names in a table through VBA

    On 29 Mar 2001 11:37:22 -0800, "Jeff" <Jgreen@tmgsolutionsinc.com> wrote:


    I thought about that way. I was hoping there was an way to change just a single
    field name without having to create a query to it with all the other fields
    in the table because using a make table query will slow down the process.
    Thanks for your help I appreciate it.


    The only other way is to define a schema.ini file that identifies the fields in your text file or
    specify the field names in the first row of the text file.

    http://msdn.microsoft.com/library/ps...k/odbc8t45.htm

    I don't think is has ever been possible to change the field name. You generally need to drop the
    field (and lose the data) and then re-add it.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  5. #5
    Douglas J. Steele Guest

    Re: Changing field names in a table through VBA

    "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
    newsp67ct0fi35s24p14eof8h00u4o7uo75ct@4ax.com...
    > On 29 Mar 2001 11:37:22 -0800, "Jeff" <Jgreen@tmgsolutionsinc.com> wrote:
    >
    >
    > I thought about that way. I was hoping there was an way to change just a

    single
    > field name without having to create a query to it with all the other

    fields
    > in the table because using a make table query will slow down the

    process.
    > Thanks for your help I appreciate it.
    >
    >
    > The only other way is to define a schema.ini file that identifies the

    fields in your text file or
    > specify the field names in the first row of the text file.
    >
    > http://msdn.microsoft.com/library/ps...k/odbc8t45.htm
    >
    > I don't think is has ever been possible to change the field name. You

    generally need to drop the
    > field (and lose the data) and then re-add it.


    Sorry to be argumentative, but it's easy to rename fields in a table using
    DAO:

    CurrentDB().TableDefs(TableName).Fields(CurrentFieldName).Name =
    NewFieldName

    You can't change field characteristics (datatype, length, etc.): for that
    you need to rename the existing field, add the new field with the correct
    characteristics, run an update query to transfer the data from the old field
    to the new field, then drop the old field. (No need to lose the data!) Don't
    forget to compact once you're done.

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/






  6. #6
    Craig Brown Guest

    Re: Changing field names in a table through VBA


    Just a thought here, I'm not sure whether or not one can or should do it,
    but, what about creating a view with the desired names?

    Would someone, please, validate or refute this idea. (I'd hate to make the
    same mistake again. On the other hand, if it can work, why not?)

    Craig Brown


    "Douglas J. Steele" <djsteele@canada.com> wrote:
    >"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
    >newsp67ct0fi35s24p14eof8h00u4o7uo75ct@4ax.com...
    >> On 29 Mar 2001 11:37:22 -0800, "Jeff" <Jgreen@tmgsolutionsinc.com> wrote:
    >>
    >>
    >> I thought about that way. I was hoping there was an way to change just

    a
    >single
    >> field name without having to create a query to it with all the other

    >fields
    >> in the table because using a make table query will slow down the

    >process.
    >> Thanks for your help I appreciate it.
    >>
    >>
    >> The only other way is to define a schema.ini file that identifies the

    >fields in your text file or
    >> specify the field names in the first row of the text file.
    >>
    >> http://msdn.microsoft.com/library/ps...k/odbc8t45.htm
    >>
    >> I don't think is has ever been possible to change the field name. You

    >generally need to drop the
    >> field (and lose the data) and then re-add it.

    >
    >Sorry to be argumentative, but it's easy to rename fields in a table using
    >DAO:
    >
    >CurrentDB().TableDefs(TableName).Fields(CurrentFieldName).Name =
    >NewFieldName
    >
    >You can't change field characteristics (datatype, length, etc.): for that
    >you need to rename the existing field, add the new field with the correct
    >characteristics, run an update query to transfer the data from the old field
    >to the new field, then drop the old field. (No need to lose the data!) Don't
    >forget to compact once you're done.
    >
    >--
    >
    >Doug Steele, Microsoft Access MVP
    >Beer, Wine and Database Programming. What could be better?
    >Visit "Doug Steele's Beer and Programming Emporium"
    >http://I.Am/DougSteele/
    >
    >
    >
    >
    >



  7. #7
    Paul Clement Guest

    Re: Changing field names in a table through VBA

    On Thu, 29 Mar 2001 20:33:17 -0500, "Douglas J. Steele" <djsteele@canada.com> wrote:

    "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
    newsp67ct0fi35s24p14eof8h00u4o7uo75ct@4ax.com...
    > On 29 Mar 2001 11:37:22 -0800, "Jeff" <Jgreen@tmgsolutionsinc.com> wrote:
    >
    >
    > I thought about that way. I was hoping there was an way to change just a
    single
    > field name without having to create a query to it with all the other
    fields
    > in the table because using a make table query will slow down the
    process.
    > Thanks for your help I appreciate it.
    >
    >
    > The only other way is to define a schema.ini file that identifies the
    fields in your text file or
    > specify the field names in the first row of the text file.
    >
    > http://msdn.microsoft.com/library/ps...k/odbc8t45.htm
    >
    > I don't think is has ever been possible to change the field name. You
    generally need to drop the
    > field (and lose the data) and then re-add it.

    Sorry to be argumentative, but it's easy to rename fields in a table using
    DAO:

    CurrentDB().TableDefs(TableName).Fields(CurrentFieldName).Name =
    NewFieldName

    You can't change field characteristics (datatype, length, etc.): for that
    you need to rename the existing field, add the new field with the correct
    characteristics, run an update query to transfer the data from the old field
    to the new field, then drop the old field. (No need to lose the data!) Don't
    forget to compact once you're done.

    Hi Doug,

    Nah you're not being argumentative. I just got my facts my confused.

    Thanks for posting the correction.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  8. #8
    Douglas J. Steele Guest

    Re: Changing field names in a table through VBA

    Well, the original post talked about Access 2000, which doesn't have views.
    However, you could create a query that renamed the fields (the same as a
    view could in, say, SQL Server)

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/


    "Craig Brown" <brownc3@ing-afs.com> wrote in message
    news:3ac47b13$1@news.devx.com...
    >
    > Just a thought here, I'm not sure whether or not one can or should do it,
    > but, what about creating a view with the desired names?
    >
    > Would someone, please, validate or refute this idea. (I'd hate to make

    the
    > same mistake again. On the other hand, if it can work, why not?)
    >
    > Craig Brown
    >
    >
    > "Douglas J. Steele" <djsteele@canada.com> wrote:
    > >"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in

    message
    > >newsp67ct0fi35s24p14eof8h00u4o7uo75ct@4ax.com...
    > >> On 29 Mar 2001 11:37:22 -0800, "Jeff" <Jgreen@tmgsolutionsinc.com>

    wrote:
    > >>
    > >>
    > >> I thought about that way. I was hoping there was an way to change

    just
    > a
    > >single
    > >> field name without having to create a query to it with all the other

    > >fields
    > >> in the table because using a make table query will slow down the

    > >process.
    > >> Thanks for your help I appreciate it.
    > >>
    > >>
    > >> The only other way is to define a schema.ini file that identifies the

    > >fields in your text file or
    > >> specify the field names in the first row of the text file.
    > >>
    > >> http://msdn.microsoft.com/library/ps...k/odbc8t45.htm
    > >>
    > >> I don't think is has ever been possible to change the field name. You

    > >generally need to drop the
    > >> field (and lose the data) and then re-add it.





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center