Results 1 to 6 of 6

Thread: MSACCESS QUERY

  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MSACCESS QUERY

    Anyone who can help. I have a MSACCESS database that is significant in size (137Mb). This database has not been compacted for some time and when I try to compact it I receive an error "records can't be read, no permission on {database name}.mdb". I have checked the permissions and these are all assigned. Taking advice from Microsoft I was advised that the database may be corrupt and that I should copy each table into a new database, reassign the relationships and then compact the database. I proceeded with this process and all but one table were able to be exported (note the database only contains tables). The error I get with this particular table on export is "The microsoft Jet engine stopped the process because you or another user are attempting to change the same data at the same time". This error can not be correct as I am on a stand alone machine off the network. It may be worth noting that the database normally works across a network. If anyone can shed some light on these errors or help with compactingthe database it would be much appreciated.

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSACCESS QUERY

    If you have Memo field(s) in the table, it's possible the corruption could exist in them. Compact/Repair and Jetcomp are not very good at correcting Memo field references. To date I've had no success in locating corrupt memo field programatically either. As long as the table is not too large, you can visually scan the memo fields looking for an indication of corruption. (If memory serves me, corrupted memo fields begin with ???? or some such). Should you discover such a record, the only solution I've ever found is to delete and re-create the record. Incidentally, I have found it useful to open the table, locate the cursor in the Memo field and hold down the down arrow thus moving from record to record. If corrupted, Access will not be able to get past the field in question and will halt at that point. Hope that helps.
    p.s. Sure sign of a corrupt memo field, you can't modify it.

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSACCESS QUERY

    Brian

    Thanks for your reply. I have not yet tried the solution offered but will let you know how it goes.

    Mark

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSACCESS QUERY

    Brian

    Thanks for your help. It worked!! Very easy - only found 4 out 15000 records that were at fault and contained the wording #error. Deleted these and off we went. Compacted a 135Mb Database down to 9.2Mb!!

    Great Thanks
    Mark

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSACCESS QUERY

    Never ceases to amaze me the amount of bloat in non-compacted databases. You should see an increase in performance as well. It's a good idea to compact on a regular basis (listen to me, I haven't compacted mine for over a month). <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Must do that tonight. <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

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

    Re: MSACCESS QUERY

    Just for the record, Access 2000 is even more prone to bloat than its predecessors because of the unicode thing. And, of course, it doesn't compact down as far for the same reason.
    Charlotte

Posting Permissions

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