Split DB - A way to Give regular user rights to switch/refresh BE?

[AlexB123]

Hello,

I updated a database at work, and during development changed it to a split database.

Prior to my changes, users would make a copy of the single file database and store it in a directory as a backup. Whenever the production version would get corrupted, users would take the most recent backup and replace the old file.

Since making my changes, there has been only one occasion where I had to go in as an admin and use the Linked Table Manager to restore the backend to a previous copy. My users would like to be able to do this themselves, in case I am OOO.

Is it possible to create a form button, or a macro, that will temporarily give one or more of my users the ability to refresh the tables in the split DB?

 

[ranman256]

The tables don't need refreshed. Do you mean changing the link paths?

if you alter the table path in your copy,then distribute your FE.
i have a macro to swap between production and a test db.
it relinks all tables to the other BE.
is that what you want?

 

[AlexB123]

Hi ranman256,

That sounds like an amazing macro! But no ...

For example, let's say the name of my BE is: prodDB_7.25.18_be. At the end of yesterday, I made a copy of the prodDB_7.25.18_be and put it in a folder. Then this morning, a user clicked a form button several times, and now there are several rows of duplicate data.

Is it possible to allow the user to update the links and switch to the saved copy of prodDB_7.25.18?

 

[stumac]

Your setup sounds quite confusing, normally I would have a main BE with a name like MydatabaseBE and then create backups like mydatabaseBE.DDMMYY

If I then need to restore I just overwrite the MydatabaseBE with the relevant backup file - that way there is no need to relink the BE.

Also it means that there is no need to deploy a new FE to every user and no risk that any user may still be updating a different BE.

 

[AlexB123]

Yes, the setup is confusing. I'm trying to force order on to chaos

What is the best way to make backups? I've wanted to automate the process, but can't use Windows scheduler due to user permissions (fairly limited). Also, what is the best way to overwrite the file w the relevant backup file? Can I do it with VBA?

Coming up with a better backup & restore solution is what I need ... but I still have meet my users expectations in some form or another.

 

[Joe4]

What is the best way to make backups?

Does your workplace not do nightly backups?

If not, you would need some process to copy over the database to another location every night.

From personal experience, I would be VERY hesitant to giving this sort of control to the users. Restoring databases typically isn't a task that you want them doing. There is too much of a possibility that they will mess something up, and wipe out a bunch of changes. You could have users "stomping" on each other.

For example, User A made 100 changes today. User B goofed something up, and decides to restore from last night's back-up. So User A's 100 changes are then wiped out.
That is why companies usually have a single person (or team) managing this sort of stuff.

The bigger question may be: why is your database becoming corrupted so often? Is it really becoming corrupted, or are users just entering bad data?
I think it may be better to investigate the reasons why that is happening and address those.

 

[AlexB123]

The bigger question may be: why is your database becoming corrupted so often? Is it really becoming corrupted, or are users just entering bad data?
I think it may be better to investigate the reasons why that is happening and address those.

I inherited a legacy database replete with errors ... it had been built by many different people over a decade, and I recently replaced it. Many of the reasons the old database became corrupted were addressed when I re-developed and updated the application. This recent error was due to a Network backup, plus user error (excess clicking) ... so I addressed the user error by adding an additional MsgBox before running the process.

Because these users have done things a certain way for so long, I'm having to very carefully, very minimally change procedures. They expect to do certain things as part of their job, and the times they run into issues I am not always available. The problem is that I can't automate the backup procedure without some workaround, and the other option to get IT to run it as a nightly process will require time and diplomacy and red tape. As to restoring, I'm guessing the best approach is to create a step by step process, and train a manager specifically to do it, in the event I am not around?

 

[Joe4]

The backup/restore process is actually very easy, in theory.
All you need to do is to make a daily backup of the back-end database.
If you need to restore it, all you need to do is to copy over the back-up copy overtop the active copy with the same name. That's it. The really isn't anything else to it. You don't need to worry about re-linking or anything like that.

If you copy the backup to a backup folder with the exact same name, then it is really easy. You could create a very simple executable or VBS file that does this by clicking on it.
However, it sounds like the tricky part for you is to create that back-up copy every night. You will need to come up with a way to do that. The only way to ensures it happens automatically is to Schedule it through some program, or have your network guys set up a job to do it. I do not see any way around that, other doing something like keeping an Excel file perpetually open with an OnTime event that makes the copy at a certain time (not a good solution in my book, as if the Excel file closes for any reason, i.e. reboot, if someone forgets to re-open it, you won't have any backups going).

What exactly are the errors caused by "excessive user clicking"?
If that is the main cause of problems, there are probably better ways of handling it rather than restoring the database.
If it is creating Duplicate records, you may be able to add Indexes or processes to prevent that. Or at least, a simple process to delete the bad records.

 

[AlexB123]

Thanks for the response! I think I have a few ideas for creating and simplifying the backup/restore process now!

The "excessive user clicking" was a one-off error ... the network bottlenecked, and instead of waiting the user clicked a second time on the control ... although the code won't run a second time after it has finished running, in this instance the program executed twice since the calls happened so fast. I added a MsgBox to have the user confirm they want to run the code, so that should not happen again. But the problem was duplicate data.

Apart from just doing a search on Indexes, or similar processes, can you recommend any sources for learning about them? Any specific recommendations as they relate to Access? This sounds like a good solution that will prevent a need to restore from backups, but implementing both approaches as a single strategy seems most ideal.

Thanks for all of your input!

 

[Joe4]

Apart from just doing a search on Indexes, or similar processes, can you recommend any sources for learning about them? Any specific recommendations as they relate to Access? This sounds like a good solution that will prevent a need to restore from backups, but implementing both approaches as a single strategy seems most ideal.

Actually, I made a poor choice of words - it is actually Primary Keys that may help you here. So, a Primary Key is a unique field. You can also have multi-field Primary Keys.
So, if you have certain non-Autonumber field(s) that are required to be unique in a Table, by setting them as the Primary Key, if someone accidentally tried to add the same record a second time, Access would reject it due to a Primary Key violation. So that would prevent the accidental addition of duplicate data.