Add column to backend table through frontend

[legzelda]

Hello. I am new to this site and Access databases in general, so please hear me out.

I am trying to add a column to a table located in my database's backend. I am trying to do this through VBA code, but when I open up my database, I get a message saying: "Error 3611: Cannot execute data definition statements on linked data sources."

Any ideas on how to work around this issue? Is there a way to modify the backend through the frontend? One solution, I suppose, is to create a new table in the frontend, but my database would get large, right? Any suggestions would be much appreciated.

[mattj]

You will not be able to do this. The bigger question is "why would you need to? " If your tables are properly designed, there should be no need to add columns during "runtime". Perhaps if you were to describe your current table strucuture and what you are ultimately trying to accomplish we can provide a better solution.

HTH
Matt

[legzelda]

Unfortunately, I inherited this database at my job. The end-users of this database have requested additional functionality, and the only way to implement it is to create a new column in one of their tables. The database's original creator seems to have done a poor job at normalizing the database, as the structure is far from perfect. To complicate the matter, this database is located on remote desktops located several hundred miles apart, so I am looking for a relatively simple solution without having to modify each computer's copy of the back-end. Trust me, if I could implement a better way, such as a centralized database on my company's server, I would, but I just can't see that happening.

[geekgirlau]

What about this approach - instead of creating a new field in the existing table (which can be done but is fairly involved and not good design!), use DoCmd.TransferDatabase to export a table or table structure to an external database. You can then have a table that has a one-to-one relationship with your existing table, rather than changing the current structure.

[legzelda]

I created an interesting solution to this problem. I wrote a VBA script that, instead of modifying the table, creates a new table and exports it to the backend. When the database starts, it checks to see if the table already exists; if it does, then it does nothing. This works better for this database's design, too. Thanks for the thoughtful suggestions!