field adding by user

[massivefermion]

Hi
I want to know how can I make my database that the user can add a field to a table from a form or sth?
thanks

[cgyrob]

you need to explain what you are trying to do a little clearer.

If you actually mean you want to give the user alter control in your database to add their own fields to your tables this is very bad practice and should never be done.

If what you actually want to do is let them insert new records into one of your tables you can do this with an insert statement.

I haven't used Access in a long time but I beleive if you are using access forms you can permit insert in your controls (drop box, etc). This will allow the user to add new values in the controls and inserts them into your table for you or else you will have to write an insert statement to manually add the records.

[massivefermion]

Yes I'm going to let the user add a new field to the table but Its not a real world database.

[cgyrob]

If that is the case you will have to use the alter command.

This is the syntax for addding a column in Access.

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] }

If you google 'VBA MS Access ALTER TABLE' you should get alot of hits for more details on how to use the command.

You will most likely have to build the statement in VBA on submit but I haven't built any Access forms in along time so there might be another way.

[massivefermion]

You know I'm not really good in access so could you tell me where should enter that command and how to connect it with the submit button?

[cgyrob]

In your form you should have a submit button. On the submit action you will add the alter commands. Like I said earlier it has been ages since I have even looked at Access but you should be able to double click on the button on the form which should bring you to the button click function. This is where you would put the commands.

If I were you, go through an Access tutorial or use the Access help manuals they should be able to walk you though the steps. Unfortunately I doubt they have a wizard for what you are looking for.

[massivefermion]

Ok.Now I have just one more question.
I created a form and a macro.the macro is a runSql with alter table statement.I just want to use the text boxes of the form for the table name,field name and field type of the alter table statement.I just wanna know how can I access them in the alter table statement?

[cgyrob]

Like I said earlier I am not an Access forms guru but I would guess that you would have to access the text boxes attributes from the macro. Something like 'textbox1.text', but again unless someone else who knows more about access form development chimes in I would suggest either reference book, tutorial, or help files to find the process. This process should not be too hard to find even a google search should give you some good examples on how to manipulate text fields in your forms.

The few times I have actually done anything in Access there was quite alot of documentation. Tons of people at microsoft are paid just to write docs so i am sure you should be able to find some decent resources.

[massivefermion]

This is my macro definition:

alter table 'Forms![Form1]![tname].text' add 'forms![form1]![fname].text' 'forms![form1]![ftype].text' not null

But when I run it,I get the error below:

syntax error in ALTER TABLE statement

[cgyrob]

The basic syntax for the alter command is

ALTER TABLE Employees ADD COLUMN Emp_Email TEXT(25);

I think you should be able to run something more like

alter table 'Forms![Form1]![tname].text' add Column 'forms![form1]![fname].text' 'forms![form1]![ftype].text'(field length required) not null

I am not sure how your macro handles text fields. You might have to build the string command and then execute it.

[massivefermion]

I wrote your code exactly in the macro definition but I got the same error message.then I changed it to:

ALTER TABLE 'Forms![Form1]![tname].text' ADD COLUMN 'forms![form1]![fname].text' 'forms![form1]![ftype].text'('forms![form1]![fsize].text) NOT NULL

But no difference.
I searched microsoft.com for access helps about this but I found nothing.I also searched through google but again no result.So could you give me a direct link?
thanks

[cgyrob]

When looking through a few pages I saw reference where someone used the syntax

Alter Tablename Add fieldname datatype(length)

Have you tried this syntax, not sure if it works.

I found this tutorial, not sure how good it is: http://fisher.osu.edu/~muhanna_1/837/MSAccess/tutorials/macro.pdf

or

MSDN Access Library: http://msdn.microsoft.com/en-us/library/bb979619.aspx

[massivefermion]

No difference
I'm really planing to do sth with bill gates.

[cgyrob]

Not sure what I can tell you. i have never used the Macros in Access and have no clue what the syntax error is. There is most likely some Microsoft syntax that you need but I don't know where to find except to troll some Access specific forums and tutorial. Did you look through the Access help files, sometimes they aren't over cryptic.

[massivefermion]

Yes.I really don't know where to look.Do you have an access fan friend?

[cgyrob]

Why don't you try on this forum.

http://www.accessforums.net/forms/

It is devoted to MS access so it would be a good place to take a look or most likely get a more informed answer then I can provide. It doesn't look like anyone else here has anything to add so i would take a look there for more help for this question.