Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

record locks

Discussion in 'Information Technology' started by DSharbaugh, Jul 28, 2006.

Tags:
  1. DSharbaugh

    DSharbaugh
    Expand Collapse
    Guest

    I am working with an inherited table (of nearly 900,000 records), which was
    originally developed with a unique key. When I try to add an autonumber
    field to this table in order to have a primary key field, I get the error
    "maximum number of record locks exceeded". There are only about 20 data
    fields and the default record locking option for the db is set at "no locks".
    Can someone explain what my problem is, and how it can be (hopefully) fixed?

    Thanks,

    DLS
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    This should work around whatever is causing the issue:

    1. Close Access.

    2. Make sure there is no LDB file in the same folder as the MDB (spurious
    locking info, or indicating someone else has the file open.)

    3. Open the file exclusively.
    Version dependent, but in A2003:
    File | Open, select file, drop-down the Open button, and choose
    Exclusive.

    4. Uncheck the boxes under:
    Tools | Options | General | Name AutoCorrect
    Explanation of why:
    http://allenbrowne.com/bug-03.html

    6. Compact the database:
    Tools | Database Utilities | Compact/Repair.

    7. Make a copy of the table structure:
    Select the table in the Database Window.
    Copy (Ctrl+C).
    Paste (Ctrl_V).
    Choose Structure Only.

    8. Open the new, empty table in design view, and add the AutoNumber field.

    9. Populate the new table with an Append query.
    (Apppend on Query menu, in query design.)

    10. After verifying the data is correct, remove any relations on the old
    table.

    11. Delete the old table.

    12. Compact again.

    13. Rename the new table with the old name.

    14. Recreate any relations you deleted at step 10.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "DSharbaugh" <DSharbaugh@discussions.microsoft.com> wrote in message
    news:38869E88-48B8-4854-A4FA-AC49276BA6EA@microsoft.com...
    >I am working with an inherited table (of nearly 900,000 records), which was
    > originally developed with a unique key. When I try to add an autonumber
    > field to this table in order to have a primary key field, I get the error
    > "maximum number of record locks exceeded". There are only about 20 data
    > fields and the default record locking option for the db is set at "no
    > locks".
    > Can someone explain what my problem is, and how it can be (hopefully)
    > fixed?
    >
    > Thanks,
    >
    > DLS
     
  4. ChrisM

    ChrisM
    Expand Collapse
    Guest

    Hi Allen,

    Just read with interest your article on AutoCorrect. I'm using Access2000,
    and was unaware of this feature.
    I have built a fairly large database, that, by default, has this turned on.
    I have been experiencing some of the problems you mentioned, but have
    generally managed to fix them without realising what has caused them.
    May try what you recommend, and import all my objects into a new 'fixed' DB.

    Thanks for that.

    Cheers,

    Chris.

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:eTLSyViqGHA.4992@TK2MSFTNGP05.phx.gbl...
    > This should work around whatever is causing the issue:
    >
    > 1. Close Access.
    >
    > 2. Make sure there is no LDB file in the same folder as the MDB (spurious
    > locking info, or indicating someone else has the file open.)
    >
    > 3. Open the file exclusively.
    > Version dependent, but in A2003:
    > File | Open, select file, drop-down the Open button, and choose
    > Exclusive.
    >
    > 4. Uncheck the boxes under:
    > Tools | Options | General | Name AutoCorrect
    > Explanation of why:
    > http://allenbrowne.com/bug-03.html
    >
    > 6. Compact the database:
    > Tools | Database Utilities | Compact/Repair.
    >
    > 7. Make a copy of the table structure:
    > Select the table in the Database Window.
    > Copy (Ctrl+C).
    > Paste (Ctrl_V).
    > Choose Structure Only.
    >
    > 8. Open the new, empty table in design view, and add the AutoNumber field.
    >
    > 9. Populate the new table with an Append query.
    > (Apppend on Query menu, in query design.)
    >
    > 10. After verifying the data is correct, remove any relations on the old
    > table.
    >
    > 11. Delete the old table.
    >
    > 12. Compact again.
    >
    > 13. Rename the new table with the old name.
    >
    > 14. Recreate any relations you deleted at step 10.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "DSharbaugh" <DSharbaugh@discussions.microsoft.com> wrote in message
    > news:38869E88-48B8-4854-A4FA-AC49276BA6EA@microsoft.com...
    >>I am working with an inherited table (of nearly 900,000 records), which
    >>was
    >> originally developed with a unique key. When I try to add an autonumber
    >> field to this table in order to have a primary key field, I get the error
    >> "maximum number of record locks exceeded". There are only about 20 data
    >> fields and the default record locking option for the db is set at "no
    >> locks".
    >> Can someone explain what my problem is, and how it can be (hopefully)
    >> fixed?
    >>
    >> Thanks,
    >>
    >> DLS

    >
    >
     

Share This Page