Alter table add column code help

[enfrspit]

Hi everyone, i'm very amateur in VB for Access. I'm just want to add a column into a table automatically. I've a table named "Times". Now i want to add a text filed into this table. I name the field "Third". What i write is:

Private Sub Toggle8_Click()

Dim SQL2 As String
SQL2 = "ALTER TABLE Times ADD COLUMN Third TEXT(255)"
DoCmd.RunSQL SQL2
MsbBox ("Done")

End Sub

I do realize it's simple, but i've not succeeded to compile the right syntax. It always says:" Compile error: Sub or Function not defined"
Please, help me.

Thank all of you for your help.

 

[DCrake]

Is there a reason why you want to add a field via VBA?

 

[enfrspit]

The table "Times" restore the amount for quotation. I have 2 fields in this table: "First" and "Second". However, in some case, i must send more than 2 quotations for one project. So, i just want create one button which add a new field automatically, because my colleagues don't know to use access.

 

[DCrake]

Your quotations should be in their own table

With a quotation number and a reference back to the source. Then you add recrods to the table as and when you need them. What happens if you need 3, 4 or more? What you are attempting is called denormisation.

 

[enfrspit]

Actually, what i want to learn is how to compile the right syntax of alter table add column. I've found some syntax in the internet but all of these don't work, just like the code i've shown above. I'm just a beginner of using VBA in access. Please help me to know how to write the right alter table add column sql code.

 

[DCrake]

If you persist in going down this route then why not create the 3rd field manually in the table and populate that. But like I said you should not be allowing users to add fields at ramdon in the database.

What does you data capture screen look like?

 

[enfrspit]

So, i've got a complicated table named "Quotation". In the table i have the following field:
"Id","Project","Amount1","Amount2", "Amount3","Amount4", "Amount5","Amount6", "Ref1","Ref2", "Ref3","Ref4", "Ref5","Ref6","Date1","Date2", "Date3","Date4","Date5","Date6".
"Amount1" is the amount for the first quotation of the one project, "Ref1" is the reference of first quotation of this project and "Date1" is the date of the first quotation of this project. So on for other field. In one month we send so many quotation for different projects. I'm scare if i transform second time into a record, it could excess the limit of the number of record in a table. In some case we have more than 6 times of quotation ( until 20 times). So that why i think about using alter table add column sql statement.

 

[DCrake]

You have got the concept totally twisted. You will run out of column beforre you run out of records

Max fields in 1 table = 255
Max Records in 1 table = millions

You need at least 2 tables

TblQuotations
QuoteId
Quote Details


TblRequotes
QuoteId
RefId
Date
Amount
etc

Then link the Quote id in both table so you can add as many requotes as you like.

You need to research what Access can do. Take a look at the northwind sample db that comes with Access.

 

[enfrspit]

I've got it. I'll try as you said and review Northwind sample. Thank u very much for your replies.