Renaming Access Fields from Excel Via DAO

Renaming Access Fields from Excel Via DAO

Post by John » Mon, 30 Apr 2001 09:07:39



After encountering Excel memory limitations, I moved the database
entirely to Access and now use Excel 2000 as the front end.  I have
encountered a problem.  It would simplify things if I could create,
delete, and rename the field name of one of my Access tables under
program control. Creating and deleting fields is no problem, but
everything I have tries has failed to allow me to change an existing
field name.  I am using the field name to hold information about the
fields contents.  For example the table in question holds periotic price
information about traded securities.  Field 1 holds the security symbol
and the additional fields hold the price at a given time.  The number of
price fields is a variable. The field name consists of a character
concatinated with the date. The lead character allows the program to
decide if the field represents current prices or not.  I have concocted
some ways arround the problem but they are kludges.

Any Suggestions?

Thanks-in-advance
John Owens

 
 
 

Renaming Access Fields from Excel Via DAO

Post by Douglas J. Steel » Mon, 30 Apr 2001 23:07:43


Well, it sounds as though your database isn't normalized (having the _name_
of the field contain information about the _contents_ of the field is
usually an indication of this), but it isn't that hard to change a field's
name:

Function RenameField(TableName As String, OldFieldName As String,
NewFieldName As String) As Boolean
' Function renames field OldFieldName in table TableName to NewFieldName
' Function returns True if successful, False otherwise

On Error GoTo Err_RenameField

Dim booStatus As Boolean
Dim dbCurrent As Database
Dim tdfCurrent As TableDef
Dim fldCurrent As Field

    booStatus = False
    Set dbCurrent =
Workspaces(0).OpenDatabase("C:\Data\download\temp\Tests.mdb")
    Set tdfCurrent = dbCurrent.TableDefs(TableName)
    Set fldCurrent = tdfCurrent.Fields(OldFieldName)
    fldCurrent.Name = NewFieldName
    booStatus = True

End_RenameField:
    RenameField = booStatus
    Exit Function

Err_RenameField:
' Err.Number = 3265 if table TableName doesn't exist, or if
' field OldFieldName doesn't exist in table TableName
    If Err.number <> 3265 Then
        MsgBox "Error " & Err.Description & " (" & Err.number & ")"
    End If
    Resume End_RenameField

End Function

HTH

--

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/


> After encountering Excel memory limitations, I moved the database
> entirely to Access and now use Excel 2000 as the front end.  I have
> encountered a problem.  It would simplify things if I could create,
> delete, and rename the field name of one of my Access tables under
> program control. Creating and deleting fields is no problem, but
> everything I have tries has failed to allow me to change an existing
> field name.  I am using the field name to hold information about the
> fields contents.  For example the table in question holds periotic price
> information about traded securities.  Field 1 holds the security symbol
> and the additional fields hold the price at a given time.  The number of
> price fields is a variable. The field name consists of a character
> concatinated with the date. The lead character allows the program to
> decide if the field represents current prices or not.  I have concocted
> some ways arround the problem but they are kludges.

> Any Suggestions?

> Thanks-in-advance
> John Owens



 
 
 

Renaming Access Fields from Excel Via DAO

Post by John » Mon, 30 Apr 2001 22:54:12


Douglas,
Thanks for the assist. I was close, but in this world you are either dead wrong
or dead right. I was dead wrong.
You mentioned the term "Normalized" and I have looked at severall references to
no avail. Any suggestions where I might find info on this?
Thanks again
John Owens


> Well, it sounds as though your database isn't normalized (having the _name_
> of the field contain information about the _contents_ of the field is
> usually an indication of this), but it isn't that hard to change a field's
> name:

> Function RenameField(TableName As String, OldFieldName As String,
> NewFieldName As String) As Boolean
> ' Function renames field OldFieldName in table TableName to NewFieldName
> ' Function returns True if successful, False otherwise

> On Error GoTo Err_RenameField

> Dim booStatus As Boolean
> Dim dbCurrent As Database
> Dim tdfCurrent As TableDef
> Dim fldCurrent As Field

>     booStatus = False
>     Set dbCurrent =
> Workspaces(0).OpenDatabase("C:\Data\download\temp\Tests.mdb")
>     Set tdfCurrent = dbCurrent.TableDefs(TableName)
>     Set fldCurrent = tdfCurrent.Fields(OldFieldName)
>     fldCurrent.Name = NewFieldName
>     booStatus = True

> End_RenameField:
>     RenameField = booStatus
>     Exit Function

> Err_RenameField:
> ' Err.Number = 3265 if table TableName doesn't exist, or if
> ' field OldFieldName doesn't exist in table TableName
>     If Err.number <> 3265 Then
>         MsgBox "Error " & Err.Description & " (" & Err.number & ")"
>     End If
>     Resume End_RenameField

> End Function

> HTH

> --

> 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/

 
 
 

Renaming Access Fields from Excel Via DAO

Post by Douglas J. Steel » Tue, 01 May 2001 04:49:15


You can find a little about it at the Microsoft site. You might start with
http://support.microsoft.com/support/kb/articles/Q100/1/39.asp : it has
links to couple of other articles as well.

Normalization is database-independent, so the following SQL Server articles
may help you too:
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/e...
_008c.htm
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/c...
_02_2oby.htm

What you might want to do is get a textbook on relational database
principles, though. Almost anything by C.J. Date, or Rebecca Riordan's
"Designing Relational Database Systems" Microsoft Press, ISBN 0-7356-0634-X.
You can get details at http://mspress.microsoft.com/books/3222.htm

HTH

--

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/


> Douglas,
> Thanks for the assist. I was close, but in this world you are either dead
wrong
> or dead right. I was dead wrong.
> You mentioned the term "Normalized" and I have looked at severall
references to
> no avail. Any suggestions where I might find info on this?
> Thanks again
> John Owens


 
 
 

1. Linking Excel to Access via DAO

I have tried to link Excel table to an Access db from VB code, using
DAO. It was successfull, but data types for linked columns are
determined by Access (I suppose), so i couldn't get controll over
it....When textual data from Excel are more than 255 characters long,
they are truncated and I get error msg from DAO...
How can I link Excel table to Access database and be shure that the data
type of linked field would be MEMO type? Its very urgent!

--
Posted via http://dbforums.com

2. how to access a table without knowing it's name

3. Renaming an Access field (reserved word) via VB6

4. How to execute sql script file from ODBC?

5. Connecting to Excel 8.0 via DAO

6. Q122352 - Updated

7. help: create view via excel.dao

8. ORA 01119 Mystery

9. How Excel as RS via ADO or DAO

10. Rename a field with DAO

11. Accessing Access 2000 via DAO

12. How can I rename an access table via c++ and ADO

13. Error 3464 when querying numeric field via DAO/ODBC