Alter table question

[Beaurou]

Hi,

I have a from [ATC] with a textbox [project_id] where the user will input a field name. On the form I have a button to launch an event procedure to add (alter table )a new field to a specific tsable [TCodes]. so far I try almost everything my last script look like this:

Private Sub Command4_Click()
Dim strSql As String
Dim db As DAO.Database
Set Project = [Forms]![atc]![project_id].Form.Value
Set db = CurrentDb()
strSql = "ALTER TABLE TCodes ADD COLUMN project;"
db.Execute strSql, dbFailOnError
Set db = Nothing
End Sub

but I still get an error. Is there a way to pass the user input to the alter table?

 

[plog]

Dynamic ALTER TABLE queries scream poor table structure to me. Why do users need the ability to modify a table by adding columns?

I really think you don't have your tables set up properly if you are giving users this ability.

 

[static]

As plog said.

But to answer the question you need to specify a datatype and size/length.

CurrentDb.Execute "ALTER TABLE TCodes ADD [" & [Forms]![atc]![project_id] & "] text 255", dbFailOnError

 

[isladogs]

It would have been helpful to say what the error was!

I started typing this before I saw the responses from plog & static
Agree with plog's comments

Anyway ...

There is an error in the Set Project line
Also, you should specify what datatype you want for the new field
I've assumed a text field of size 100 - modify as necessary

Try changing your code to (changes marked in RED)

Private Sub Command4_Click()
Dim strSql As String
Dim db As DAO.Database
Set Project = [COLOR="Red"]Me.[project_id][/COLOR]
Set db =[COLOR="red"] CurrentDb[/COLOR]
[COLOR="red"]strSql = "ALTER TABLE TCodes ADD COLUMN Project Text(100);"[/COLOR]
db.Execute strSql, dbFailOnError
Set db = Nothing
End Sub

NOTE:
1. You do need the word COLUMN as shown above
2. Add error trapping. You WILL get an error if the 'new' field already exists!
3. This assumes its a local table. If its a linked table in an Access BE, the code will need modifying to get it to work

 

[Beaurou]

Problem solve Thank to you

final sript

Private Sub Command5_Click()
Dim strSql As String
Dim db As DAO.Database
Set db = CurrentDb
CurrentDb.Execute "ALTER TABLE TCodes ADD [" & [Forms]![add atc]![Report_ID] & "] text 255", dbFailOnError
Set db = Nothing
End Sub

 

[Minty]

Like the others I'm very wary of any sensible data storage method that would require or want a user name defined field added to a table.

Care to expand on it's purpose and your data layout?

 

[The_Doc_Man]

The problem is that you have limits on the number of fields and the size of a record, and for a short-text (255) field, if someone does this 8 times, you blew away the record. Access won't handle it any more. You might think this is a good idea, but it is DANGEROUS in the extreme. It's your database, so you get to choose the risks - but some choices are riskier than others. We would be remiss in our duties as good mentors if we didn't at least point out to you that this design has a major flaw with respect to long term stability.

 

[Galaxiom]

Definitely a design error. Moreover, the extra field is probably going to need another control in the associated form. This cannot be done in the runtime version of Access.

Simple rule. If something can't be done in runtime then it shouldn't be done at all.

 

[The_Doc_Man]

Excellent point, G!