Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Slow PC Performance after running Access Application

    I am looking for a solution to slow PC performance immediately after running an application that opens Access from Excel. All files are on the C drive. While the program is running, any other Windows applications (email, MS Communicator, etc.) run in slow motion. When the application is closed, the slowness goes away after I open an application, such as Excel, but the first time opening takes 20 seconds. After that, Excel opens immediately each time.

    My application works with 600,000 records by importing a text file into Access, and brings back summarized data to Excel where several reports are created with formatting, totals, etc. The whole process takes about 20 minutes. Does anyone know of a trick to procedurally restore a Windows XP PC to the quick-running state it had before running the application?

    The VBA coded used to start Access from Excel:
    Code:
    Set appAccess = CreateObject("Access.Application")
    macro2Run = "Production Main"
    
    appAccess.AutomationSecurity = 1 ' set macro security LOW.
    appAccess.opencurrentdatabase _
        "C:\Documents and Settings\Data\~myDBName.MDB"
    
    appAccess.Visible = True
    appAccess.UserControl = True
    With appAccess
        .DoCmd.RunMacro (macro2Run)
        .Quit
    End With
    I developed the application for an associate, who said after running it the first time "I'm rebooting", and that is why I am trying to improve the application.

    Jerry

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    What does appAccess.UserControl = True mean/do? (I do understand all the rest of your code)
    Have you "set appAccess = Nothing"? I don't see it here. That might help too.
    Vic

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Damn, beaten to the answer!
    Always, always, always make sure you close all objects and clear (set = nothing) after they have done their job!
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Sorry georgev. just trying to help.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Haha I'll forgive you this time :snicker:
    How're you ddoing anyway?
    Not seen so much of you these past few..
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I have a job (client) that is requiring a lot more of my time than I'm used to giving outside my own office. But, they are paying me to do this work so there is no complaining!

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh, that would explain the new found interest in SQL Server?
    I've not had any consultancy work for about a year now (that sounds impressive consiering my age (imo ), but really, they were simple little apps and Macros)... But yeah, great pay for them, huh!
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi All

    I obviousle don't know what your macro does, but my method of choice would be to run it all from Excel using ADO connection/recordsets.

    I have used this method extensively for data exchange between spreadsheets/csv/access (and from one Acces DB to another) and it all works extreamly fast.

    You can use the Access select queries as table and execute action queries against the ADO connection. However, if you have a FE/BE linking then it runs quicker to connect then BE and write SQL statement against the table in the BE.

    I did start writing code in Access to open that Excel and import data from multiple time sheets, but fond this very slow, so pasted the code into excel (with appropriate mods) using ADO and it was significanly faster. I don't why, but only one App is open this way.

    I don't know if this is a practical proposition for your situation/requirements, but it is a thought.


    MTB

  9. #9
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I realized that I should have copied my next line of code that reads:
    Set appAccess = Nothing
    When I first tested opening Access from Excel, Access opened and immediately closed until I put "appAccess.UserControl = True" in the code. I see that it is not necessary now because Access stays visible while the Access part of the program is running.

    MikeTheBike: I have a lot of things going on in Access including importing text files, the creation of temporary tables, editing the data, pivoting the data into days overdue columns: 0-30, 31-60, 61-90 ... 120+, adding sales officer name from a separate linked database, combining data from two sources to produce reports for each source as well as a combined version. I was able to pull this off running from Access and I was not aware it may be possible to do it all from Excel. I would be interested in seeing documentation or brief Excel VBA code, however it may not be practical to convert my application with its macros and dozens of queries.

    Jerry

Posting Permissions

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