Changing data structure automatically

[Rjconrep]

I have a recurring job that requires quite a bit of changes made to the structure of over twenty tables and I would like to automate this process instead of having to go into design view and making the changes there.
Here is what the original structure of the table looks like.
ID Long Integer 4
Z4, ZIP+4/DP Text 255
Opt Endorsement Line Text 255
Sack and Pac Text 255
Full Name Text 255
Alternate Address 2 Text 255
Alternate Address 1 Text 255
Delivery Address Text 255
City, St ZIP+4 Text 255
City Text 255
St Text 255
ZIP+4 Text 255
First Text 255
Last Name Text 255
TAN_ID Text 255
ARC_ID Text 255
PromoNumber Text 255
SupplierName Text 255
ReturnAddrImage Text 255
RA_AgencyName Text 255
RA_Addr1 Text 255
RA_Addr2 Text 255
RA_City, RA RA_Zip Text 255
Fax Literal RA_Fax1 Text 255
RA_Email Text 255
RA_WebSite Text 255
RA_TS_StatPoundRA_TS_Number Text 255
RA_Phone1 RA_Phone2 Text 255
CTA_AgencyNamePrePhrase Text 255
CTA_AgencyName Text 255
CTA_AgencyPhonePrePhrase Text 255
CTA_AgencyPhone Text 255
CTA_CustomMessage Text 255
Copies Text 255
VIP Phone Text 255
SNPhone Text 255
This is what I need to change the data structure to look like:
ID Long Integer 4
Pin Long Integer 4
Z4, ZIP+4/DP Text 100
Opt Endorsement Line Text 100
Sack and Pac Text 100
Full Name Text 100
AltAddress Text 100
Delivery Address Text 100
City, St ZIP+4 Text 100
First Text 100
Last Name Text 100
TAN_ID Text 100
ARC_ID Text 100
PromoNumber Text 100
SupplierName Text 100
ReturnAddrImage Text 100
RAAgencyName Text 100
Addr1 Text 100
Addr2 Text 100
City Text 100
Fax Text 100
Email Text 100
WebSite Text 100
TS_State Text 100
Phone1 Text 100
NamePrePhrase Text 100
CTAAgencyName Text 100
PhonePrePhrase Text 100
AgencyPhone Text 100
CustomMessage Text 100
There are over 20 data files within this MDB. How can I automate this.

 

[mpastore]

you can use "ALTER TABLE". Enumerate the tables collection and do a exec() call with new fields for each table you need to change.

Mike Pastore

Hats off to (Roy) Harper

 

[Rjconrep]

I am not familiar with this process, could you be more detailed.

 

[Rjconrep]

This is what I have:
Dim cnn As ADODB.Connection
Dim strSQL As String
Set cnn = CurrentProject.Connection
'*************************CHANGE FIELD LENGTHS**************************************
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [Z4, ZIP+4/DP] Text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [Opt Endorsement Line] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [Sack and Pac] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [Full Name] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [Alternate Address 1] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [Delivery Address] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [City, St ZIP+4] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [First] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [Last Name] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [TAN_ID] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [ARC_ID] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [PromoNumber] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [SupplierName] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [ReturnAddrImage] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [RA_AgencyName] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [RA_Addr1] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [RA_Addr2] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [RA_City, RA RA_Zip] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [Fax Literal RA_Fax1] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [RA_Email] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [RA_WebSite] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [RA_TS_StatPoundRA_TS_Number] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [RA_Phone1 RA_Phone2] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [CTA_AgencyNamePrePhrase] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [CTA_AgencyName] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [CTA_AgencyPhonePrePhrase] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [CTA_AgencyPhone] text(100)"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] ALTER COLUMN [CTA_CustomMessage] text(100)"
cnn.Execute strSQL
'******************************RENAME COLUMNS*******************************************
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [Alternate Address 1] TO ALTADDRESS1"
'cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [RA_AgencyName] TO RAAgencyName"
'cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [RA_Addr1] TO Addr1"
'cnn.Execute strSQL
'strSQL = "ALTER COLUMN [50581 Group 1]* RENAME [RA_Addr2] TO Addr2"
'cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [RA_City, RA RA_Zip] TO City"
'cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [Fax Literal RA_Fax1] TO Fax"
'cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [RA_Email] TO Email"
' cnn.Execute strSQL
' strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [RA_WebSite] TO WebSite"
' cnn.Execute strSQL
' strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [RA_TS_StatPoundRA_TS_Number] TO TSState"
' cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [RA_Phone1 RA_Phone2] TO Phone1"
' cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [CTA_AgencyNamePrePhrase] TO NamePrePhrase"
' cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [CTA_AgencyName] TO CTAAgencyName"
'cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [CTA_AgencyPhonePrePhrase] TO PhonePrePhrase"
' cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [CTA_AgencyPhone] TO AgencyPhone"
' cnn.Execute strSQL
'strSQL = "ALTER TABLE [50581 Group 1]* RENAME COLUMN [CTA_CustomMessage] TO CustomMessage"
'cnn.Execute strSQL
'*******************************Delete Columns***********************************************
strSQL = "ALTER TABLE [50581 Group 1] DROP COLUMN [Alternate Address 2]"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] DROP COLUMN [City]"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] DROP COLUMN [ST]"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] DROP COLUMN [ZIP+4]"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] DROP COLUMN [Copies]"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] DROP COLUMN [VIP Phone]"
cnn.Execute strSQL
strSQL = "ALTER TABLE [50581 Group 1] DROP COLUMN [SNPhone]"
cnn.Execute strSQL

End Function

Now I need to find out how to update all twenty files at once.

 

[mpastore]

You are on the right track. Set up a function that takes the name of the table as a parameter. Then pass the names of all 20 tables to the function. You can set up the table names in a table and cycle through that.

Mike Pastore

Hats off to (Roy) Harper