Adding a field to Access DB using vba (1 Viewer)

MarkK

bit cruncher
Local time
Today, 03:19
Joined
Mar 17, 2004
Messages
8,178
I'll repeat, if your DB is properly designed you should not need to add columns, much less add them programmatically. A field named "ReferencePoint" in a table called "RootCanal" sounds important, and it is therefore, IMO, a design failure to need to add it using code. It should be there from design time on.

That, or you have confused your data with your structure, for instance it is obvious that if you have a system keeping track of people you don't have tables called Sue, Phil, Ed, Bob, Katrina, and so on. No. You have a table called People, and that table has a single field called FirstName.

But RootCanal is the name of a treatment the same way that John is the name of a person. To have a table called RootCanal is probably a mistake that then requires you to make other mistakes, like what happens now if you add a ScalingTreatment table. Now, how do you list the treatments you offer? You should have a Treatments table in which RootCanal should be a row.

I repeat, if you need to programmatically add columns as a part of the routine maintenance of your data, you have made a mistake.
 

JohnPapa

Registered User.
Local time
Today, 12:19
Joined
Aug 15, 2010
Messages
954
Cronk thanks for your input. You must be referring to billmeye's code, because the code which I posted works in both 2003 and 2013.

lagbolt thanks also for your input. Let me try to clarify a few things

Code:
I'll repeat, if your DB is properly designed you should not need to add columns, much less add them programmatically. A field named "ReferencePoint" in a table called "RootCanal" sounds important, and it is therefore, IMO, a design failure to need to add it using code. It should be there from design time on.

When the software (www.VisualDentist.com) was originally released it addressed general dentistry. It did not include functionality for specialists like Endodontists. When we decided to include such functionality we had to add tables and files. The same applies to functionality for other specialists we address such as orthodontists, periodontists, laser treatment specialists etc.

If you have any suggestions on the matter please let me know, but unfortunately the choices are limited.

John
 

billmeye

Access Aficionado
Local time
Today, 06:19
Joined
Feb 20, 2010
Messages
542
Your SQL won't work to add a field to a table in another database file.
Firstly there is a missing key word COLUMN missing after the ADD.
Secondly, as above, it does not work on an external file, whether anything is open or not.
Try it yourself and you will get an error message saying so..

John, this is the last I'll say about this. Using ADO and SQL is just about the easiest way to add columns to external tables. It always works. You can do it if your backend is Access or MSSQL or MYSQL etc. You can add tables, columns, write data anything you can do with SQL.

I've attached a DB and BackEnd for you to try and see it work. The code is in Database1.mdb in the Module.
 

Attachments

  • ADO_ADD_Column.zip
    47.8 KB · Views: 116

JohnPapa

Registered User.
Local time
Today, 12:19
Joined
Aug 15, 2010
Messages
954
billmeye, I just wanted to drop a quick line and say that I tried your code and can confirm that your code works very well.

I will try it out, to see whether it solves the specific problem with the database engine not being able to lock the table it wants to update.

Thanks,
John
 

billmeye

Access Aficionado
Local time
Today, 06:19
Joined
Feb 20, 2010
Messages
542
Thanks for the feedback John. If you have any questions let me know. Good Luck with your project.
 

Users who are viewing this thread

Top Bottom