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

    Unanswered: Subform / Primary Key Problems

    I have a subform in my form which displays data pulled from a table in my oracle database.

    If the table in oracle is assigned a primary key, the subform loads the data very slowly line by line and holds up the application running smoothly. Data can still be deleted directly from the subform.

    If the table in oracle is NOT assigned a primary key then the subform loads the data quickly and works fine. But the problem here is that data cannot be deleted directly from the subform.

    Can anyone think of a reason why assigning the primary key to my table is effecting the subform so badly?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Perhaps it isn't indexed, relationships are not setup correctly, or you're not using criteria in the subform.

    If you're using ODBC, it could also be an Oracle driver related issue. I believe MDAC controls driver issues. Oracles a different beast so to speak. It doesn't play as nicely with MSAccess as SQL Server does.

    I'd recommend running any "repair" type commands on the oracle table to make sure there's not a table-side issue (similar to the dbcc command for SQL Server.) I'd also test opening the relational table in Oracle and compare that with opening it in MSAccess (perhaps designing some queries using the relational join.)

    Otherwise, to answer why it's so slow when you're assigning a value to the primary key, we'd need to see what kind of code you're using or how your subform is setup. If you're simply setting the DefaultValue of the primary key on the subform, there shouldn't be a problem. If you're trying to LINK 2 tables together for the recordsource of the subform to somehow get/assign the primary key, not a good idea (which is often why you can't delete records in the subform). The recordsource for your subform should only be based off the relational table which should have criteria in it for the relational field such as =Forms!MyMainFormName!MyRelationalJoiningFieldName (note: there are a few exceptions to this rule but normally this is the least problematic and best method.)
    Last edited by pkstormy; 09-25-09 at 22:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2009
    Posts
    12
    Thanks for the advice pkstormy.

    The real weird thing is that when I have the primary keys all set up correctly and it runs really slow, if I click the design view button then go back to the form view it seems to run brilliantly with no delays or anything.

    Really confused by this!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Did you compile the code? (not just compact/repair the mdb).

    Otherwise, you may want to delete/re-link the tables. Without seeing your design, it could also be something as simple as a recordset opened in code and there's no rs.close or set rs = nothing type statement to close the recordset or often, it's a DefaultValue or DLookup expression which isn't correct.

    Other than indexing, having/not having a primary key field should not impact the form speed other than you're trying to populate something into that primary key that it doesn't like.

    Is the recordsource for your subform ONLY based on the relational table or are you trying to link in the main data table as part of the recordsource for the subform?

    You want the subform only based on the relational table, otherwise I've seen symptoms on what you described.
    Last edited by pkstormy; 09-28-09 at 20:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2009
    Posts
    12
    I have deleted/re-linked the table but has no effect when the table already has a primary key set.

    This is the code I am using to populate the subform...

    SELECT *
    FROM COMMENTS
    WHERE (((COMMENTS.ID)=FORMS!DBComments!ID_CMB))
    ORDER BY ID, BGN_DATE DESC , TIMESTAMP;

    The primary key on the Comments table is a key combined of both the Timestamp and a column called Initials.

    I tried taking Timestamp out of the order by but it had no effect.

    I do not close any of my record sets now that you mention it, but I have tried closing them but they do not change anything. The subform still loads very slowly and refreshes itself alot.

    Thanks for your help so far. I assume your based in America or something as your replys always come at some rediculous hour of the night here in the UK.

  6. #6
    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.

Posting Permissions

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