Distributing Updates to the backend. (1 Viewer)

[GoodyGoody]

Hi All, Now my database is at MVP stage I am now looking to distribute updates to the backend database. For now the copies are all local to each user so they will need to manually update the database themselves. What is the most efficient way of doing this? It means potentially new tables, changes to existing tables and SQL queries to e.g. populate new fields and/or tables. I'd appreciate people's advice and experience. I'm looking not necessarily for the cleverest solution but the one easiest to implement for users.

Thanks

 

[GinaWhipp]

Hmm, okay why isn't the Backend on the Server? If it was you could go get it at night, make your updates and put it back Then once you update the Frontend put that out to be automatically updated to the Users.

 

[isladogs]

Several of my applications are used by a number of different clients and I have no access to the SQL Server backends used with the applications.
Several also include one or more separate Access BEs on the network which are used for configuration etc

As a result, I use a different approach which all happens seamlessly

I distribute my FE updates as EXE files downloadable from my website.
Clients are alerted to any updates using code built into the FE: Web Version Checker
When the client runs the downloaded update, the installer does a number of tasks including running a SQL Server script which updates the BE adding new tables, deleting redundant tables, modifying field structure of existing tables, updating records etc as required by the new version of the FE.
The client then runs the 'master copy' of the FE which automatically relinks to the updated SQL Server BE and to any Access BEs in use
If the Access BEs also require updating, this is automatically done immediately from the 'master copy' following the relink.
The FE is then placed in a network folder to make it available to end users

End users click on a desktop shortcut which is actually a starter app. This checks whether a newer version of the FE exists on the network and, if so, copies it to the user's workstation. It then opens the FE and closes the starter app itself. See attached PDF if interested

 

[Cronk]

Gina, Colin's method is similar to my own for applications which are running in remote locations, remote as in distance and/or not having the ability to make direct access to the BE.

 

[GinaWhipp]

Hmm, I've done this a few times...

In my *primary tables* I add two User Defined fields of each Data Type, UserDefined1, Userdefined2, etc. That gives me the ability to add extra fields without actually adding them. They are just sitting there waiting to be used.

 

[Pat Hartman]

I send either a SQL Server script to perform the updates or create a separate Access app to run DDL to add columns and tables - I never delete either because they hold customer data. It is up to the client to coordinate updating the FE and BE at the same time. For safety, my FE always checks the BE version to avoid issues.

In your case, I would embed the update in the FE. Write a procedure that looks for a version table in the BE. If it is not there, add the table and run your DDL and DAO code to update the BE. In the future rely on the version table in the BE to decide what needs to be done. You have to be careful because if a user goes from v1 to v3, the update steps will be v2 to v3 rather than v1 to v3 so you have to figure out how handle this. In my case, I started with a version table in the BE because I was aware from day 1 that I would need to handle this issue so my code that runs at startup knows what version it is and knows what version it is set to update. If the BE is not the correct version, the FE will not run the updates and it will also not run at all since it will not be compatible with the client's BE.

 

[GoodyGoody]

Thanks Guys for all the helpful comments. Interesting the different approaches. Yes it would be nice to have the backend on a server but a small app like this and my current skillset and the users' locations potentially offgrid all mean that isn;t the case so I need a means of distributing backend updates and coordinating the BE and FE (like the good ol' days before the internet.