Access front-end deployment

[Euler]

Hello,

I need to deploy a revamped front-end database to users in the field. This new front-end includes a new module that needs to run and add new fields to the back end tables, increase the size of a field in one of the tables, create a new back-end table, relate it to the existing tables and link the front end to the back-end tables.

I need this code to run once only when the user opens the new front end and then never run again when the user subsequently opens the front end.

I can't find anything on the Internet about how to do this, if there is a way. I'm thinking of running it through the autoexec and then deleting the module but then the next time the autoexec runs it will seize up because it can't find the code.

Any ideas anyone?

P.S. After posting this I thought of a way to do this but I'm not sure it's generically valid. All I need to do is check for the existence of the added table at the start of the code. If it isn't there, run the code. If it is there, exit the sub. This is fine for this occasion, where I add a table. But is there a better way for all occasions?

[June7]

Every user has their own independent backend?

What do you mean by 'all occasions'? Doesn't look like to me there can be a 'generic' condition. If another situation arises then incorporate another conditional statement and relevant code.

[Euler]

Yes, every user as his/her own backend.

Thanks for your response.

[orange]

Interesting set up --could you tell us more about the databases - their independence - and the users?

[Euler]

Each user keeps his/her own orders on the database (backend). They are completely independent and work internationally. In fact, this database has been in production for the North American users and they want to extend its use to their international staff.

[Rawb]

It's an interesting issue.

If the database is split, I don't think there's a way to do it. If there was though, it would have to work something like this

1. Check the Tables to see if they've already been updated
2. Import/copy the Linked Table to a local Table in the FE database.
3. Run an ALTER TABLE Query to make the changes (or make them using VBA).
4. Export the local Table back to the BE database.

Honestly, step 4's the only one that's likely to be hard. And, if nothing else, you might be able to just dump everything into a new BE database and then rename it after the update.

[Rawb]

So this question got me interested...

I've been playing around with it and it looks like it might actually be possible, although it's certainly not easy.

I've put together some VBA code that can do the following:

• Create a backup copy of a BE database (as long as none of the tables are opened by any users)
• Import a Linked Table
• Modify the local copy of said Table
• Export the local copy to a new BE database
• Delete the original BE database and replace it with the new BE database

I also have code that can query Table design specifics (which could be used to check if the update has already been run) and have seen code that can programmatically determine the location of a BE database by looking at a Linked Table.

If you're still looking at a method of pushing updates, I'd be happy to give you what I have. You'd still have to put it all together, but it would at least be a place to start.

[uniq_usr]

Interesting set up --could you tell us more about the databases - their independence - and the users?

I created a username to learn more about this. I work in a place where we have pretty similar setup with each user having separate backend. My employer wants to eventually bring all the data together on one database. So i was wondering how Euler combines data in his separate backends intor one place as I would assume all of them have same structure but different data. All of the tables in my setup have an auto number field as primary key so i cant simply copy/paste (append data) while maintaining referential integrity. Any inputs much appreciated.

[June7]

I have code in two dbs that merge copies of db into 'master' file - one uses autonumber PK and one uses custom PK. Custom PK is easier.

https://www.accessforums.net/access/...ate-49500.html

However, in both cases the merge is strictly to import new records, not update existing.

Also, review http://www.blueclaw-db.com/broad_int...eplication.htm

[Euler]

To tell you the truth, I don't think they ever bring the data together into one database. At least, no one has mentioned this as part of the process. At this point what the database does is send their orders to an Excel spreadsheet which the users can save and print. I think this is what they send to HQ for someone to enter into SAP or somewhere else. I just don't know what happens at that point.