SQL Alter Table help?

[SusanV]

Hi all,

I need to modify field names via VBA for numerous tables without knowing all
the field names in advance. I don't want to alter any data at this stage,
just the field names themselves.

For instance, for a table called FANS:

Field names BEFORE modification:
Equipment
HSC
Location
1245
1236
(etc - may have many numeric fields or only a few)

AFTER modification:
F_Equipment
F_HSC
F_Location
F_1245
F_1236

Then I have another table called MOTORS and I would prepend M_ to those
field names, and C_ for CONTROLLERS etc.

I'm looking at SQL's Alter Table method, but can't seem to figure out how to
have it loop through all the field names and rename them. What am I missing?

Thanks in advance,

Susan

 

[Douglas J Steele]

You don't need Alter Table for this. Assuming you've got a reference set to
DAO, you can use it:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("FANS")
For Each fldCurr In tdfCurr.Fields
fldCurr.Name = "F-" & fldCurr.Name
Next fldCurr

Of course, it sounds as though your database may need some more analysis.
Odds are you should probably have a single Product table, rather than one
for Fans, one for Motors, one for Controllers and so on.

 

[SusanV]

Woohoo! You rock Doug! And I know EXACTLY what you mean by not normalized -
but this isn't even a database really - I'm trying to use Access and VBA to
create a utility to do the following:

** Import 5 separate spreadsheets with a zillion fields each
** Grab 2 fields and concat to one new field
** Make pivot table from choice fields in spreadsheet using new field as
pivot
** Take related equipment using HSC (equipmentID) and concatenate single row
in new table
** Export back to Excel as 3 spreadsheets for final product.

So in essence, from 5 totally separate and unrelated spreadsheets (Pumps,
Fans, Motors and Controllers) I'll wind up with 3:
One for Fans and related motors and controllers
One for Pumps and related motors and controllers
One for "orphan" motors and controllers

The Fan spreadsheet will look something like this, with the rows aligned by
HSC:
Fan HSC, Location, (various numeric fields), Fan Motor, Motor HSC,
Location, (various numeric fields) Fan Motor Controller, Controller HSC,
Location, (various numeric fields)

It's F-Ugly, but doing this manually is taking approximately 2 weeks per
ship, and we have to do this for 109 ships, so I get to try to automate it,
as the deadline is fast approaching. Blah.


Thanks TONS!!!
;-)

Susan

<goes back to hideous project and mounds more frustration>