Update the table structure in a Front-end/back-end situation

[Stijnele]

Hello,

I've got a Split database, where there a lot of back-end databases. There is one Backend-database for each customer. And there is also only one back-end linked at a time.

Now I want to update all of these back-end databases for the release of a new front-end version.


when I try this:

DoCmd.TransferDatabase acLink, "Microsoft Access", Text1.Value, acTable, "Analyses", "Analyses"
CurrentDb().Execute "ALTER TABLE analyses ADD Column Caption1 MEMO;"

I got an error "cannot execute data definition statements on linked data sources".

Is there an easy solution for this?

I guess copying the whole table from BE to FE, alter it and copy it again to BE is possible, but that doesn't look very efficient.

thanks,

Stijn

[patjones]

Hi Stijnele -

Where are you trying to run this code? In the front end or back end? And in what module?

In a situation like this, if there are any modifications that you need to make to table design, you can simply open the back end file and work with the tables in table design view. Since it seems like you're just trying to add a column, I don't think this should be too hard.

The other thing I would note is that if you have a separate back end for each customer, you might want to look at how your data is structured. It would generally be considered pretty inefficient to have a separate file for each customer...espe cially if you have a lot of customers!

Pat

[Stijnele]

Hi Pat

Actually I don't know how many back-end databases exist. I guess about 30.
The customers are seperated because they are used on our laptops and it's a must that they can be copied easily in case someone takes over a customer from a collegue.
I'm also working on a front-end for the customers so they can generate the reports on their own.

There is a table in the Back-end database that records the version. The Front-end reads the version and when it sees it's an old version it should add a few columns to the database.

Adding columns manually is not really an option because of the quantity of back-end databases and I'm planning to use this method more in the future for further developing the application.

Stijn

[patjones]

Stijn -

So, the laptops are taken off-site by employees when they go to work with a customer?

Do all 30 of the back ends contain the same type of data (in other words the same tables with the same columns, etc.)? If so, I would say that the easiest way to solve this problem is to combine them into one back end with properly normalized data.

If it's designed correctly it shouldn't be a problem when a customer's account transfers hands internally...be cause you'll have a customer ID or some other way to uniquely identify a particular customer and all their transactions, as well as an employee ID for the person in your organization who handles that customer. When the account changes hands, you'll simply do an update to the employee ID to reflect the change.

Let me know what you think. I'm just trying to understand your situation a little better...

Pat

[NeoPa]

To my surprise, I found there was no option within the syntax of ALTER TABLE to specify an alternative database. This seems to imply only the database that contains the code (FE) can be changed by such code. An unfortunate limitation it seems.