Solved Access - the most sustainable setup?

[hhag]

Hi there,

I need some advice. What is the best way to work on a daily base with Access and how should I set up the structure?
My idea is as follows:

Split the database with all tables (amount of data is not so big) in one back-end file and then have two front-end versions. One non-editable front-end (accde-file) for all users (5-10 co-workers will have access to the database) and one editable for my self where I can develop the application further with queries, forms and VBA macros. When I’m ready to distribute a new front-end version to users I just lock down my editable accdb-file and distribute a new accde-file to the users. During development of a new front-end version I do security copies of the editable front-end version of course.

How does this sound? Something I should think of? Any other ideas?

 

[theDBguy]

Hi. I would add a dev copy of the BE also. For example, the live BE would be on the network, and the production FEs are on each users machine. The dev copies of the FE and BE are on your machine.

 

[Deleted member 147267]

One non-editable front-end (accde-file) for all users

No - but it depends on what that means. It should be one copy per user. Maybe that's what you mean. If so, the plan sounds good to me, including a backup be for design development, or rely on periodic backups of the be as data changes. I used to do that daily on some db's.

 

[Dreamweaver]

I always maintain two complete versions developer and main

 

[AccessBlaster]

I do not believe you need to split the database UNTIL you the developer are ready to distribute. Get the basic structure right first.

 

[hhag]

Thanks for all feedback!
My idea is not to split the database until I think I'm ready to do so (but I know I'll face new wishes from the users) and all users will have their own copy of the FE version.
The only thought left I've is what about if I have one BE version on my machine as well. Then, when distributing a new FE version to all users, I need to make a new reference to the BE in the FE version, won't I?
And in the future I think I might need to add some new fields to some table in the BE, and this I would like to do in the 'sharp BE version', since data is continously change/added etc by users. The BE is backed-up on a daily base.

 

[Deleted member 147267]

I need to make a new reference to the BE in the FE version, won't I?

Definitely. It's the one thing you'll hear about after a release if you forget to do so. You could keep a document that outlines the steps you need to take when providing an update, and follow that until you get the hang of it. Could even just be a sticky note on your monitor.

 

[The_Doc_Man]

@hhag - I had four copies of what I was doing because of my Navy site. Maybe this is overkill for you, but it is so easy to do with just a little careful planning. I had four sets of folders: DEV, TEST, STAGING, PROD.

I had a DEV copy that was where I kept the developing code. The BE was just a copy of the production BE unless I was making a structural change. When structural changes were about to occur, I always took good notes for later.

I had a TEST copy that was where I kept the code that was ready for testing. It had its own separate BE, again a copy of the production BE unless there was a change to the structure. But there, I could do things to test operation without fear that I would disrupt the users.

When everything was good to go, I copied the TEST copy to a STAGING copy. The only difference was where the table links pointed. The staging area was where I changed links to the production copy. I also did Compact & Repair here. If I was making a structural change to the live BE file, it occurred here, too.

When it was all good to go, everything went to the PROD copy. We always had the opportunity to schedule down-time if notice was needed, so I would give notice just before one of the massive changes. If I had to restructure the PROD BE copy, I took everything down and then renamed the PROD copy of the BE. That way nobody could open the FE because the links are by file name. I made the changes to the STAGING copy. Tested it with the STAGING copy of the FE. Then moved it back to the PROD copy. When the BE file reappeared with the proper name, the linked tables would link up properly.

 

[hhag]

Thanks for all your feedback and sharing! I'll consider your advices.

 

[CJ_London]

My idea is not to split the database until I think I'm ready to do so

In my experience, better to do this as soon as the table design and relationships are defined so you can pick up performance issues - ideally the dev back end needs to be located in a similar relative location to the production back end.

 

[Lightwave]

I had one application that I had been running for over a decade. I settled on a split front and back ends and I took copies of the back end and the development front end. I would not keep copies of the runtime front end and I just compiled the front ends from my latest development copy. If I couldn't compile to a runtime from the development front end I considered the development front end to be corrupt and would roll back to the previous version. This only happened twice roughly in that period.

I had a shortcut to the vbs on my desktop that would allow me to make copies of the back and front end by a double click which I did in a rolling format. One named after the day of the month, one after the month of the year and another after the year. The thinking was that in the unlikely event that the picked up backup was corrupt I could keep stepping back initially through the days of a month then months then eyars either on the front end or back end.

When developing I also wrote a script so I could make copies of the front end distinct to the second by double clicking an icon on the desktop.

I still find it comforting to have this setup for important files so that I know that I can make a backup of all important files in the time it takes to double click an icon on the desktop and I know that everything will be nicely filed and clearly named.

This is the link to a variation of the vbs file that I used to copy all the files and put them in the correct locations

Rolling VBS save script

 

[zeroaccess]

I like the idea of a script, especially if I can run it daily with the Task Scheduler. That means even if I am away it will continue working (if I'm logged on - and that could be a problem).

Up until this point my regimen has been rather mundane, just doing a manual copy. I think that has its merits: if the script ever chokes some day due to IT changing things, someone needs to know how to do a backup.

 

[Deleted member 147267]

if I'm logged on

IIRC it doesn't matter. Thinking there is an option for running it regardless, but I for one didn't have to worry about it. They had a pc with its own account which was always on and listed as one to not be rebooted by IT.

 

[Lightwave]

And here's a vbs version that will make copies and name them unique to the second

I've left in the names of an actual file that I targeted for backup - here its an application that times athletics races which so happens isn't split. The database is pretty small data wise usually only about 200 to 300 runners but I like to have this script as I have timers punching in race numbers and there are only every seconds between racers. So if a database corrupted a minute ago I could have missed quite a few competitors. Timing takes places typically in tents half way up mountains and anything can kind of happen at anytime so I'm paranoid about taking backups!!

Things that have happened
RFID timing matts being dragged away by vehicles (when system using RFID chips)
Bloody weather - Horizontal rain / snow - Wind blowing over dragging away tent
Power breaks / overloads
Wobbly power
Cables being ripped from sockets or cables not being ripped and computers flying off tables.

Possibly a bit over exaggerated but challenging - when you get back in an office you are very rarely phased by flat tables a good network and stable power systems.

Option Explicit
Dim FSO
Dim Vardaytimefile
Dim Varmonthfile
Dim BDayFilePath
Dim BMonthFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
Vardaytimefile = "BackupVogrieParkManual-WeekdayTime-" & day(varnow) & "-" & Hour(varnow) & "-" & Minute(varnow) & "-" & Second(varnow) &  ".mdb"
Varmonthfile = "BackupVogrieParkManual-Month-" & Month(varnow) & ".mdb"
BDayFilePath = "C:\Users\Mark\Documents\RaceTiming\2020\20200126VogriePark\Backup\" & vardaytimefile
BMonthFilePath = "C:\Users\Mark\Documents\RaceTiming\2020\20200126VogriePark\Backup\" & varmonthfile

FSO.CopyFile "C:\Users\Mark\Documents\RaceTiming\2020\20200126VogriePark\VogriePark.mdb", BDayFilePath, "True"
FSO.CopyFile "C:\Users\Mark\Documents\RaceTiming\2020\20200126VogriePark\VogriePark.mdb", BMonthFilePath, "True"
Set FSO = nothing

msgbox "Backup Complete" ,0, "Backup Script"

 

[Lightwave]

And here is what some example backup files look like in explorer when I double click on the short cut you can see I made two backups on the 15th April

One at 16:45:47 and another at 17:13:14

April file is constantly overwritten with the latest version and will be until May is reached.

 

[hhag]

Hi, thanks once more for your input. I appreciate it!