Checking table layout?

[ptg]

Hi to all, This is my first post on AP.

We have an application which is used in a couple of offices and on a number of terminals. The data is stored in a back-end database on the server on each site.

The databases are getting to the stage that we cannot roll out changes to the back end table layouts relatively efficiently. What I want to do through vba if possible is basically have a blank local version of each table and say:

check tab_1 on linked_backend, if tab_1 layout <> tab_1_local layout then update the back end layout to mirror that of the local layout.

We cannot overwrite the data, but on occasion, we need to add an extra field to a central table as new bits of the application are developed.

I've tried a few searches here, and am probably using the wrong phrases, but not getting any results. I'd appreciate any pointers in the right direction

 

[jdraw]

see this forum/thread
http://www.accessforums.net/access/multiple-location-problem-33937.html

You may also try google

automatic updates of msaccess back end

This may be of value too.
http://autofeupdater.com/

I don't understand the

on occasion, we need to add an extra field to a central table as new bits of the application are developed.

Wouldn't these be planned updates/revisions?

 

[ptg]

Thanks for the links.. I shall take a good look - I tried phrasing it a few ways, but couldn't get google to be my friend on this occasion..

Unfortunately, I don't often get the luxury of a planned revision.. it's normally a knee jerk reaction from one of the bosses and we have to tweak pretty much there and then

 

[Pat Hartman]

I support an application that my client sells to his clients so I have no control over the environment or access to it. The FE needed new features and those new features needed schema changes so I created an Access app that made changes to the BE to accomodate the new FE. The database I am uploading won't solve your problem but it will show you how I solved mine. To use this type of solution, you need to impress on the users the need to have everyone out of the app while the BE is being modified.

The app opens to a form that asks the user to locate the BE that needs to be upgraded. From that form, the app runs the various DDL queries that modify the tables.

The original schema was pretty good (pat self on back) and so I haven't actually had to make any structural changes to it. All the changes have been to add new columns, add/delete indexes, and add new tables.

 

[ptg]

Hi Pat, I shall take a look and see if it gives me some insight - thanks for your reply, I appreciate it