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:
Set appAccess = CreateObject("Access.Application")
macro2Run = "Production Main"
appAccess.AutomationSecurity = 1 ' set macro security LOW.
"C:\Documents and Settings\Data\~myDBName.MDB"
appAccess.Visible = True
appAccess.UserControl = True
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.
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!
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.
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.