Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Microsoft Access database maintenance script

Posted on 2013-12-18
Medium Priority
Last Modified: 2013-12-23
We have a Microsoft Access database created for a client. We will be splitting it to the server and the client workstations. It will be used by at least three users. I was wounding if I could create and run a script to do any maintenance on the backend overnight? I would image it could be done I am just not sure how to write it and what needs to been done. Thank you for any help.
Question by:RhemaTek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 48

Expert Comment

by:Dale Fye
ID: 39726803
Yes, there are a variety of ways to address this, but it depends on what kind of "maintenance" are you looking to perform?

Author Comment

ID: 39727241
Well I suppose that is my question within the question. We do not do much with Access databases. This is really my first. I did not develop it but I am the onsite technician for the client so I was giving the task of deploying it and maintaining it. So I am not sure exactly what needs to be done when it comes to maintenance. It is a PO creating database so I don't want forms or PO's that have already been created to get out or order if you will. There will be multiple users on it and I just want to make sure it works correctly as it gets used.
LVL 48

Accepted Solution

Dale Fye earned 1500 total points
ID: 39727310
Then generally, the only maintenance you would need to do would be to back it up (can be done as part of your normal server backup process), and to occasionally compact/repair the database.

There are several utilities that could be used to perform the latter operation.  FMS has an application "Total Access Admin" which is designed for just this sort of purpose.  You might want to look into it.
LVL 39

Expert Comment

ID: 39727542
I have an application that is sold to my client's clients which makes me quite far removed from their BEs.  To complicate the issue, the app can swap at will between SQL Server and ACE.  In practice a client chooses his preference and sticks with it so live swaps don't happen on the fly although we have had clients go from ACE to SQL Server.

We have made the decision to never delete a column or a table so maintenance is pretty much restricted to adding columns, changing column widths, adding tables, and adding/removing indexes.  We have a couple of system tables that we sometimes need to add rows to but I am gradually moving them all to the FE so I can update them more easily when I release a FE update.  Our schema was well thought out and so far we haven't had to do anything drastic.  Our schema changes are limited to twice a year although FE updates sometimes happen more frequently.

I have a tool (it's name escapes me at the moment and I am not at my office to check) that converts from one database type to another.  One of its features is that it will compare two databases and produce DDL to make one look like the other.  So I compare the old version with the new and end up with a bunch of Alter statements that make the necessary modifications.  For the SQL Server folks, I just bundle this in a file that the DBA can run.  For the ACE clients, I have to edit the generated DDL to change the syntax slightly.  For example, SQL Server prefixes all its table names with owner.  which in most cases ends up as DBO.mytable.  So, I use a text editor and make global changes to fix that up.  Then I take each separate Alter and save it as a querydef.  I assemble all these DDL querydefs in an update database.  I create a form with a run button and I add code to back up the database and then run each query in the appropriate order.  I also create a table in the BE if it doesn't already exist where I can log the fact that I ran the update because I don't want to ever run it twice.  Then I send the update database to the client with detailed instructions about when to run it (not while people are in the database!!!!).

So far we've run 4 schema upgrades in 5 years and all has gone well but it takes a lot of testing.

The tool I referred to earlier doesn't actually support Access but even for my apps that are strictly ACE I use it.  I upsize the old BE and then when I am finished with my changes, I upsize the modified BE.  I run the process and take the generated DDL and convert it to Access syntax.  I have found that this is a much more certain way of managing changes unless they are very simple and I do them immediately.  When I was doing it myself, I had to be really conscientious about logging EVERY change I made so I wouldn't forget it when I ended up modifying the production DB.  This tool, although there is still work for me, is a lot less stressful because it picks up every single change I made.

Author Closing Comment

ID: 39736030
We ended up getting a scheduled task to open the data base and then close it and on close Access compacts and repairs. We have tested it and it is working. The program fyed pointed me too would be a big hello and if we get to the point we need move control over the database out client will most likely purchase it. Thank you.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question