Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Unanswered: Subform Refreshing Slowly Alot

    I have a form with a subform in it which displays data from my Oracle database.

    It has all been working fine but now I'm coming up against an annoying problem.

    I have added a little bit of code which deletes which ever record you double click on in the subform from the database.

    Ever since I have added this my subform seems to be running extreamly slow. It constantly refreshes itself, and does so very slowly. It holds up everything else.

    Also, when ever another widow is open and displayed ontop of the subform, it has to refresh to get the data back.

    Very strange issue. Have tried removing all of the deletion code but the problem still occurs.

    Any ideas?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you're having problem with code, it's probably a good idea to actually post the code so we can look at it! Is this Oracle database on a network? I ask because this type of problem really sounds as if it could well be network related rather than a primary Access problem.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Did you debug/compile the (frontend) code?

    Did you compact/repair the (frontend) mdb file?

    My first guess is that it's related to the Oracle table itself and messed it up someway (you can double-check this by just opening the table itself in the frontend (ie. not through a form but open just the table). - if it opens a lot slower than before, then it's not form related and IS table related which you must look at Oracle commands to try and fix.) You can also try dropping (deleting) the table and re-link it into the frontend (as it could be ODBC related) but I'm still guessing you'll need to run some repair commands against the Oracle table. Again though, open just the table (not the form) to see if it's table related or form related. If the table opens slow or is sluggish navigating from record to record, continue....

    You may also want to run any commands on the Oracle table itself (such as a dbcc command for a SQL Server table) to essentially repair the table. If you deleted records in the table (and it was a relational table), it's possible the record identifier got messed up which is causing the new delay. Running some kind of "repair" command on the relational table (I don't know what it is for Oracle), might fix any problems where a record got deleted and messed up the record identifier somehow. As a last resort, create a new relational table, copy all data to it, then delete the possibly corrupt one and rename the new one (double-checking that your relationships and indexes are created correctly.)

    You may also be able to just re-create your relationship on the table in Oracle and fix it this way. You can also "re-build" your indexing fields as it's possible the indexing got messed up (again, not sure what the command to do this is on an Oracle table.) Indexing on the table is good probability of the problem if you deleted records from the table (but I don't work much with Oracle data tables.) I'd try re-indexing the oracle table (and maybe any other relational tables which link to this table.)
    Last edited by pkstormy; 09-20-09 at 02:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Sep 2009
    Posts
    12
    For anyone reading this with the same problem, I managed to fix the issue.

    In the properties of my subform, Allow Additions and Allow Edits were set to No. I have these now set as Yes and the subform loads quickly. In the VBA code I have set additions and edits to false.

    No idea why this was slowing down my subform but it was.

    Hope this helps anyone with a similar problem.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Wierd - I've set the AllowEdits/AllowAddtions property to true or false in code quite a bit and it's never really impacted the loading speed. Other than possible Oracle linking related issues, I'm wondering if it's related to when you tried setting the AllowAdditions and AllowEdits properties to True or False in the OnLoad or OnOpen event of the form/subforms.

    I usually open the form this way and then change the allow additions/edits. I do this often and there's isn't any speed difference.

    docmd.openform "MyFormName"
    Forms!MyFormName.AllowAdditions = False (or True)
    Forms!MyFormName.AllowEdits = False (or True)

    This allows me to add my "testing for admin" code before opening the form so I know whether to set these values to True (for admin) or False (non-admin) when they click the button.

    I also don't set these in the OnLoad event of the form itself but instead, in the button event which opens the form.
    Last edited by pkstormy; 10-16-09 at 02:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Sep 2009
    Posts
    12
    Yea, it is wierd.

    All I can say is that it works!

Posting Permissions

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