Microsoft Access database maintenance script

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.
Who is Participating?
Dale FyeConnect With a Mentor Commented:
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.
Dale FyeCommented:
Yes, there are a variety of ways to address this, but it depends on what kind of "maintenance" are you looking to perform?
RhemaTekAuthor Commented:
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.
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.
RhemaTekAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.