Add new field via Vba

[gstylianou]

Good morning,

Is there any way so that I can add a new field to a table through automated process Vba? The Field which i would like to add is this:

Field Name: CustID (Number)


Field Size: Long Integer

Thanks in advance

[June7]

Yes, but why? Just open table in design view and edit.

[gstylianou]

Hi June7,

I'm trying to make an automation regarding the auto-update old data with new....believe me there is a reason..
Is there a way for this?

Thanks

[ItsMe]

DAO and TableDefs
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

[June7]

Also ALTER TABLE SQL action. http://www.w3schools.com/sql/sql_alter.asp

[gstylianou]

June7 thank you very much for your info,

Can you please make an example please because i don't have experience in Vba?

Thanks again

[June7]

Examples in the link. VBA is not needed. Build query in SQL View of query designer.

However, can run an SQL action in VBA like:

CurrentDb.Execute "ALTER TABLE tablename ADD fieldname LONG"

I still don't understand why you need code to just build and edit tables.

[gstylianou]

Good morning my friend June7,

Thanks for your information. I will try to add the new field into my table and if a will have any other issues i will let you know.! June7, as i told you I'm trying to make an auto- update procedures into my database in order for non - duplicates values as well as about to import old data (since 2003) into a new designed tables..! Believe me, there is an important reason.

Thanks again my friend.

[orange]

I suggest you tell us in plain English what you are trying to do;and why you need to add field(s) to table(s).
You may have your reasons/rationale, but, as you can see by the questions, this is not common behaviour/process.

If you have a newly designed database, then it should have all necessary tables -- unless you really haven't done thorough analysis before doing the design.

Normally, you would do some analysis, then some design; then some testing/validation/proof of concept; then refine design. By the time you go operational/production, you table designs should have been finalized. It would be a rare occurrence, or a newly defined requirement that would mandate a new field. And then again, the new field(s) would be after analysis and modification to table design in the design window-- not vba in the middle of production code.
I'd like to hear why it should be done using vba.

[June7]

As orange says, table structure should already be fixed. Importing or updating data should not require creating new fields.

[gstylianou]

Guys,

The answer of your questions is the following:
Since the last 2-3 years ago, I'm working on a "health and Nutrition" project and i hope you can understand how complicated is that sector.! Therefore, you can understand the data volume which are very complicated and includes many specifics issues (these data mainly are from the W.H.O - World Health Organization).

Its important to say that the database is on design mode till now and I'm working on it every day the last 2-3 years . Moreover, all the above data must being included into database but the "bad issue" is because the database is an active database on 14 PC.!!! As you can understand i must work every day for the development of the database but the same time the database must be active for the daily work.! Of course I'm making backups every hours and every morning (before 7am) i must prepare the "new version" for the next day. What that means? Its mean that its important to make some critical jobs before 7am every day such us the followings:

1. Firstly i must add the new data (Usually are some references values - etc)
2. If I made any modifications on the tables (last night or yesterday) i must find them and proceed directly to put them into existing tables (which not includes the new fields - objects etc)
3. Finally, i must prepare the database for the users (14 PCs)
4. Backup and ........relax.!!

The above is one of my usual day..!! So, i was looking to find a way in order to discover "something" which can help me to do all the above.! And after some many - many - many - coffees and of course cigarets i found "something".!! What is my idea:

a. The good news for me is i know from the beginning all the modifications which i made (last night). Remember, modifications such as new objects - new data - new fields into a tables - new forms etc..!! So, I thought if i can find the way automatically to import the new objects, sounds good.!! OK, Done.!! I found the way and i can add all the new objects into database by "One Click". Step 1 finished

b. The big issue for me its to going one-by-one to add the new fields into modified tables.!!! That's why I thought if i can find a solution for that..........means relax for me every morning..!So, if i can solve this problem i will be able through vba to insert the new fields into a modified tables without to going on each table to make that.!!

That's all guys..!! Any help is welcoming and respect..!!

Regards

[ItsMe]

...3. Finally, i must prepare the database for the users (14 PCs)...

Considering the overhead, I suggest you hire a consultant. If this is not an option, perhaps you can post your ERD here to help answer some of the questions posted here.

[gstylianou]

Hi ItsMe,

Sorry, i didn't understand your comments....! Could you please help?

Thanks in advanced.

[June7]

Is your database a split design? - it should be.

I don't understand the process for adding new fields. When I need to add a new field I open the functioning backend database and edit the table. I can sometimes even do this in the middle of the day because we are a small group and I know when the db is in use. Otherwise, do it at night when no one is around.

I do programming edits in a master copy of frontend and when ready for distribution, copy it into another folder. Each user has a copy of the frontend installed on their local PC. Whenever they open it, code checks for a new version and if found, automatically copies and replaces the file on user PC.

What you describe makes no sense to me.

[ItsMe]

Hi ItsMe,

Sorry, i didn't understand your comments....! Could you please help?

Thanks in advanced.

Sure, if you have 14 users and not enough time to get the database and applications squared away, you should employ an expert to help you. It is feasible to do so when you consider the productivity of 16 users and your time. If, for some reason, you cannot hire someone to help you, you should post your Entity Relationship Diagram here, in this thread. An ERD will help others to better understand what it is you are dealing with.