Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compact not completing process (Access 2000)

    All of a sudden I lost the ability of using Now(). So I did a Detect and Repair on My Access 2000 and then tried to do a Compact and Repair on the problem database. It would run, but by the indicator at the bottom, but stop slightly less that half way through. It did not delete the db#.mdb - rename it. I am able to run other databases even though the Compact indicator is at the bottom. Have made a copy of the problem
    database and can not Compact the copy either. I am not getting a "Not Responding" error when I do Ctrl+Alt+Delete so it seems that Access is not hung-up. I am able to Compact and Repair other databases. I have about 8.5 GB free disk space and 256 MB of memory. Have not gone back to see if the Now() is now working. The problem database is 31.8 MB.

    Any suggestions or ideas!
    Thank You in Advance,
    Jim

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    Open a module in design view, select Tools, References and look for Missing References. Uncheck these try to run the database.
    Francois

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    I do not find "Missing References" under the VBA Editor Tools menu list. I do have "References" in the list. Understand what you were think here - I think. :-)
    Jim

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Compact not completing process (Access 2000)

    Try importing all of the objects in the troublesome database into a new empty database. That will often fix bizzare behavior like this. (How long have you let the compact run - I have seen databases take 30 minutes or more if they were really badly fragmented - but much larger than you have?)
    Wendell

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    I've let it run for a max of about 20 minutes. I'll try lettimg it run longer. I forgot about importing in to a new database. I had seen that a long time ago in a post.
    Thanks,
    Jim

  6. #6
    New Lounger
    Join Date
    Sep 2001
    Location
    Portland, Oregon, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    Just a quick FYI. The Microsoft Common Controls Reference can cause a problem with Format(), Mid(), Left(), Right(), and other functions, even if it is not marked as MISSING. If you can't figure it out, I would try unchecking it, close the dialog, reopen the References dialog, then BROWSE to the Windows/System folder where you can select Comctl32.ocx and then close the dialog, SAVE and try again.

    It's worked for me in the past and I usually look for that one first if I have problems with certain functions.

    BL
    hth

  7. #7
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    Good Morning,
    Last night I left Access try to Compact the database for over 2 hours with no luck. This morning I've tried to Import and Export to a new clean database and both processes would hang-up. I have a total of 37 tables with 11 used for the main part of the database. Most of the other tables are other functions(Reports) and the data in those table is Imported from other sources. Now, with all of that said, I just realized that 5 tables are "Linked" to data sources I do not have on this computer. I created this database for our local PBS station and it is used on a network where all the files reside. This is possibly - probably - why I can not Compact here at home. The one problem I still have is that I can not Import/Export most of the 11 main tables. Access will let me Import 1 file, always the same one, where with the other files I get the Structure and not the data - all is set for Data and Structure.

    So to finish, I think that I have lead several of you astray by my not remembering/realizing that I had "Linked" tables. For that I'm very Sorry. I want to Thank all of you for your assistance on this. I have learned much, mainly to look closer and think more before I try something. Some day I'll learn Access - I hope.

    Thank You All again,
    Jim

    P.S. The problem with Now() I mentioned in the original post, was that I had started to create a Module but had not included the Sub () / End Sub as required. Once I corrected that, Now() worked.

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    Thank You for Your assistance. Please see the latest post to Wendell.
    Jim

  9. #9
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    Thanks for Your assistance. Please look at the Post to Wendell, I think I realize most of the problem now.
    Jim

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact not completing process (Access 2000)

    When you say it won't "let" you import files (I assume you mean tables and that most of the are NOT linked), what exactly do you mean? You said you got the data structure but not the data. Does that happen on all tables, only the linked tables, or what? Do you get any error messages or any kind of message at all?
    Charlotte

  11. #11
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    When I would try to Import from a new database or Export from the old one to the new one, the Import/Export indicator at the bottom left of the screen would just stop at about 80% complete. I will get the select arrow back and would be able to do other things, even open the table I had just Imported - no data. The one table I tried to import only has about 2600 records. I had the table structure but no data. I have tried several tables with the same results, but not all the non-Linked tables. There were no messages of any type. Right now, I'm chalking it up to the fact that this database is used on a network, has "Linked" files and in a few cases references "I" and "U" drives on the network in a few of the VBA modules. I will have to try Import at the PBS station this week. Will let you know what happens. Now, just to add more confusion, I have since created a module to export data from a table in the original database. Am using the following code;

    DoCmd.TransferText acExportDelim, "Peg list test Export Specification", _
    "Peg List test", "A:Peg List.txt", True

    Not sure if any of this helps.
    Jim

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact not completing process (Access 2000)

    You should be able to tell from the dialog box, which table it hangs up on. Try creating a new database and importing all the tables up to that one in the list. Do those come through with data? Then skip that table in the list and try importing the other non-linked tables. If one of them hangs up, try again, skipping that table. you may, in fact, have some corrupted tables or corrupted data. Do either of these machines have OXP or any of its components installed? I haven't seen this problem even with linked tables where the backend link was not on the current machine, but I have seen a machine spend a LOT of time trying to find a network drive that wasn't there.
    Charlotte

  13. #13
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    Good Evening Charlotte,
    I knew the first table that was giving me problems. Had found that yesterday while working on the problem with others guidance I had thought that other tables were doing the same thing. Of course the problem table is the main one where the most important data is - names, addresses, phone#, etc. Because of the importance of the table, I wanted to try to save the data. I created a Query based on all fields in the table and found that it listed about 3/4 of the records and I saw where it stopped. Then I set the Query to give me the records past where it stopped previously. With that information I Exported the data from the two Queries in .TXT format. Tried to Export the one apparently bad record with no luck. Was unable to see anything in the record that looked amiss. I printed out the record and deleted it from the table. And guess what, I can now compile the database. But, I still have a slight problem. In the table-All Volunteers- I'm using Auto-Number for the Volunteer#. I know that everyone says Do Not use Auto-Number, but I have trouble using DMax() to get the next number properly. How is that old saying go "know enough to get myself in trouble". That is why I read NG's and ask questions. I know how to correct the table now and how to get the Volunteer back in the table with his old number - I think I remember. Think this where I will need to do some playing trying to make a second table that will be a back-up duplicate of the All Volunteer table Or maybe try to get DMax() to work properly for me.

    Will close for now, Thanking You for your time and guidance and again Thank all the others that gave me assistance. Just an aside, do I ever need help with VBA. I will study and try first.:-)
    Jim

  14. #14
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compact not completing process (Access 2000)

    Forgot to ask, what is OPX?
    Jim <img src=/S/hosed.gif border=0 alt=hosed width=73 height=24>

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Compact not completing process (Access 2000)

    You didn't hear me tell you not to use autonumbers ... unless you're talking about trying to keep them sequential, which is a waste of time.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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