Change Field Name in Table

[GrandMaMa]

We have a new software package from the bank. However we have one problem
with it! All of the other packages we have plus the one's we wrote use
LoanNo as the name of the field containing the Loan Number.

The application we have purchased use HMDA - LOAN NUMBER. I want to change
this Field to LoanNo

Here is the code I have so far. It does not work! I get no halts of any
kind. If someone knows the correct script I would really appreciate it!

Private Sub CmdB_Click()

Dim db As Database
Dim rst As Recordset
Dim tdf As TableDef
Dim tdfOld As Field
Dim fldLoop As Field
Dim fldCount As Byte


Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM tblWklyUpdt")

Set tdfOld = db.TableDefs(0).Fields(0)

fldCount = 0

For Each fldLoop In rst.Fields

If fldLoop.Name = "HMDA - LOAN NUMBER" Then
tdfOld.Name = "LoanNo"
MsgBox " " & fldLoop.Name
End If
fldCount = fldCount + 1

Next fldLoop

End Sub


Many Thank You's!

 

[Marshall Barton]

We have a new software package from the bank. However we have one problem
with it! All of the other packages we have plus the one's we wrote use
LoanNo as the name of the field containing the Loan Number.

The application we have purchased use HMDA - LOAN NUMBER. I want to change
this Field to LoanNo

[snip ill advised code]


Generally, it's not necessary to actually change the name of
a field just for this kind of reason. Instead, change the
name of the table to something else such as
tblWklyUpdtImport
Then just create a query that selects each field, assigning
the desired alias name as needed. Finally, save the query
using the original table name.

 

[GrandMaMa]

Marshall;
Thanks for getting back to me!

I tried that but something in their software will not let me rename the
table or let me delete it.

I cannot figure out (and neither can 10 other programmers) how they have
secured the table so that we cannot rename or delete it.

Naturally I created my own little database and want to get the change of the
name field working there first. It may stop me from changing the field name
also. However I would like to give it a try before I give up the ghost.

We have been running in to more of this every time we purchase a product. I
am trying to convince the school board to let the programmers in the region
do the applications.

Thanks Again

Granny

We have a new software package from the bank. However we have one problem
with it! All of the other packages we have plus the one's we wrote use
LoanNo as the name of the field containing the Loan Number.

The application we have purchased use HMDA - LOAN NUMBER. I want to change
this Field to LoanNo

[snip ill advised code]


Generally, it's not necessary to actually change the name of
a field just for this kind of reason. Instead, change the
name of the table to something else such as
tblWklyUpdtImport
Then just create a query that selects each field, assigning
the desired alias name as needed. Finally, save the query
using the original table name.

 

[Marshall Barton]

If this a linked table, then you don't need to rename the
actual table. The local TableDef can have a different name
than the actal table in the other program. Just rename the
linked table right in your application's database window.
--
Marsh
MVP [MS Access]

I tried that but something in their software will not let me rename the
table or let me delete it.

I cannot figure out (and neither can 10 other programmers) how they have
secured the table so that we cannot rename or delete it.

Naturally I created my own little database and want to get the change of the
name field working there first. It may stop me from changing the field name
also. However I would like to give it a try before I give up the ghost.

We have been running in to more of this every time we purchase a product. I
am trying to convince the school board to let the programmers in the region
do the applications.

We have a new software package from the bank. However we have one problem
with it! All of the other packages we have plus the one's we wrote use
LoanNo as the name of the field containing the Loan Number.

The application we have purchased use HMDA - LOAN NUMBER. I want to change
this Field to LoanNo

[snip ill advised code]


Generally, it's not necessary to actually change the name of
a field just for this kind of reason. Instead, change the
name of the table to something else such as
tblWklyUpdtImport
Then just create a query that selects each field, assigning
the desired alias name as needed. Finally, save the query
using the original table name.

 

[GrandMaMa]

Thanks again for getting back to me!

The file is transmitted over the Internet. It comes in as an Excel.csv
file. Somehow their software converts the .csv file and inserts it right
into the database. It is not a linked table.

I never even knew an Excel file had an extension other than .xls.

We are still trying to figure out how to do it. We are trying TableDefs and
Fields but still cannot figure out how to change the field name.

Thanks Again

Granny

If this a linked table, then you don't need to rename the
actual table. The local TableDef can have a different name
than the actal table in the other program. Just rename the
linked table right in your application's database window.
--
Marsh
MVP [MS Access]

I tried that but something in their software will not let me rename the
table or let me delete it.

I cannot figure out (and neither can 10 other programmers) how they have
secured the table so that we cannot rename or delete it.

Naturally I created my own little database and want to get the change of the
name field working there first. It may stop me from changing the field name
also. However I would like to give it a try before I give up the ghost.

We have been running in to more of this every time we purchase a product. I
am trying to convince the school board to let the programmers in the region
do the applications.

GrandMaMa wrote:

We have a new software package from the bank. However we have one problem
with it! All of the other packages we have plus the one's we wrote use
LoanNo as the name of the field containing the Loan Number.

The application we have purchased use HMDA - LOAN NUMBER. I want to change
this Field to LoanNo
[snip ill advised code]


Generally, it's not necessary to actually change the name of
a field just for this kind of reason. Instead, change the
name of the table to something else such as
tblWklyUpdtImport
Then just create a query that selects each field, assigning
the desired alias name as needed. Finally, save the query
using the original table name.

 

[Marshall Barton]

A .csv file is a text file with comma separators. Are you
sure that it is really "inserted" into your .mdb? If so, it
was Imported and you should be able to rename it. It's also
very likely that it is linked and changing the table name
should be an easy thing to do (actually, without your
application using Access's full security paraphernalia, I
can't think of a way to prevent you from renaming it). Is
this Access file something that was provided to you along
with some other application?

If you can't rename the table, then you can't alias the
field name in a query so the rest of your application is
unaware of it.

It's probably irrelevant now, but normally you can rename a
field by using a line of code like this:

CurrentDb.TableDefs!tablename.Fields![HMDA - LOAN
NUMBER].Name = "LoanNo"

Of course this is not allowed for a linked table because the
fields are in the actual table.

Note that the database window displays a little diamond icon
in front of a linked table so it's easy to tell if it's a
real table or linked.
--
Marsh
MVP [MS Access]

The file is transmitted over the Internet. It comes in as an Excel.csv
file. Somehow their software converts the .csv file and inserts it right
into the database. It is not a linked table.

I never even knew an Excel file had an extension other than .xls.

We are still trying to figure out how to do it. We are trying TableDefs and
Fields but still cannot figure out how to change the field name.

If this a linked table, then you don't need to rename the
actual table. The local TableDef can have a different name
than the actal table in the other program. Just rename the
linked table right in your application's database window.

I tried that but something in their software will not let me rename the
table or let me delete it.

I cannot figure out (and neither can 10 other programmers) how they have
secured the table so that we cannot rename or delete it.

Naturally I created my own little database and want to get the change of the
name field working there first. It may stop me from changing the field name
also. However I would like to give it a try before I give up the ghost.

We have been running in to more of this every time we purchase a product. I
am trying to convince the school board to let the programmers in the region
do the applications.


:

GrandMaMa wrote:

We have a new software package from the bank. However we have one problem
with it! All of the other packages we have plus the one's we wrote use
LoanNo as the name of the field containing the Loan Number.

The application we have purchased use HMDA - LOAN NUMBER. I want to change
this Field to LoanNo
[snip ill advised code]


Generally, it's not necessary to actually change the name of
a field just for this kind of reason. Instead, change the
name of the table to something else such as
tblWklyUpdtImport
Then just create a query that selects each field, assigning
the desired alias name as needed. Finally, save the query
using the original table name.

 

[GrandMaMa]

Marshall;
Thanks Again for assisting me. When I get to the office I will
try to figure out the delete and/or renaming it again. I can create another
table with the make Table query or even using the DoCmd Copy. I may have to
move everything over to another database and use the new database.

To be honest I never thought this should ever be this
complicated. Will
let you know. Believe me, we all are very confused on the security issue of
not being able to rename or delete the table.

Thanks A Lot for Assisting Me!

A .csv file is a text file with comma separators. Are you
sure that it is really "inserted" into your .mdb? If so, it
was Imported and you should be able to rename it. It's also
very likely that it is linked and changing the table name
should be an easy thing to do (actually, without your
application using Access's full security paraphernalia, I
can't think of a way to prevent you from renaming it). Is
this Access file something that was provided to you along
with some other application?

If you can't rename the table, then you can't alias the
field name in a query so the rest of your application is
unaware of it.

It's probably irrelevant now, but normally you can rename a
field by using a line of code like this:

CurrentDb.TableDefs!tablename.Fields![HMDA - LOAN
NUMBER].Name = "LoanNo"

Of course this is not allowed for a linked table because the
fields are in the actual table.

Note that the database window displays a little diamond icon
in front of a linked table so it's easy to tell if it's a
real table or linked.
--
Marsh
MVP [MS Access]

The file is transmitted over the Internet. It comes in as an Excel.csv
file. Somehow their software converts the .csv file and inserts it right
into the database. It is not a linked table.

I never even knew an Excel file had an extension other than .xls.

We are still trying to figure out how to do it. We are trying TableDefs and
Fields but still cannot figure out how to change the field name.

If this a linked table, then you don't need to rename the
actual table. The local TableDef can have a different name
than the actal table in the other program. Just rename the
linked table right in your application's database window.


GrandMaMa wrote:
I tried that but something in their software will not let me rename the
table or let me delete it.

I cannot figure out (and neither can 10 other programmers) how they have
secured the table so that we cannot rename or delete it.

Naturally I created my own little database and want to get the change of the
name field working there first. It may stop me from changing the field name
also. However I would like to give it a try before I give up the ghost.

We have been running in to more of this every time we purchase a product. I
am trying to convince the school board to let the programmers in the region
do the applications.


:

GrandMaMa wrote:

We have a new software package from the bank. However we have one problem
with it! All of the other packages we have plus the one's we wrote use
LoanNo as the name of the field containing the Loan Number.

The application we have purchased use HMDA - LOAN NUMBER. I want to change
this Field to LoanNo
[snip ill advised code]


Generally, it's not necessary to actually change the name of
a field just for this kind of reason. Instead, change the
name of the table to something else such as
tblWklyUpdtImport
Then just create a query that selects each field, assigning
the desired alias name as needed. Finally, save the query
using the original table name.