Hi,
I might become a regular poster on here as I have a new job which is supporting a miscellany of Access and Excel applications, something I have not done for a few years now....
One thing I have inherited is an Access database which we use on a daily basis to run reports for customers.
Each time it's used, a customer-specific dataset is imported to a table, then update and make-table queries are run to perform various calculations before the data is copied back into Excel to create the final report.
The majority of the time this runs fine, but if the initial dataset is large (> 100,000 rows) then we sometimes hit an error "Run Time error 3035 - System Resource exceeded". The solution (up to now) has been to run the same job on an old Windows XP machine, running Access 2003, which seems to be able to handle even the largest datasets. It's only our newer, Windows 7 pcs with Access 2013 which have the problem. Since XP is no longer supported we really need to get the issue with Access 2013 resolved.
The process is kicked off from within Excel VBA, which opens our Master database, imports the dataset to the main table, then calls an Access VBA sub-routine, which in turn calls about 60 Access update queries and make-table queries one after the other, using docmd.openquery.
So, in Excel we have ;
The 'UpdateTables' subroutine in Access is just a list of about 60 DoCmd.OpenQuery calls.
The report that I am running today is falling over consistently at a particular update query. I am told that it is not necessarily the same query that falls over each time and I am also told that occasionally just pressing F5 in the Debug window will eventually get the query to run, although I've not seen that myself. If I comment out the rogue query and press F5, then the process moves on 2 more queries before failing again with the same error message.
I have tried compacting and repairing the database, and adding Indices to the join fields being used in the query, but to no avail. I also googled and found a link to a Microsoft Hotfix for exactly this issue, but when I tried to install it, it said that it could not find any relevant application installed on my pc.
I have also tried changing the OLE timeout setting from 30 to 999 but it did not make any difference.
Does anyone have any other suggestions ? Do you think it might be worth splitting the subroutine so that I run, say, 10 queries at a time, closing and re-opening Access db in between ? Or is there a simpler way to free up resources between each query within the Access routine ?
Thanks very much for any suggestions...
StepOne
I might become a regular poster on here as I have a new job which is supporting a miscellany of Access and Excel applications, something I have not done for a few years now....
One thing I have inherited is an Access database which we use on a daily basis to run reports for customers.
Each time it's used, a customer-specific dataset is imported to a table, then update and make-table queries are run to perform various calculations before the data is copied back into Excel to create the final report.
The majority of the time this runs fine, but if the initial dataset is large (> 100,000 rows) then we sometimes hit an error "Run Time error 3035 - System Resource exceeded". The solution (up to now) has been to run the same job on an old Windows XP machine, running Access 2003, which seems to be able to handle even the largest datasets. It's only our newer, Windows 7 pcs with Access 2013 which have the problem. Since XP is no longer supported we really need to get the issue with Access 2013 resolved.
The process is kicked off from within Excel VBA, which opens our Master database, imports the dataset to the main table, then calls an Access VBA sub-routine, which in turn calls about 60 Access update queries and make-table queries one after the other, using docmd.openquery.
So, in Excel we have ;
Code:
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase (m_strDBCopyPath & m_strDBCopyName)
'Set db = CurrentDb
acApp.Visible = True
acApp.Run "UpdateTables"
The 'UpdateTables' subroutine in Access is just a list of about 60 DoCmd.OpenQuery calls.
The report that I am running today is falling over consistently at a particular update query. I am told that it is not necessarily the same query that falls over each time and I am also told that occasionally just pressing F5 in the Debug window will eventually get the query to run, although I've not seen that myself. If I comment out the rogue query and press F5, then the process moves on 2 more queries before failing again with the same error message.
I have tried compacting and repairing the database, and adding Indices to the join fields being used in the query, but to no avail. I also googled and found a link to a Microsoft Hotfix for exactly this issue, but when I tried to install it, it said that it could not find any relevant application installed on my pc.
I have also tried changing the OLE timeout setting from 30 to 999 but it did not make any difference.
Does anyone have any other suggestions ? Do you think it might be worth splitting the subroutine so that I run, say, 10 queries at a time, closing and re-opening Access db in between ? Or is there a simpler way to free up resources between each query within the Access routine ?
Thanks very much for any suggestions...
StepOne