Results 1 to 10 of 10
  1. #1
    Join Date
    May 2007
    Posts
    27

    Unanswered: Database corruption?

    Hi,

    I have an access2000 database which worked fine, but is acting up as of recently. Here is what is happening:

    1. Queries take hours rather than minutes. I checked my indexes, and they are all still there.
    2. One of my create table queries runs, but does NOT create the table! No error messages, no confirmation dialog boxes, nothing. The query runs (I see the progress bar on the bottom), and then ends, but no table has been created.
    3. Opening forms takes minutes rather than seconds. Even forms that do not query anything when opening still take a long time to appear.

    At this point, I don't trust the integrity of the database, and I am not sure how I can fix things.

    I tried creating a new database, and importing everything. This did not fix the database.

    I tried exporting to access2003 format, end then re-exporting from access2003 to access2000. This worked, and the database seemed to have behaved for a little while, but is now back to a broken state.

    Any suggestions?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This sounds like possibly a docmd.setwarnings False and the docmd.setwarnings True is not there or somehow did not execute that line.

    The times I've seen the symptoms you've described above are when I've had either a reference missing or a problem with the code somewhere. Or when I had 2 versions of Access on my computer and I imported one or more objects into another mdb and started coding without debug-compile first (always immediately debug-compile after importing a form, report or module from one mdb into another.) I assume you've debugged-Compiled and also compacted/repaired the mdb.

    Have you tried opening the mdb using the shift key and then manually running the query?
    Last edited by pkstormy; 10-31-07 at 14:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also - strange as it seems, try changing your default printer. I know it sounds wierd but this was the cause of some strange problems in an mdb for me once which tended to "die" with a certain printer driver selected as my default printer. I'd be curious if changing this makes a difference in your situation.
    Last edited by pkstormy; 10-31-07 at 15:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I also assumed you've checked for things like duplicate function names or any queries which may have a possible bad expression in it. Or field names which have a bad name (like a fieldname called: Date.) Not that I think this is the cause but these are some common things.

    Before you write-off the database and start over, I'd try possibly creating a new mdb and importing just the tables. Then just the queries. Run the queries to see if they work (especially the make-table ones.) Then 1 or 2 forms. Then a few more forms (and the modules to support them). (again, making sure you debug-compile after each import.). Then a few reports. Then the rest.

    If you can delete all the info in the tables and zip up the mdb and upload it, I'd be more than happy to diagnose it and see if I can detect anything. I can usually find problems pretty quickly.
    Last edited by pkstormy; 10-31-07 at 15:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Scary and dangerous....Make sure you've made a backup at this point. Try a Compact & Repair on both the Front-End and Back-End databases.

    If your database is set to Break on Unhandled Errors then go into Code and check all those On Error...... statements. Are the Errors being checked and Cleared (If Err <> 0 Then Err.Clear) before leaving each routine it's used in. Errors CAN move globaly accross your database. I found that out the hard way.

    Try setting your code to Break on All Errors and see what happens. Repair those Errors. You should be in this mode as much as possible anyways (in my opinion). Work on one module at a time until things are fixed or properly taken care of..

    Check all your Queries. Right down to each and any of your Combo and or List Boxes. Optimize them if you can. Make sure they are truely doing what you intended them to do. If a new engine was introduced somewhere then anything can happen.

    If your Back-End is on a server....then check permissions. Something may have gotten lost in that respect. Ask your Network Administrator to check things out and if he says no.....shoot him/her in the knee. They'll move a little slower but things will get checked out (just kidding ).

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by CyberLynx
    .....shoot him/her in the knee. They'll move a little slower but things will get checked out (just kidding ).
    .
    Head shots are sometimes quicker (or threatening to key their car or induce slow torture on their body). I re-decorated a guy's cube once to make my point.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2007
    Posts
    27
    Thanks for all the replies.

    I am the network administrator. I shot myself in the knee. It didn't solve the problem...

    I tried starting the database with SHIFT helt, and I am now running the create table query, but it's been running for well over an hour, so I don't think it's any better.

    I did try compact and repair many times already, no luck.

    Next thing I'll try is getting a couple of tables and queries in a new DB, and run the infamous create table query. Hopefully that will work.

    Any other suggestions? Anything else I can try in the mean time?

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Check out Post #12, and Post #7, in this thread at a sister-site.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by loquin
    Check out Post #12, and Post #7, in this thread at a sister-site.
    YESS! YESSSS!!!!

    You cannot believe what a relieve this is! My database works. Its' alive!!!

    The trick was, following post #7, to "decompile" the database. It did not reduce the file size of the database (to be excepted, as there is a LOT of data in it), but my forms, my queries, my everything just plain and simply works again.

    I truly owe you one (and the others that posted in this thready).

    Please accept my sincere thanks.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by pkstormy
    (always immediately debug-compile after importing a form, report or module from one mdb into another.) I assume you've debugged-Compiled and also compacted/repaired the mdb.
    Are you referring to the above or did you do the "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\MyPath\MyDatabase.mdb"

    option? If I Debug -> Compile on a regular basis, I've rarily had to do the 2nd /decompile method.
    Last edited by pkstormy; 11-01-07 at 10:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •