Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107

    Unanswered: linked table performance

    hi all

    I did some developments where I had both front- and backend as mdb-file connected by linked tables. the performance was in general very bad so I switched over to an SQLServer backend solution. Has anyone ever experienced an Access/Access solution with an acceptable performance?

    thanks
    bachatero

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Performance issues is a broad topic, but you can start some of these:

    did you apply indexes to the tables?,
    were they the right indexes (too many indexes can be as bad as too few)
    have you use appropriate data types?
    Have you used referential integrity correctly?
    what were the performance issues? -
    were the forms too slow?
    are you using too many combo boxes on a form?
    are you accessing all the data, instead of a subset of data
    for a data entry form have you set the data entry properties of the form so you get a blank data entry form?
    Do you have a slow network?

    The list is as long as you want it to be!

    You can get reasonable performance out of an Access/Access solution if you design it well - but you will never get the performance you can get from a true client/server solution such as Access/SQL Server
    Justin

  3. #3
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    *g*
    indexes - naturally
    appropriate datatypes - for sure no memo-fields
    referential integrity, relationship model - absolutely (usually no textkeys)

    in general the performance of forms is bad

    sure I'm using comboboxes and subforms (though not to an enormous extent)

    the network is ok .. not the fastest but should be enough for the task

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    With form design the trick is to load what you need to and delay the rest until the user requests something.

    Instead of opening a form that loads all the data, open the form as either a data entry form (Me.DataEntry = True) or only with one record (SELECT TOP 1 ....). Let the user request through a query form the data they want to work with.

    Combo boxes can be a big problem - each combo box has to be filled by a recordset, so make sure the recordsets are small. Make sure you don't have too many, any more then 2 or 3 and i would look at your form design again. You can split a big form up into smaller forms - it would mean more managment on your part though

    You can use the recordset property (later versions of access only, XP onwards I think) of the combo box to open a recordset once and assign many times to combo boxes or any other control with a recordset

  5. #5
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    thanks for these tips ..

    but it doesn't answer my question. The point is, if to avoid access/access solution in general if more than just a mere table should be displayed at a form

  6. #6
    Join Date
    Jun 2009
    Location
    Holland
    Posts
    1

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Unbound forms is the only answer to great performance with Access back ends, but if you are heading down that road, SQL Server is a far wiser option.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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