code for fields, renaming fields, and changing field lengths? 1

[nja5150]

Hello-

I was wondering if anyone knew a way to use VB to change field names at the table level. I also require code to add fields to a table and rearrange order plus change the field length property. So far it seems that only changing the field lenght property is possible. Does anyone have a work around?

Specifically I have two tables with the similar data types BUT the tables do not have the same amount of fields nor are the fields named the same. I need to incorporate these two different tables into one main table. See the structure below:

Invoices Table
---------------
Invoicenum = Invoice number
invdate = Invoice date
Name = Name
Addr_1 = Address line 1
City = city, st & zip
*also contains several other fields both before and after this data that I don't need

Transfers Table
----------------
Invoicenum = Invoice number (with prefix added like -001)
receipt = Invoice date data in this table
B_Name = Name data in this table
B_Addr = Address data in this table
B_City = city, st & zip data in this table

In addition to moving everything over from the Transfers table I'll need to strip the prefix off of Invoicenum in Transfers table. So far this is the only task I think I know how to do. I might be going about this all wrong..I'm thinking maybe I should develop some code that will walk the record set and add records rather than going this route.

If anyone can help one way or the other I would greatly appreciate it!!!!

 

[TonyJollans]

Hi nja5150,

What a lot of questions .

If you want to change a column name, you can do this:

Dim tjDb As DAO.Database
Dim tjTab As DAO.TableDef
Dim tjFld As DAO.Field
Dim tjProp As DAO.Property

Set tjDb = CurrentDb
Set tjTab = tjDb.TableDefs!

TableName

Set tjFld = tjTab.Fields!

OldFieldName

Set tjProp = tjFld.Properties!Name

tjProp = "

NewFieldName

"

tjDb.Close

Set tjProp = Nothing
Set tjFld = Nothing
Set tjTab = Nothing
Set tjDb = Nothing

If you want to add columns you can do it with a bit of SQL, for example:

tjDb.Execute "ALTER TABLE

TableName

 ADD COLUMN

ColumnName ColumnType

;"

HOWEVER, do you really need to do this? If you want to add data from one table to another (with an Append Query for example) they do not need to have the same names - just similar enough data types.

Enjoy,
Tony

 

[nja5150]

Thanks Tony!

I've been looking for that field name change for a while!

I had never used an append query and I looked it up right after I posted this question---sure enought the append query was robust enough to perform what I wanted to do.

Still that change field name code was driving me nuts.

I am still having trouble coding for the field length property though. I can append to a current table that already has that setting in it--- so problem solved. But I am curious how the code would look.

Any ideas?

Thanks again!

 

[TonyJollans]

Hi nja5150,

Sorry, I thought you said in your original post that you'd managed the length change.

If it's text fields we're talking about, use the SQL ALTER:

tjDb.Execute "ALTER TABLE TableName ALTER COLUMN ColumnName TEXT(

NewLength

);"

Enjoy,
Tony