Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 10th, 2004, 10:19 PM
Authorized User
 
Join Date: Mar 2004
Location: , , USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Renaming fields in access table using VBA

Hi:

I have a situation where I need to rename the fields in an Access table using VBA but I'm having no luck. For example I may want to rename the fields in the table from whatever they are to Field_1, Field_2 and so on.

I would appreciate any help I can get on this one.

Thanks,

Ian
Reply With Quote
  #2 (permalink)  
Old November 10th, 2004, 11:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 184
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jonax
Default

Quote:
quote:
I have a situation where I need to rename the fields in an Access table
Why?!

Whatever your problem is, I'm almost sure you could do something smarter... Do you know UNION?

Reply With Quote
  #3 (permalink)  
Old November 11th, 2004, 05:41 AM
Authorized User
 
Join Date: Mar 2004
Location: , , USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Nope. Anybody else help?


Reply With Quote
  #4 (permalink)  
Old November 15th, 2004, 04:22 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   Are you trying to rename them in reports or forms? If so, just change their Caption to the name you want to appear.

   If you are changing them on the fly, you can use aliases in query fields like this:

Query Field - Field_1:([RealFieldName])

   The RealFieldName of the field will show up as "Field_1" in all subsequent uses.

   You can also use VBA to do this. If you use the Form's OnCurrent Event and use this code:

  Me.TextBox_Label.Caption = "Field_1"

Hope this helps,

mmcdonal
Reply With Quote
  #5 (permalink)  
Old November 15th, 2004, 04:24 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You could also do this in a report using the Detail's OnFormat property and referring to the Lable.Caption property.

mmcdonal
Reply With Quote
  #6 (permalink)  
Old November 16th, 2004, 08:15 AM
Authorized User
 
Join Date: Mar 2004
Location: , , USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for replying. I'm afraid neither case applies. I would like to be able to change actual field names in a table which is an imported, manually prepared, Excel spreadsheet and the first row has column headings which may have spaces, periods or slashes etc.
I would like to rename the field names to remove the above characters.

For example:

Default date to DefaultDate
BalanceO/s to BalanceOs
Old.address to OldAddress

and so on. I have code that runs against this table and it would save me having to go in and change the field names manually.







Reply With Quote
  #7 (permalink)  
Old November 16th, 2004, 02:41 PM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dim sSQL As String
Dim cn As Connection

    Set cn = CurrentProject.Connection
    cn.CursorLocation = adUseClient

    sSQL = "EXEC sp_rename 'tblSiteFee.[Modified Date]', 'ModifiedDate', 'COLUMN'"
    cn.Execute(sSQL)
set cn = nothing
cn.close


Reply With Quote
  #8 (permalink)  
Old November 16th, 2004, 03:34 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would change the field names manually.

mmcdonal
Reply With Quote
  #9 (permalink)  
Old November 20th, 2004, 10:18 AM
Authorized User
 
Join Date: Jul 2004
Location: clapton-in-gordano, n.somerset, United Kingdom.
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

Despite the cynical comments you have so far received, I too have to do this regularly. All my legacy databases are currently DAO so if you have not got the library in your references, you must activate it before you run this

Function changefieldnames(oldname As String, newname As String)
'----- oldname and newname are passed to this function from
'----- wherever you are in your process. I would do it via a
'----- form, but if the field names you change are the same every
'----- time the process is run, why not store them in a table. The
'----- code you will need to pass the field names to be corrected
'----- from the table is given in Sub readinfieldnames() below.

Dim db As Database
Dim tdf As TableDef
Dim n As Object

Set db = CurrentDb
Set tdf = db.TableDefs("tablename")
For Each n In tdf.Fields
    If n.Name = oldname Then n.Name = newname
Next n

Set tdf = Nothing
Set db = Nothing

End Function

Sub readinfieldnames()
Dim rst_data As Recordset
'------ Assumes you have a table with 2 columns. Column 1 contains
'------- downloaded field names, column 2 has the names you want
Dim oldfieldname As String, newfieldname As String

Set rst_data = CurrentDb.OpenRecordset("tbl_of_field_names")

With rst_data
    .MoveFirst
    Do Until .EOF
        oldfieldname = .Fields(0).Value
        newfieldname = .Fields(1).Value
        changefieldnames oldfieldname, newfieldname
        .MoveNext
    Loop

 End With
 Set rst_data = Nothing

End Sub


I did this from memory so there may be one or two syntax errors. Sorry in advance. Hope this helps


Alan T
ElanSolutionsLtd@aol.com

Reply With Quote
The Following User Says Thank You to elansolutionsltd For This Useful Post:
seljo (August 10th, 2009)
  #10 (permalink)  
Old January 18th, 2006, 11:50 AM
Registered User
 
Join Date: Jan 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This code might be a little bit more flexable

Sub changefieldnames()

Set db = CurrentDb()
For Index = 0 To db.TableDefs.Count - 1
    If Left(db.TableDefs(Index).Name, 4) <> "MSys" Then
        tablename = db.TableDefs(Index).Name
        MsgBox (tablename)
        Set tdf = db.TableDefs(tablename)
        For Each n In tdf.Fields
            oldname = n.Name
            n.Name = Replace(oldname, "xxx", "yyyyyy")
        Next n
    End If
Next
Set tdf = Nothing
Set db = Nothing

End Sub
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Renaming long file name with VBA JillB Excel VBA 1 March 24th, 2008 06:20 PM
How to add fields to an existing table using VBA donrafeal Access VBA 3 March 22nd, 2006 06:11 PM
How to replace pivot table data fields using VBA? rstober Excel VBA 2 August 19th, 2005 06:06 AM
Renaming a table ... ERROR ?! SKE Classic ASP Databases 2 May 16th, 2005 06:04 AM
Renaming a Column/Field in Access Table james_sellwood ASP.NET 1.0 and 1.1 Professional 3 February 4th, 2005 08:45 AM



All times are GMT -4. The time now is 04:55 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.