VBA error: Action cannot be completed because other program is busy

[seanavan]

Hi everyone. I've done a lot of VBA in Excel programming, but not a lot in Access. Hopefully this problem has a simple fix but so far the solution has eluded me.

General situation: My company stores data in different sources (Oracle, SQL Server, Excel, and Access). For lack of a better word, we'll say the region to which the data apply determines the program where the data is stored. This is a situation that isn't going to change. I've been tasked with creating a database that will talk to the different systems. The user needs to be able to select the region on the form and then the program will go retrieve data from the appropriate system. The user can then generate reports based on the retrieved data.

What I've done so far: I have the form built in an Access database (DB1) and am adding code behind it. It talks to Excel and SQL successfully. Here's where it gets weird. The way I'm trying to implement the Oracle part is by having it interact with another Access database (DB2) that was built specifically for that purpose. DB2 has about 20 linked tables from Oracle and about 40 queries that run off these tables to summarize data into tables. It also has a macro that runs the ~40 queries in order. What I'm doing at the moment is opening DB2 from DB1 and calling the macro that runs the ~40 queries. The final query creates a table in DB2. This table is also a linked table in DB1 and the last thing my code does is refresh the link to this table.

My problem: When I call the routine, the macro running in DB2 takes so long to run that I get an error in DB1 saying, "This action cannot be completed because the other application is busy. Choose " switch to " to activate the busy
application and correct the problem " The user then has to select: [Switch to] [Retry] or [Cancel] before the program will keep going.

My question: How do I prevent this and keep the macro running with focus staying on or returning to DB1 when the macro is done?

To be fair, running the macro directly in DB2 takes about 45 minutes(!). As long as I hit [Switch to], then it takes about the same amount of time when calling it from DB1.

Here's some code to show how I'm implementing this stuff. It's being from run DB1, so the current database is DB1:

Dim accDB As Access.Application
Set accDB = New Access.Application
Dim InvDB As DAO.Database
Dim strDbName As String
Dim tdf As TableDef

' Open DB2:
strDbName = "C:\path\DB2.mdb"
accDB.Visible = True
Set InvDB = accDB.DBEngine.OpenDatabase(strDbName, False, False)
accDB.OpenCurrentDatabase strDbName

' Refresh Oracle linked tables in DB2:
For Each tdf In InvDB.TableDefs
    If Len(tdf.Connect) > 0 Then tdf.RefreshLink
Next tdf

' Call Macro to run ~40 queries in DB2:
accDB.DoCmd.SetWarnings False
accDB.DoCmd.RunMacro "mcrRun40Queries"   '<-- Where I get the error
accDB.DoCmd.SetWarnings True

' Refresh DB1 table linked to table in DB2:
CurrentDb.TableDefs("Data4Export").RefreshLink

' Close DB2:
InvDB.Close
Set InvDB = Nothing
Set accDB = Nothing

Thank you in advance for any advice on how to solve this. As I said, VBA in Access is far from my strong point. Oh, and the reason I'm not just recreating the 20 linked tables and ~40 queries in DB1 is because other people edit DB2 to fix/improve the queries there to interact with our ever-evolving Oracle database structure. It will be a lot easier in the future to stay current if I don't have to try to duplicate all changes to DB2 in DB1.

I did search for posts on the same error but my searchfu must be weak because I didn't find anything that helped.

-Sean

 

[Rx_]

DB2 has about 20 linked tables from Oracle and about 40 queries that run off these tables to summarize data into tables. DB2 takes about 45 minutes.​

Looks like it is a heavy query. If this is being run across the network from your PC, it might be be a pretty heavy load across the network. ​

Instead of having all the processing taking place on your Access workstation, it is probably time to create a View or PassThrough Stored Procedure on the Oracle side.​

An example: Using linked tables to prototype, a query to SQL Server took a little over 10 minutes. Creating the View in SQL server to get the same results ran in well under 1 minute. The biggest bonus was the massive reduction in network traffic.


BTW: Are you asking how to asynchronously run a query?

Another trick is to use ADO and fire the execute statement with the option adAsyncExecute. But, this will not reduce the processing time on your client computer.​

​

 

[boblarson]

What steps are in the macro? It could be one of them. It might be better to do it in code and use:

DoEvents

after each step so it completes before moving on.

 

[seanavan]

Thanks, Rx. That's exactly how I'm doing the SQL part, with a pass-through call to a SQL stored procedure. I can't create the Oracle stored procedure but will see if someone else who deals with it can. Part of the problem, which I forgot to mention, is that I'm in OR and the Oracle database is in TX, so this is through our network.

Regarding your question about asynchronously running a query, I'm not sure. I haven't heard of those and will look into them. From a quick search on Google, it seems like they might be useful.

Thanks, Bob. I started doing that, putting the calls to the queries in VBA, rather than calling the macro. Thanks for the thought on using DoEvents. I'll give that a go too. And hello from someone also in Portland, OR.

 

[spikepl]

Another possibility is to separate the Siamese twins. Launch the DB2 in its own shell, and e.g. let it write some file to indicate when it's done, and look for that file from DB1

 

[seanavan]

Thanks, spikepl. I'm not having luck with the DoEvents idea so I'll look into launching DB2 in its own shell next. Other than this issue, this procedure is working fine, which is a minor miracle in and of itself.