Results 1 to 10 of 10
  1. #1
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442

    Why am I so stupid....

    I have a mental block... or I'm stupid...

    So here is the situation. The main form of my database remains open all the time. When the main form is loaded it links in a bunch of SQL tables. This is the 'default', for almost everything in the database a linked table has no discernible effect on the speed at which my database operates.

    When I open a second form (frmSlidingFeeScale) I import some tables from a SQL database, for this particular part of the database I need the tables to be local for the sake of speed. That part works fine. The problem I'm encountering is when I close the second form (frmSlidingFeeScale). I want to drop the imported version of these tables, then re-establish the linked version. I've tried perhaps a dozen different methods to do this and I just can't seem to get it right. This is my current code, there are two tables that the function RELINKTABLE will not work on (RELINKTABLE drops the imported table and re-establishes a linked version instead) because they are used in combo boxes/list boxes on frmSlidingFeeScale.

    The do loop is not correctly waiting until frmSlidingFeeScale is closed before firing off the relinks. The only two solutions I've found that work without fail so far are:
    1. Put in a timer to wait x many seconds to close the secondary form


    2. Set the rowsources for the combo/list boxes on frmSlidingFeeScale when I open it

    I would prefer to get away from these two options If I can, but I just can't see to get the isloaded to fire correctly.

    If anyone has a suggestion I am all ears.
    RELOAD is a field on the main form, it is populated with the text value SF when I close my secondary form (frmSlidingFeeScale) in the ON CLOSE event.

    Code:
    If RELOAD = "SF" Then
        Do Until CurrentProject.AllForms("frmslidingfeescale").IsLoaded = False
        Loop
        Call RELINKTable("Enrollment", 0)
        Call RELINKTable("Individual", 0)
        Call RELINKTable("AdjudicatedClaim", 0)
        Call RELINKTable("ServiceCode", 0)
        Call RELINKTable("Agency", 0)
        Call RELINKTable("Business", 0)
        Call RELINKTable("SlidingFeeScale", 0)
    ElseIf RELOAD = "RA" Then
        cmdImportLinked_Click
        EvaluateButtons
        RELOAD = Null
    End If
    RELOAD = Null

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Maybe there are more options:
    -too close to the issue
    -not a good day (onset of man flu...)
    -etc.

    Untested but curious, why
    Code:
    Do Until CurrentProject.AllForms("frmslidingfeescale").IsLoaded = False
        Loop
    Is DoCmd.Close acForm, "frmslidingfeescale", acSaveYes and option??

    RELOAD = NULL in the ElseIf seems redundant.



  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Instead of re-importing the data, why not just copy the data in the linked table to a temporary local table with the same structure, work with it in the local copy, then delete all the temporary data when the form closes? You don't need to delete the temporary table each time - just the data in it. This way you don't need to worry about relinking - as long as the main form is open, the links are there.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I agree with John_G, use the temp tables and delete the data as needed. You could also enable the Compact/Repair on close option so the db size does not grow too large.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I wanted to avoid temp tables if I could.

    Running the report from linked tables alone (The source tables are properly indexed) takes about 5 minutes.
    Running the report from purely local tables (if I import them at run time) takes about 25 seconds, including the import.
    Deleting/Appending to temp tables the report takes approximately 1 minute to run, including the deletion/append.

    So yes, the temp table approach would work but the run time is considerably longer and this is not a big dataset, the biggest table has about 20k rows with about 30 fields.

    I am not entirely sure where I will go with this but I am still looking for a 'wait until this report or form closes before continuing' code set. Everything I have tried so far fails to re-link the tables because I can't detect when an object is 'fully' closed.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    If RELOAD = "SF" Then
        Do Until CurrentProject.AllForms("frmslidingfeescale").IsLoaded = False
            DoEvents    
        Loop
        DoEvents:DoEvents:DoEvents
        Call RELINKTable("Enrollment", 0)
        Call RELINKTable("Individual", 0)
        Call RELINKTable("AdjudicatedClaim", 0)
        Call RELINKTable("ServiceCode", 0)
        Call RELINKTable("Agency", 0)
        Call RELINKTable("Business", 0)
        Call RELINKTable("SlidingFeeScale", 0)
    ElseIf RELOAD = "RA" Then
        cmdImportLinked_Click
        EvaluateButtons
        RELOAD = Null
    End If
    RELOAD = Null
    Shot in the dark. Try this. Maybe also a couple of DoEvents between calls.
    Last edited by davegri; 12-10-2016 at 02:01 PM. Reason: afterthought

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I've tried variations of that davgri, but thanks for the effort.

    The way it's working now is with temp tables as john_g suggested. I don't like it but I've gotten it down to about a 25 second run time so it's fairly comparable outcome when compared to having the tables be local tables.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you are worried about bloat, you can put your temp tables in a local temporary db and link to that instead.

    Another option for populating, although I don't know if it will be quicker, is to make them permanent and rather than using 'full' delete and append queries, use an update (where records have changed), append (where records are not in local table) and delete (where records are in local table but not in sql table) instead

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Unfortunately I do not think that would work or I would be performing this 'update' to the local tables every time I run a report because this database is highly changeable and the reporting needs to be up to the minute, I don't think a series of append/delete queries would be as efficient as it is now (except for this report).

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I might not have this right but in reading your OP, what if y0u keep the linked SQL tables and just renamed the SQL and Local tables before importing data? Then when the reports are done, rename the local and SQL tables back to original. (tblSQL1 is the linked table)

    Delete/Export data from tblSQL1 to tblLocal1_hold
    Rename tblSQL1 to tblSQL1_hold and rename tblLocal1_hold to tblSQL1
    Run reports or whatever
    Rename tblSQL1 back to tblLocal1_hold and rename tblSQL1_hold back to tblSQL1
    ..repeat process for next report run

    Not sure this gains anything, just throwing out there.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Am I stupid, or.....
    By MattLewis in forum Programming
    Replies: 4
    Last Post: 11-13-2016, 07:18 PM
  2. Being Stupid - #div/0
    By Epona in forum Queries
    Replies: 1
    Last Post: 06-09-2012, 09:15 AM
  3. Stupid Question ?
    By nood in forum Forms
    Replies: 3
    Last Post: 09-29-2010, 07:54 AM
  4. I can't be this stupid
    By corquando in forum Import/Export Data
    Replies: 2
    Last Post: 03-01-2010, 04:12 PM
  5. Why is Access so stupid?
    By dvongrad in forum Queries
    Replies: 1
    Last Post: 11-14-2009, 10:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums