Results 1 to 9 of 9
  1. #1
    squirrly is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    33

    Will database auto compact and repair on the server?

    I have a small home-based website where clients email form information to me that I manually input into the database and upload. I've rebuilt my site and plan on having the database reside on my web host's server so clients will have some control, and I can input from an admin section. This will be my first time having a database reside on the server and I have concerns regarding compact/repair and corruption. I've been researching these issues which only escalates my concerns.



    So that I understand, if my database is set to compact and repair on close (locally), will it compact and repair remotely? I was told by one of my host's tech support guys "yes". If this isn't so, will I need to run a script to check, close, compact/repair? Can the script be automated? Do I risk losing data?

    What causes corruption and how will I know if this happens? The only activities that will be allowed by clients are adding and updating records which are structured very simplistically (dropdowns, small fields, etc).

    Any advice is much appreciated. The whole idea is to make my work easier, but the process has been laborious and exhausting!

  2. #2
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    How and if the database can compact itself depends on how you're using it.

    Personally, for web content, I prefer to just use Access as a storage medium and do all of my web pages, data entry, and modification via PHP (OK, that's not true, for web stuff I prefer to go with a different database all together, typically MySQL or Postgres).

    That said, my guess is that it won't compact itself because doing so requires exclusive access to the database file which typically wouldn't be granted if multiple users need to be able to use it at the same time.

    It is, however, possible (if difficult) to compact the database on a schedule. How you do that depends on what OS the server is running though.

  3. #3
    squirrly is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    33
    If I ever decide to rebuild again, I'll learn PHP. At this point, I'm not willing to start all over again, so will continue with ASP and Access as long as I can still get support out here. I've put the question into my host again, so will see what they say.

    I've seen a few scripts out there, some of which check if the database is locked or closed before running a compact/repair - if locked, it doesn't run. I've also seen a script that will not change the database name once compacted/repaired, but will create a temp file and delete that file after running. Most of the scripts change the db name which can't possibly be run on a database supporting web pages.

    I can't possibly be the only one that has ever asked about this, so there must be an answer somwhere..... I'll keep searching. On the other hand, maybe I'll just periodically download the database off the server, run compact/repair and re-upload. I may have to take the site down or do this in the middle of the night so I don't interrupt client interaction, but so be it.

    Thanks for your input!!

  4. #4
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    You never said anything about ASP before! :O

    If you're using ASP to access the database file, then it most definitely will not auto Compact for you. The only way that would happen is if the database file was being accessed directly by the users (then you could set Compact on Close, which probably still wouldn't work since the users won't have exclusive access ).

    For right now, I'd say your best bet is to have a script shut down the site once a week for 10-15 minutes and compact the database then. Then, during the rest of the week, just make regular (at least daily) copies of the database that you can re-upload in case of corruption issues.

  5. #5
    squirrly is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    33
    Yeah, I run queries through ASP to publish my pages (tables/queries). I'm the only one back-end -- all front-end users are, well, the public visiting my website. The database will literally sit in a folder on my website's server like any other interactive dynamic database.... that's the plan anyway. Until the time I publish my updated website, I've manually entered all data, run compact/repair for maintenance purposes, and uploaded.

    Aside from corruption, I would like to continue the compact/repair maintenance about once a week, just like you mention. Your suggestion is exactly what I think I'll need to do. But since this issue is new to me, I have no idea what type of script to look for. I write small bits of VBScript, but this type of script is beyond me. I would like to find some samples on the internet that I could use.

    I posed my question to my web host's IT people and got a rather confusing answer back, giving step-by-step instructions on how to use Access's compact/repair tool locally on a corrupted file. They also provided a link to a Microsoft Office article on compact/repair and a list of command switch lines. Either I wasn't specific enough in my question, or this is their way of passing me off.

  6. #6
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    lol!

    Normally, I'd say its their way of passing you off, except that I have personal experience with scripting Access and the Compact & Repair process. And it's surprisingly hard to do. They might not know how to do what you want.

    If you're willing to stick with the older .MDB format, you can use Microsoft's JetComp tool to compact the database. It will probably be faster and simpler than any other method.

    If you want to use the new .ACCDB format though, you'll have to go with a full version of access (it MIGHT work with a copy of the Runtime, but I've never tried it). Also, Access will need to be installed either on the server where your file is stored, or somewhere on the same local network. You cannot expect to run this script over the internet!

    The biggest issue I've had with scripting Access's Compact & Repair is that Access ABSOLUTELY HAS to be able to interact with the desktop. This means that you have to keep a user logged in during the Compact/Repair. And, of course, there are a few setting that need to be changed in Scheduled Tasks for it to work.

    My personal method is to run Access from the command-line using the /compact option. I have a ready-made BATCH script that can be used, but it requires that you be able to install a few programs (Win32 ports of a few Linux utils) in whichever system Access is on.

    When you set up the Scheduled Task, make sure that under "Security Options" the option titled "Run only when the user is logged on" is checked, otherwise Access will lock up on running.
    Attached Files Attached Files

  7. #7
    squirrly is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    33
    I stuck with the .mdb format for the new database. My hosted server is through a very large commercial company, so I have no control.

    Honestly, it's feeling more and more like I just need to take the site down once in awhile for maintenance, copy the database off the server, compact/repair, back it up, and re-upload. Luckily, my site is small and community-based, so I could probably forewarn the public with a giant note on my index page. And I guess I'd know if someone were actually interacting with the database if an .ldb file appears in the db folder on the server, which I'd check for first.

    For future reference, do only Access databases have the corruption issue? Also, don't other databases need to be compacted if they become bloated, or do they self-compact on close? What other databases can be used with classic ASP?

    I've worked so, so hard on my project and when I run into things like this, it saddens me. But I will persevere.... until I decide I'm just getting too old and tired for all this.

  8. #8
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Any type of data can get corrupted, no matter what or where it is. But yes, Access databases, because they tend to include much more than just "data" have a far greater tendency towards corruption than other RDBMSs. And, If you remove all the other things Access has (like Forms, Reports, Modules, and Saved Queries) from the equation, I don't think corruption is nearly the issue it otherwise would be.

    Depending on the level of usage (creating, deleting, and modifying Records - just reading data doesn't count), every database will need some sort of compacting every once in a while. For most databases though, even those with high usage, the time between compacting data is typically measured in months (if not years). And even then, compacting other databases is mostly done for performance and size considerations, not to combat corruption.

    If you don't have access to the server itself, then I agree your best plan would be to have a "scheduled down time" once a week or so where you download the DB, run a compact & Repair manually, then copy it back up.

    Also, for the future, MS SQL Server is a good alternative to Access for the web because it's easy to transfer data between the two formats. You may want to look into just replacing your .MDB file with a SQL Server database, which would alleviate these corruption and performance issues. And, assuming you're using a SQL Wrapper for your ASP code, shouldn't be too much trouble to implement.

  9. #9
    squirrly is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    33
    You've made me feel a lot better, thank you. And thank you for the kind support which is hard to find for people like me choosing to remain in an archaic scripting and database environment without getting some sort of backlash.

    I have to at least move on with what I've done so far, and if things start to fall apart for one reason or another, I'll take it all up a notch. THANK YOU!!!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Compact & Repair, Will I Run Into Problems?
    By robsworld78 in forum Access
    Replies: 1
    Last Post: 01-10-2012, 05:11 PM
  2. Compact and Repair Database "Bombs"
    By GraeagleBill in forum Access
    Replies: 16
    Last Post: 09-27-2011, 09:23 AM
  3. Replies: 7
    Last Post: 11-11-2010, 11:16 AM
  4. Compact and repair on close
    By colotazzman in forum Access
    Replies: 2
    Last Post: 05-20-2010, 02:04 PM
  5. Error 3167 Compact and Repair
    By jonsuns7 in forum Access
    Replies: 3
    Last Post: 10-27-2009, 08:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums