Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Access 2000, 2003 and 2010 versions

    I have a database that is in .mdb format. I don't know whether it was initially created in 2000 or 2003 but is one of several similar databases that have been working fine on the client's 2007 PC and my 2010 PC.

    We have recently started to experience problems with just one of these databases; during a certain VBA procedure that involves analysiing data from one linked table and storing certain records in a second linked table, at some random point in the loop Access suddenly decides it can't find the storage table when the rst.update is attempted (there are actually three similar tables, to store daily, weekly and monthly analysed data, and the problem seems to occur at random, sometimes completing the daily before hanging on the weekly or monthly). At this point, even if I stop the procedure, I cannot add data manually to the affected table, although I can do so if I open the backend database.

    I am currently at the stage of trying to fix this problem, and am not in fact asking for help with the linking problem just yet (although if anyone recognises the symptoms, I'll be happy to hear from them!). My question arises from a certain stage of my attemtp to fix it myself.

    I tried upgrading the whole set of front-end and linked databases to 2010 (I know that's not generally regarded by this community as a good solution, but did work for another client with linking problems, before I realised that). However, it didn't fix the problem, and now the whole thing runs like a dog. A very old, fat and lame dog.

    So my next attempt is to try and eliminate some kind of corruption in the database, by creating a new, blank database for the front end, re-linking the files, importing the queries and forms (no reports are used as all results are exported to Excel for further manipulation) and using copy and paste on the VBA text. While I suspect the problem to be in the front end, I'll probably also recreate the back end.

    Finally, The Question:

    When creating a new Access .mdb file on a PC running 2010, I get a choice of MS Access 2000 format, or MS Access 2002-2003 format. Does anyone know the difference, and which one is better? My inclination is to go with 2002-3, but since Microsoft seems to have progressively crapped up Access with each new format, I'm wondering if 2000 works better on the principle of K.I.S.S.


    Any thoughts?

    Thanks
    Ali C

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't have (and can't find) anything definitive on this, but I always use the 2000 format. It seems to most stable, and there is nothing from later formats I want to use.
    Regards
    John



  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I believe I've seen something somewhere which seemed to indicate it would be better to run Access2010 against an Access2010 database (.accdb). Unfortunately, I'm not sure. I did have some problems with a client who upgraded to Access2010 Runtime with the frontend and backend databases as Acess2000. We finally upgraded the databases to Access2010, but I'm not sure it just wasn't problems with the Runtime.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Thanks, John and Mark. I think I've gotten to the bottom of the original problem - the amount of data being processed had gradually been growing, and resulted in the back-end file reaching 2Gb even after compacting. (Duh. I kept compacting, but not actually registering the overall size of the file.) Solved at this stage by splitting the tables between two files and running a compact before each analysis (while I work on what to do when a single table looks like getting that big).

    Meanwhile, as everything else had been running OK on 2000, and the client is running 2007, I'm going to stick with John's suggestion of a 2000-format .mdb for this client; but Mark's comment probably accurately reflects the solution I used when my other client had problems. Looks as though it's a matter of horses for courses, and starting at the bottom and working up. (How's that for a bag of mixed metaphors?)

    Thanks again, as always, for your prompt replies.

    Cheers

    AlisonC

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I have just upgraded a client from Access 2000 format to Access 2002-2003 format and all is going well. They used to be on Access 97 format but i have learnt they wish to go to Access 2010 and that won't link to Access 97 format. So i thought i would play it safe just in case MS decide to dump Access 2000 format in the not so distant future.

  6. #6
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    FWIW:

    I use the Access 2002/2003 format. This forces Access 2007/2010 to use JET. The new ACE (.accdb format) has performance issues. Microsoft is aware of this and working on a fix.

    One reason I use the Access 2002/200 format and not teh 2000 format is that I do not support Access 2000. Access 2000 is just to buggy for me. If I use the 2002/2003 format then Access 2000 can't not open the database.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    What are the performance issues Boyd?

  8. #8
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    The performance issues seam to be worse when run on Windows 7 or Server 2003 R2.

    See: Description of the Access 2010 hotfix package (Ace-x-none.msp): August 30, 2011
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by patt View Post
    I have just upgraded a client from Access 2000 format to Access 2002-2003 format and all is going well. They used to be on Access 97 format but i have learnt they wish to go to Access 2010 and that won't link to Access 97 format. So i thought i would play it safe just in case MS decide to dump Access 2000 format in the not so distant future.
    Realistically, I think you should be looking at moving to the newer formats. I know all the reasons for staying with the old formats, but you start missing too much by delaying the move. There are new features and capabilities that you can't afford not to use. As for some possible performance issues between the versions, if performance is that much of an issue, then migrate to a SQL Server Express backend.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by MarkLiquorman View Post
    Realistically, I think you should be looking at moving to the newer formats. I know all the reasons for staying with the old formats, but you start missing too much by delaying the move.
    I think it is a sign that I have become a Grumpy Old Man that I look at most of the new stuff and think that I can't be bothered with any of it. Most of it is just awful. Take split forms - in theory a terrific idea..I create split forms myself often, but the new split form created by the wizard is just a disaster.
    Regards
    John



Posting Permissions

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