Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error When Splitting Database (Access 2000 SR-1)

    I finally decided to take everyone's advice from this forum and split my database. I followed the steps in Special Edition using MS Access 2000, so I have a good backup. I also compacted and repaired the database before trying to split the database.
    I've tried three times to split the database, each time I get the following error. "Record(s) cannot be deleted. No delete permission on "MSysObjects". When I click on the OK button, I get a second pop up box, "Invalid procedure call or argument". I then get kicked back to the Database splitter window.
    I searched this forum but couldn't find anything relating to this error. It is almost as if there is something open in the background. The only window I have open is the database window, with tables window selected. Has anyone run across this and what might I be doing wrong?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Error When Splitting Database (Access 2000 SR-1)

    Is there any security on this database? You also might want to try holding down the shift key when you open the database. This will prevent any hidden forms from loading that are specified in the database startup option or in an autoexec macro.

    If all that fails, I'd just copy the database over to another database name, let's say this will be the new "backend". Then just go in to the new backend database and delete everything except the tables and relationships. Then compact. Go back to old database (now the frontend), and delete all the relationships (important), then all the tables. Compact. Then re-add the tables by linking to backend.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Manually split the database (Access 2000 SR-1)

    I've never used the database splitter, so I can't tell you what it's doing, but that doesn't sound right at all. The MSysObjects table is a built-in system table and would never be deleted in any case.

    Try this as an alternative: Close your database and make a copy of the file. Now open the database and delete all the non-MSys tables. You have to delete the dependent tables first if you've enforced referential integrity without cascading deletes.

    Once you get all your tables deleted (except for any that really *must* be in the front end for multiuser apps), select File-->Get External Data-->Link Tables. That will allow you to select the other copy of your database and then to choose all the tables to link. Once again, either turn off the switch that allows you to view system objects or don't select them for linking.

    After you've done all that, you should open a code module and compile the code to make sure you don't have any code that will break on linked tables. If it compiles, try running it. Some things will compile but will error in use, like trying to perform actions and linked tables that are only valid on local tables.
    Charlotte

  4. #4
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error When Splitting Database (Access 2000 SR-1)

    Thanks Mark, I will try this the next chance I get when the database is not used.

  5. #5
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manually split the database (Access 2000 SR-1)

    Thanks Charlotte, I'll try this along with Mark's suggestions the next chance I get.

  6. #6
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manually split the database (Access 2000 SR-1)

    Hi Mark and Charlotte,
    I tried manually splitting the database. I have the front end and back end separated, but as I was deleting the relationships from the front end, I received the same error in MsysObjects that I mentioned before. In fact I can't change anything about this one relationship.
    It is currently One-to-One, but I can't make it anything but that. One of the tables was linked to two other tables in the same manner, but I didn't have any trouble deleting those relationships. I deleted all the other relationships and all the other tables, except these final two tables and the one relationship, then compacted again, but it still didn't work. Should I go ahead and delete the final two tables or should I try to fix this some other way?
    One other thing, this database started life as a replicated database so at one time the primary keys were generated randomly, I don't know if that has anything to do with the error or not. It has since been recombined (unreplicated, whatever) and is now shared over a network.
    Thanks for your help.

  7. #7
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manually split the database (Access 2000 SR-1)

    Usually when you start receiving an error in MsysObjects it indicates a database that is either corrupted or on the brink of corruption. You can attempt to repair the database but this usually does not fix the problem. If you have a recent backup, you may want to revert to it. You can try to import all the object to a new empty database. You may can recover that way.

    Good Luck,
    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

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

    Re: Manually split the database (Access 2000 SR-1)

    I suspect the problem is that your database still thinks it's replicated, although you may also be suffering from a corruption problem. About the only way to solve this problem is to create copies of your tables (using queries or whatever) that get rid of the replication fields (they are normally hidden but you can see them if you turn on the "See System Objects" flag). If you really don't want to mess with that - it isn't a fun process - consider creating a new front-end by importing everything but the tables into a new database, and then linking to the tables in a copy of your original database (ALWAYS make a copy). That way the relationships will continue to exist in the back-end, and you can delete all other objects besides tables in the back-end.
    Wendell

  9. #9
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manually split the database (Access 2000 SR-1)

    Just over a month later, I have finally got the chance to try your suggestion about creating a new front-end. So far it seems to work ok. We'll see tomorrow when I post everything to network. Thanks for the suggestion, nothing else I tried would allow me to delete the stubborn relationship.

Posting Permissions

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