Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Front end/back end (Access 2000)

    I have a database that I want to split into front end/back end. The database is secured with an mdw file. I have created a new database, secured it with the mdw, impoted the tables from the original database. I have made a copy of the original database, deleted the tables and then linked to the tables in the newly formed back end. Should be fine, nothing code wise should have changed. BUT the performance is terrrible, there's a switchboard that loads a form whose recordsource is a fairly big query; it must take 10x longer to load. Why should this be the case? The front end and back end are in the same folder. Am I doing something wrong?

    Edited to add that I've just gone through the process using the Database Splitting wizard and from clicking the button on the switchboard it's taken 5 mins and counting.....

    TIA and somewhat confused
    Mark

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

    Re: Front end/back end (Access 2000)

    Did you remember to turn off Name Autocorrect (Tools-->Options-->General)? That can have a big impact. Also, you need to turn off subdatasheets on each table, front *and* back end. There is a big performance hit from subdatasheets, and unfortunately, there is no database level setting to turn them off. This was discussed in this thread. <!profile=HansV>HansV<!/profile> posted code to turn off subdatasheets in <post#=314780>post 314780</post#>, which might help as well.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Front end/back end (Access 2000)

    Charlotte

    Thanks for that. I've had a look and Name Autocorrect and track and perform were checked. I'll try splitting with them unchecked. Just curious though, what does this do and how does it impact on the performance? I'll have a look at the tables for subdatasheets and I'll check out HansV's code.

    Mark

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

    Re: Front end/back end (Access 2000)

    Track name AutoCorrect info stores information about all changes you apply to the names of database objects (tables, queries etc.), fields, controls etc. If you check Perform name AutoCorrect, Access will try to propagate these changes throughout the database. Say that you change the name of a table from tblCustomers to tblCustomerInfo. Any query, form or report based on tblCustomers will be broken. Perform name AutoCorrect will modify all these to refer to tblCustomerInfo instead of tblCustomers. In theory, this is a great idea, but in Access 2000 and 2002 (I don't know about 2003, I don't have that), all that tracking and checking causes siginificant overhead; moreover, the implementation is buggy, it can cause database corruption.

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Front end/back end (Access 2000)

    Hans

    Thanks for the explanation. Makes sense to switch it all off.

    Just been looking at the code for removing subdatasheets. It will need running at the back end too I guess?

    Mark

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

    Re: Front end/back end (Access 2000)

    Yes, you must turn subdatasheets off in the front end AND in the back end separately.

Posting Permissions

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