Changing a back-end database from code

[Miguel A. Velez]

I'm making some changes to a Ms Access 97 application. In
order to support some functionality changes, I need to
make a couple of changes to the structure of tables in the
back end database. The first change is remaining a field,
the second is changing the length of a text field.

Is there anyway I can do this from code so that it can be
run when the application starts?

Any help will ne most welcome.

Miguel.

 

[Douglas J. Steele]

Renaming a field is simple:

Dim dbBackend As DAO.Database

Set dbBackend = DBEngine.Workspaces(0).OpenDatabase("Fullpath to
database")
dbBackend.TableDefs("TableName").Fields("FieldName").Name =
"NewFieldName"

Remember, though, that the linked table in your front-end, and any queries
that include that field name will also have to be changed.

Changing the length of a text field is more problematic.

The best approach is to add a new field of the correct size, run an update
query to transfer the data from the old field to the new one, delete the old
field, then rename the new field.

 

[Jeff Boyce]

Miguel

Doug's provided an approach for you. I'm merely curious... why do you feel
you need to change this in code? How will you prevent the same fixes from
being attempted to be applied every time the front-end opens? If you have
more than one installation, how will you prevent each installation from
attempting the update each time it starts?

 

[Miguel A. Velez]

Jeff,

In the front-end I have a control table with a set of
flags. I've added a new one that will be set to true by
the code once the database changes are made. The code
simply has to check this flag.

The application is in fact a standalone application, the
front-end and back-end are stored in a server and every
user uses the same front-end, this makes life a lot
easier. The application was split to facilitate
application changes which don't affect the database so
that the problem of copying data from the old to the new
version is eliminated.

The reason why I want to make the database changes through
code is that there are multiple uses of the application.
The application is a project control tool and each project
manager within the department may use it on their project
(s). Therefore there are multiple instances of back-
end/front-end. This way I only need to distribute an
updated front-end and when the project manager runs it for
the first time it will update the database structure.

Miguel

 

[Jeff Boyce]

Miguel

I may not be understanding your network configuration, but it sounds like
you are describing keeping a single front-end on a server. If you only ever
have a single user at a time, this configuration will only irritate the
network folks, as it requires pushing the entire form and code "up and down
the pipe" to the desktop.

If, however, you have more than one simultaneous user on a single
front-end/back-end pair, you risk data corruption, as each user will be
pushing pieces back and forth to the server. The more common approach, and
less likely to require regular rebuilds, is to deploy a copy of the
front-end to each users desktop.

The same "automated" work you are already considering/doing can serve as a
model for how you could have the individual desktop copies check for the
"latest" version on the server and download it if there's been an update.

Good luck!

Jeff Boyce
<Access MVP>