Solved

Microsoft Access database maintenance script

Posted on 2013-12-18
5
814 Views
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.
0
Comment
Question by:RhemaTek
  • 2
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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?
0
 

Author Comment

by:RhemaTek
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.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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.
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Closing Comment

by:RhemaTek
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.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now