Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2006
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    My Splitter Isn't Splittin' (Office 2000 and 2003)

    Currently I'm creating a database for our company, which is national, and therefore the database needs to be placed onto shared server drives. I'm running Access 2003 but most people in the company are using Access 2000, thus I suppose you can consider the database an Access 2000 database. Anyways, in order provide the best security measures I was planning on backing-up the database, splitting the database, then creating user-level security on both the front- and back-ends of the database itself. However, the database just does not want to split.
    The Database Splitter wizard sometimes creates a back-end file but it never creates a front-end. I have tried it four times and every time I get two error messages:

    "Subscript out of Range" and "Invalid procedure call or argument"

    What can I do? What must I do to continue my project? I'm wondering if it is because I'm running a 2000 file through 2003 but I doubt that is the case. Maybe it's becuase of how many queries and reports there are, but who knows?

    help!

    -Adam

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2003)

    Welcome to Woody's Lounge!

    I agree that using a Access 2000 format database in Access 2003 is probably not the cause of your problem. Most developers still use the Access 2000 format in Access 2002 and 2003, since it's smaller and more stable than the 2002/2003 format.

    It's not really difficult to split a database manually:

    1) Create a backup copy of your database.
    2) Create a blank new database. This will become the backend.
    3) Make sure that the Name AutoCorrect options in the General tab of Tools | Options are off.
    4) Import all tables from your database into the new one.
    5) Compact and repair this database.
    6) Close the new database.
    7) Move it to the correct folder (if necessary)
    8) Open the 'old' database.
    9) Delete all tables.
    10) Use File | Get external data | Link tables to create links to the tables in the new database.
    11) Compact and repair this database.
    12) You now have a frontend.

  4. #3
    New Lounger
    Join Date
    Oct 2006
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2003)

    Why would you need to turn of the "Name AutCorrect" options? I'm just wondering.
    Also, thanks... I never really thought of manually linking databases to create your own back-end.

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2003)

    Hi Adam,

    I habitually turn off Name AutoCorrect in all my databases. It has a negative impact on performance, it increases the chance of database corruption, and it causes unexpected/unwanted behavior; in Access 2000, for example, reports tend to lose their page setup settings if Name AutoCorrect is on.

  6. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2003)

    Hans,

    Instead of creating a new backend, I'd create a new frontend. Here's why.

    Under normal circumstances, when you create relationships with enforced R.I. within a database, Access creates hidden indexes, the name of which is the combination of the 2 tables. So if you had tblVoucher and tblDetail, the hidden index would be "tblVouchertblDetail". Importantly, Access will use this index when optimizing queries. However, when you import tables and relationships, the hidden index name is a system-created block of seemingly random characters (AB39D83F0219GA981). While R.I. is still enforced, Access apparently won't use this hidden index when optimizing queries. As a result, you may take a big performance hit. I discovered this while trying to figure out why a delete query (with cascading deletes enabled to a very large child table) of just a few records in a parent table was taking such a long time (like about a minute). After deleting/recreating the relationship, the delete was almost instantaneous.

    That's why I'd create a new frontend, so as not to disturb the relationships. If you do create a new backend, you can manually do this by deleting and then recreating all relationships; or you can use my "Liquorman Utilities" to recreate the indexes and relationship in the new db, if you have a good db as a model.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2003)

    Thanks for this tip!

  8. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2003)

    Mark,

    Would you use the same steps in creating the frontend that was described by Hans to create the backend? I'm trying to read the Q&As on splitter to get a really quick lesson so that I can tackle a project that I have been tasked with.

  9. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2003)

    A frontend is a database with all database objects except the tables (i.e. queries, forms, reports, macros, modules), plus links to the table in the backend. Instead of copying the old database and deleting the tables, you can create a blank new database and use File | Get External Data | Import... to import all queries etc. from the old database into the new one, and then use File | Get External Data | Link Tables... to create links to the tables.

  10. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2003)

    Approximately the same. Just create the new frontend database, then import everything (forms, queries, reports, modules) except tables into it. Then link to the backend. When all done, and everything is working, delete all these objects from the backend, then compact/repair.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2

    I would suggest a slight variation to the previous two suggestions.
    Link to the tables first, then import all the queries, forms etc.

    On a number of occasions where I have imported queries without first linking to the tables, all the queries have become corrupted with lots of "exp1" etc, and I have had to delete them all are import again.

    This was probably my mistake, in that I think I started to try to use the db before I had linked to the tables (forgetting I had not done it yet.)

    So I think it is safer to do the tables first.
    Regards
    John



  12. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2

    >>Link to the tables first, then import all the queries, forms etc.<<

    Good point.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: My Splitter Isn't Splittin' (Office 2000 and 2

    Yes, good point.

Posting Permissions

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