Announcement

Collapse
No announcement yet.

Access Database help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
  • #1

    Access Database help

    Any Access experts out there?

    I've been training to cover some processes for a coworker, which mostly entails running Access databases that he built. The general procedure is to copy it from the server onto my desktop, run the queries, extract some sort of reporting or data file to be uploaded elsewhere, and then compact it and move it back to the folder on the server. Simple enough, right?

    So far two of these databases have been randomly just quitting on me. I get an error that says "Access has stopped working" and Access restarts itself, tries to compact and repair, then creates a backup copy of the database. I ran the one today and it's happened several times. I've tried running the macro, running the queries individually, and running the macro with the "step" function where I have to tell it to proceed to the next query (which worked last time I did it but not so much today). And it even crashed once when I was trying to compact and repair (after sitting there for about 10 minutes), hoping it would help.

    It's not the same query every time, but it's been doing it in a set of queries that are all just looking at one or two fields in a table, and updating a another field in the same table with a tag that fits the criteria. There are no links to other databases, server data sources, or even other tables in the same database in these queries (there are in earlier queries but they worked fine). Just one table looking at itself. My coworker built these and he has no problem running them on either of his computers (and I don't have this problem with any of my own stuff). We both have the same operating system and same version of Access, so why is this happening to me and not him? It's really frustrating and I'm ready to scream.

    When I left work it was running query #20; I logged in after I got home (over an hour later, thank you winter storm) and it had crashed. I reopened it, ran the query again, and it got hung up and I killed it with the task manager. Then I tried it again and it ran in about 5 seconds. Now I'm trying to run query #21 and it is currently not responding, for the second time. ... Scratch that - it crashed again. I just restarted it and it immediately went to "not responding."

    The table in question has about 350K records, and the database is only 466,500 KB.

    Someone please help? (And I am not particularly SQL-literate so please talk to me in plain English.)
    I don't go in for ancient wisdom
    I don't believe just 'cause ideas are tenacious
    It means that they're worthy - Tim Minchin, "White Wine in the Sun"
    Tags: None
  • #2
    It's been to long since I worked with Access, so...is it possible there's a problem with the hard drive where the data and/or program are stored, or with the system's RAM?
    "For a musician, the SNES sound engine is like using Crayola Crayons. Nobuo Uematsu used Crayola Crayons to paint the Sistine Chapel." - Jeremy Jahns (re: "Dancing Mad")
    "The difference between an amateur and a master is that the master has failed way more times." - JoCat
    "Thinking is difficult, therefore let the herd pronounce judgment!" ~ Carl Jung
    "There's burning bridges, and then there's the lake just to fill it with gasoline." - Wiccy, reddit
    "Retail is a cruel master, and could very well be the most educational time of many people's lives, in its own twisted way." - me
    "Love keeps her in the air when she oughta fall down...tell you she's hurtin' 'fore she keens...makes her a home." - Capt. Malcolm Reynolds, "Serenity" (2005)
    Acts of Gord – Read it, Learn it, Love it!
    "Our psychic powers only work if the customer has a mind to read." - me

    Comment

    • #3
      There shouldn't be. Other stuff works fine. I ran literally a dozen+ other databases today that are running more complicated queries with no problems whatsoever.
      I don't go in for ancient wisdom
      I don't believe just 'cause ideas are tenacious
      It means that they're worthy - Tim Minchin, "White Wine in the Sun"

      Comment

      • #4
        I'm not clear on something: Is this happening on the SAME database no matter who uses it, or which computer it's on? Or is it isolated to one computer? Knowing that could narrow it down. Also, is there any chance that more than one person would have any given part of that database open/accessible at any given time?

        If it happens to everyone who uses that file, my guess would be that the database itself is corrupted. Do the IT guys at your place have the knowhow to troubleshoot that, or do you have a support contract with MS or another company that does?

        One outlier just to toss onto the pile: When I did support for people using QuickBooks & Peachtree, some people had issues (especially if there had been a recent computer crash or shutdown with the program open) where the system did not properly release the database handles/hooks/whatever they're called, causing it to believe that a given computer or user was accessing the database when they were not. Maybe Access is waiting for a "go ahead, I'm done with this table" response that will never come? Just a vague possibility to consider.
        "For a musician, the SNES sound engine is like using Crayola Crayons. Nobuo Uematsu used Crayola Crayons to paint the Sistine Chapel." - Jeremy Jahns (re: "Dancing Mad")
        "The difference between an amateur and a master is that the master has failed way more times." - JoCat
        "Thinking is difficult, therefore let the herd pronounce judgment!" ~ Carl Jung
        "There's burning bridges, and then there's the lake just to fill it with gasoline." - Wiccy, reddit
        "Retail is a cruel master, and could very well be the most educational time of many people's lives, in its own twisted way." - me
        "Love keeps her in the air when she oughta fall down...tell you she's hurtin' 'fore she keens...makes her a home." - Capt. Malcolm Reynolds, "Serenity" (2005)
        Acts of Gord – Read it, Learn it, Love it!
        "Our psychic powers only work if the customer has a mind to read." - me

        Comment

        • #5
          I think EricKei is on the right track.

          I would also look into how much HDD space/RAM you have available.

          But the more likely scenario is something that EricKei presented. I don't know that the DB is corrupted, as it does (eventually) run the queries. If this is a "network" database (i.e. the actual access file is stored on a server, and you open it via a desktop icon) then EricKei may be right. Perhaps someone with higher privileges tried to access it.

          Have you looked in the event viewer to see if there are any further clues there?
          Skilled programmers aren't cheap. Cheap programmers aren't skilled.

          Comment

          • #6
            I copy the whole database to my desktop and run it there, so even if someone was accessing the server copy, that shouldn't be an issue when I'm running it. There are other people who use the results after it's run each week (I'm not sure exactly what they're doing, though), but they aren't running the actual queries that create the results, so I don't know if it would crash for anyone else. The only other person who actually refreshes it is the guy who built it and he doesn't have a problem (and he has run it on two different computers).

            My hard drive is only about half full (273 GB free of 464 GB), and the computer properties window says I have 8.00 GB (7.88 GB usable) of RAM.

            I see lots of Access errors in the event viewer, but I don't really know what I'm looking at in there.

            And I don't think anyone at the help desk is going to be useful on this. For Access help I generally turn to my boss, my coworker who built this database, or Google. None of whom have been much help with this one.
            I don't go in for ancient wisdom
            I don't believe just 'cause ideas are tenacious
            It means that they're worthy - Tim Minchin, "White Wine in the Sun"

            Comment

            • #7
              If compact and repair doesn't work, you can export everything to a new database. IIRC you can do this by opening MS Access and creating a new database. Then find the import selection in the menus of the new database and tell it to import from the old database.

              Sorry for the vagueness... I'm not using a Pc right now.
              There's no such thing as a stupid question... just stupid people.

              Comment

              • #8
                Quoth It's me View Post
                If compact and repair doesn't work, you can export everything to a new database. IIRC you can do this by opening MS Access and creating a new database. Then find the import selection in the menus of the new database and tell it to import from the old database.

                Sorry for the vagueness... I'm not using a Pc right now.
                I actually tried that yesterday. It crashed. I've given up for now.
                I don't go in for ancient wisdom
                I don't believe just 'cause ideas are tenacious
                It means that they're worthy - Tim Minchin, "White Wine in the Sun"

                Comment

                Previous template Next
                Working...