Use of ALTER TABLE command

[Obione01]

Have imported an Excel 2003 spreadsheet into Access 2003 using the Docmd.TransferSpreadsheet command. Now, I want to ALTER the imported TABLE to: 1) Remove unneeded fields, 2)Rename the four remaining fields (which do not have useable headers), and 3) Change field attributes, such as, Type, size, format, decimals. The four fields are: Text, Date, and Number types. Hoping there is some help out there.

 

[CautionMP]

You could ALTER TABLE or:[ol][li]It's Access, open the table in design view and delete/rename fields until your heart is content.[/li][li]Create a query with the fields/data you want, change it to a Make Table query, then run it.[/li][/ol]

(GMT-07:00) Mountain Time (US & Canada)

 

[Obione01]

I have been doing this manually, but want to use a command button with code underneath. Had come upon the ALTER TABLE command and had hoped it could be used to simplify and automate the process.

 

[Zion7]

Yes it can.
Be careful when you change data type, you may loose some data...

Recheck my syntax...

SQL = "ALTER TABLE tblCountry ALTER COLUMN txtCapital MEMO"
SQL = "ALTER TABLE tblCountry DROP COLUMN txtCapital"
SQL = "ALTER TABLE tblCountry ADD COLUMN txtCapital TEXT(50)"

SQL = "ALTER TABLE tblCountry ADD COLUMN txtDate DATETIME"
SQL = "ALTER TABLE tblCountry ADD COLUMN txtPopulation INTEGER DEFAULT 0"

CurrentProject.Connection.Execute SQL