Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904

    Upgrading Access 2000 Database Issues


    We have an old Access database that someone created many years ago in Access 2000. They set it up with Security (companion MDW file).
    We are trying to upgrade it up to Access 2007, and are unable to do so. No matter what I do, I keep getting error messages that say "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again."

    I get this message if I open the Access 2000 database directly in Access 2007 and try to convert it or even compact and repair it. I also get the message if I try to run some of the commands on it, or try to "Decode it".
    The database size is about 142,000 KB, so it is not approaching the 2 BG limit.

    I researched it, and it looks like any number of things can cause this error.

    I tried a myriad of things, such as:
    - removing security
    - decoding it
    - cleaning up an compiling all VBA code
    - deleting all data (it will not let me delete the data from certain tables, it looks like some relationships are set up)
    - compacting and repairing
    - importing the objects into a new database

    None of this has made any difference in getting rid of those errors (as matter as fact, trying to do some of those things resulted in the error messages popping up). I still get these errors when trying to use it as an Access 2000 database in 2007, or whether I try to convert it.

    Is there any way to find out what the problem is so I can convert it?
    Anything like some sort of tool that will analyze a database for conversion?
    Or does anyone have any advice at all?

    Thanks

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Do you have any version of Access < 2007 to try?

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, I still access to Access 2000 on one machine (which is one of the machines I have been working from in trying to "fix" this issue).
    I actually have access to 2000, 2007, and 2010, on various machines.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Removing the security can be a bit trying. Are you sure you have eliminated the security MDW? Can you *import* this file into a NEW ac2000 MDB?

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Removing the security can be a bit trying. Are you sure you have eliminated the security MDW?
    You can say that again! I wasn't sure it worked, but it creates a "backup" copy. When I was done, I could log into one without any security, but the other one still had security on it. But the one that didn't have security on it still had those issues.

    Can you *import* this file into a NEW ac2000 MDB?
    I tried that, but it won't let me do it without removing security (it says I do not have permission).

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

  7. #7
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, that was the one that I had found and tried. It creates a "backup" of the database and the database. I can't remember which, but after that one of them did not need security to log in anymore. However, I was still getting those error messages on that database.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can *import* from "unsecured.mdb" w/ac2000 without errors, right? But, you cannot *import* from that same MDB without errors, right? It seems like a dumb question, but I have to ask, sorry.

  9. #9
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    In general, we can import from other unsecure Access databases.
    However, when we try to remove security and it creates two copies of the database (one secure, and one without security), we are unable to import objects from either of these two.

    Do you think the issue could have anything to do with the fact that the original programmer set up table relationships?
    I usually never do that, and just do all my relationships in queries.

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Allan,

    I tried going about this from a different angle, and think I may have found something that might work. If I go into the Secure Database, it appears I can export each object one-by-one to a new, blank database. I tried the first 10 tables, and it appears to be working thus far.

    The only thing is, there are about 30 tables, 200 queries, 15 forms, and 50 reports. I obviously do not want to export each object one at a time. Is there a way to export all of the objects of the database at once in Access 2000, maybe via VBA?

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm afraid I started with Access XP (2002) so I don't have an answer for you. You say when you remove security, it creates two db's. The instructions I pointed to:
    Open Access and login as a user with administer permissions then open your file.
    In your file grant full permissions to all objects to the Users
    group.

    Close Access and then open using
    the default System.MDW workgroup file. You
    might need to use the workgroup administrator to re-join that
    group first.

    Open a new empty file and
    then use File - Get External Data to import
    all
    objects from your original file. The resulting file will
    be identical to one
    that never had security.

    All of your users will need to rejoin
    System.MDW as their default if that is not
    already the case.
    ...talks about *you* creating a new db and *importing* everything not the system doing it for you. Are you sure this is how you did it?

  12. #12
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    To tell you the truth, I have been messing with this for so many days now and tried it up and down many different ways. When I had did it last week, I was working off the same instructions you linked to, and somehow go it to create two databases, one of which was insecure, but still had all the error issues, and I was unable to import/export to/from it.

    I have never used Access security workgroups before, so I admit that am not sure if I am doing the steps in the first two paragraphs correctly (doesn't really provide the details on how to actually do it). Also, the person who gave me Access is the database owner, and not the database creator, and really not that technically knowledgeable. She claims she gave my account full user access. I don't know if that has the same rights as being an Admin, or if I need to be an Admin to complete those steps.

    With all the issues in trying to remove security, neing able to login and export the objects seems like the most promising breakthrough I have had. I just don't feel like exporting a couple hundred objects manually! I got to believe there is a way to do it via VBA. Do you think it would be all right to post a new question asking that?

  13. #13
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614

  14. #14
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    amrut,

    That looks like it could do what I want, the ExportAll Utility (here is the description). The description looks like it is describing the EXACT situation I am encountering.
    One of the things that hasa always bothered me is the fact that Microsoft did not include a way to do bulk object exports. You can only do one object at a time. Sometimes you can do a work-around by importing the objects into another database, but this is not possible if you want to get objects from a workgroup-protected database into an unprotected database.
    ExportAll is an add-in that does just what its name says. It lets you export multiple objects at one time. The only thing I haven't figured out yet is how to carry the relationships along with it. But, hey, with all the time this will save you, you can rebuild the relationships and still be ahead of the game.
    Thanks! I'll see if my workplace will let me download it all try it out when I am in the office tomorrow.

  15. #15
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Good news! It looks like the ExportAll utility works (once we figured out where to put the Add-In - its been so long since I had Access 2000, it took a little while to find the correct AddIn folder to drop it into).

    Thanks everyone!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. HR Database/ Access 2000/ Long time since i used Access.
    By reception in forum Database Design
    Replies: 3
    Last Post: 02-11-2014, 05:08 PM
  2. Replies: 7
    Last Post: 02-04-2014, 01:59 PM
  3. Replies: 1
    Last Post: 11-01-2013, 11:13 AM
  4. Replies: 15
    Last Post: 07-17-2013, 11:29 AM
  5. Replies: 3
    Last Post: 08-31-2010, 12:44 PM

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