How does one “roll out” changes to an Access DB when the data and the UI are contained in the same file? I do programming of various sorts, and one of the last ones was involved modifications to forms ables\macros\etc. in an Access DB. In that case the data was fairly static, so I just grabbed a copy of it, modified it, and returned with the new one.
In a more dynamic production environment, people are going to want to keep updating data while hacking occurs. I can’t seem to find a recommended strategy for this kind of thing. Is there an import-schema feature I don’t know about? Maybe copy the data through some ODBC thing or even copy/pasting it if it’s small?
Don’t keep the data and UI in the same DB unless you have a really good reason to do so. For example that last Access project I did used a back end db on a server for the data and all the users had a front end Access UI with a ODBC link to the back end.
Is this what you’re asking or am I misunderstanding?
Access since about the 2003 version has the ability to deploy a solution in 2 separate parts: the data & the forms / reports / etc. And that’s how you should generally do things.
The result is there’s one set of data in one place on a server and perhaps multiple copies of the UI definition file on workers’ local PCs.
As well, as **zoid **indicated, the UI can be designed so that instead of using an Access database as its data storage engine, it uses SQL Server or some other database engine for data storage.
Even with this split of data versus UI, you still have the problem that if you change the data schema significantly, the old UI may not run with the new data. When that kind of a change is necessary, you’re stuck with stopping all use of the app, making the data changes, deploying a new UI to everybody, and *then *turning end-user access back on.
This is really no different than the problem faced by much larger more complex apps & environments using much fancier technology. Sometimes you’ve just gotta shut down to upgrade.
Since LSLGuy recommends the same thing, I guess it is.
Happens all the time with our other stuff. It looks like it’s a bit more cumbersome with Access, but still scriptable.
Don’t know if I want to relocate the UI part… not having to set up connection strings and stuff was one of the few things Access had going for it IMHO.
The way I do it is to grab a current copy of the database, make modifications to the copy, then export the forms/queroes/reports from the copy to the live database when it’s not being used. Causes a lot less downtime when I do it that way.
