Alter table - Add Column - Access 2000

[ccburn]

I want to deploy an application(FE/BE). I also want to be able to alter my tables by adding columns and incr/decr field sizes. I am more versed in ORACLE and just can't figure out how this is to be done in Access. As the application needs more columns for tracking more data. I think it should be as simple as adding a column to the BE design and adding the field(s) to the forms(FE). Since this is an app that is in production, the data must stay intact. Since the user expects to pop in a CD and click next the columns must be added through a vba or macro or something other than going into the backend themselves.

Oracle(very simple)
alter table tblfights add (column_name varchar2(10));

Access(is there an alter table function)
???


Thanks,

 

[evalesthy]

You should be able to find more in Access Help and by searching this forum - but for starters:

dim tdf as dao.tabledef

Set tdf = db.TableDefs("Batch Entry Pot&quot

With tdf
.Fields.Append .Field("ProdId", dbLong)
End With

 

[RickSpr]

There is an ALTER TABLE statement to do this in Access 2000. (It's actually a JET DDL statement.) Search the help file for ALTER TABLE.

The syntax is slightly different. Basically, it's:
ALTER TABLE table_name ADD COLUMN column_name data_type[(size)] [NOT NULL] [CONSTRAINT index]

I have to warn you, though: Any text columns added this way will NOT have Unicode Compression turned on, so they'll take up twice as much space as columns added in the user interface. For reasons only they understand, Microsoft didn't give us a way to create Unicode-compressed columns via an ALTER TABLE statement.

The CREATE TABLE statement has a WITH COMPRESSION option that creates Unicode-compressed columns, but it's ignored if you use DAO to execute it. You have to execute it through the OLE DB provider--don't ask me how! The help file neglects to mention this. I found it in a Knowledge Base article (URL:

http://support.microsoft.com/default.aspx?scid=kb;en-us;275561)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ccburn]

Thanks,

I appreciate all your help. I just got back to this- This is exactly what I needed to know. It is hard to upgrade table designs when you have to use update queries all the time.

Thanks- Thanks- Thanks !!!
ccburn.