Hi
I'm a MSSQL developer and I've recently been tasked to fix up an Access based system. This change involves making database changes. I dont want to develop on the "live" datbase, i'd rather make a copy of one and then at the end when all testing is complete sync the live one with the dev in terms of the structure.
How do I do this with access? Can I make upgrade scripts from the dev database and run them on the "live" access database?
Thanks!
You can create scripts that add and change fields (columns), but it may be better create an empty copy of the dev database and append from the live database, using queries and VBA.
Of course you can, but sometimes it's harder than manually applying the changes. If you have to perform the changes only on one instance of the database, it could be better for you to copy it for development, develop your changes and prepare changes specification to apply on it manually/half manually (some scripts'll be easy to develop - it'll shorten the time of changes deployment)
For example, it's hard to change "Required" attribute of column from TRUE to FALSE. (You've to make temporary copy of column, drop the original one, make new one with proper settings, copy content of temporary column to the new column, drop temporary one - pretty straightforward, isn't it?)
Since you area A SQL Server developer, consider that you could separate out the front end and backend in Access. Then you could make the backend be SQL Server express. Then you could easily script changes to structure for the prod database the same you do now in SQL Server. It's been awhile since I worked in Access, but I think you would then need to relink the tables in your front end.
You could follow Ramou's idea. Really, Access Databases are often completley contained in one file. So, you can copy the whole DB and work on and test the copy, deploying the copy. This way you can take advantage of the code, structure, and objects developed so far, without other programs or scripts. Access development is straightforward and there are many online resources.
Compare'Em will create the differences between two databases in VBA code so it can do the upgrade to the prod backend as required. I keep a version number in a table in the backend and perform the appropriate subroutines to upgrade the backend.
First off, the database should be split into back end (tables only) and front end (forms/reports/queries/modules/etc.). Each user will have a copy of the front end on her workstation, with links to the tables in the back end, which is shared on a file server.
For development, you would work on a copy of the front end, changing forms/reports/code, etc., and when ready to deploy, the users would just copy it over top of their existing front ends.
If you're changing data structures, you could script it, but why bother? It takes more time to code and test the scripting that it would take to make the changes in your testbed back end and then make them again when your new front end goes into production use. I'd only script something like this if I needed to upgrade multiple copies of the same back end.
The first key is splitting the database, as found in many answers already. For a quick easy method of deploying the client database, check out these instructions.