By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,392 Members | 576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,392 IT Pros & Developers. It's quick & easy.

Tell Excel VBA to wait for Access Macro to Finish Running

P: 5
I have an Excel file that uses VBA to open an Access database in the background and run a macro that refreshed tables in the database. Once this is complete, then there are steps done in the Excel to refresh data linked to the Access table, copy, paste, etc.

The Access has slowed a bit with a server change and now the macro takes a little longer to run, and now the Excel VBA is outpacing the Access and it's causing things to happen out of sequence and overwrite data incorrectly.

How can I (in my Excel VBA) tell the system to wait for the Access to completely finish before proceeding to the next steps in Excel? Thanks for any help!!
Aug 18 '20 #1
Share this Question
Share on Google+
6 Replies

twinnyfo
Expert Mod 2.5K+
P: 3,529
Just quickly thinking off the top of my head, here.....

If you had a Table in your Access DB that your Excel VBA can read (you SHOULD be able to do this), then have a flag in that table set to false once the Access Macro begins. The Excel VBA will place itself into an "infinite loop" while it is waiting for the value to Change.

Expand|Select|Wrap|Line Numbers
  1. fYourFlagVariable = False
  2. Do While Not fYourFlagVariable
  3.     fYourFlagVariable = [Code to look up value in Table]
  4. Loop
When your Access Macro completes, set the flag to True. This will allow the Excel VBA to continue.

Hope that hepps!
Aug 18 '20 #2

P: 5
Thank you! I think this would totally work. Let me give it a shot!
Aug 18 '20 #3

twinnyfo
Expert Mod 2.5K+
P: 3,529
Create a Table with one field: a Yes/No field (a Yes/No field may sometimes be referred to as a "flag." Some folks--me included--name Boolean variables with the "f" prefix for "flag" for that very same reason). You could call that Field "Continue." Add one record to that Table.

When the Macro Opens, set that Field value to False. When it completes, set the value to true.

Hope that hepps!
Aug 18 '20 #4

P: 5
Thank you for the follow up email....that helps a lot. I'm a relatively new VBA scripter and this advice is perfect. Thanks and I will let you know how it goes!
Aug 18 '20 #5

P: 5
Hey twinny! Sorry to bother you again, but would you mind helping me with the code to pull the value from an Access table? Here is what I have......the refresh of the queries in excel is happening before the Access finishes and that's causing the issue.

Expand|Select|Wrap|Line Numbers
  1. '***********************************************
  2. ' Refresh Access Queries embedded in Excel - Paste Data to Dashboard
  3. '***********************************************
  4.  
  5.     Sheets("Dashboard").Select
  6.     Range("A3").Select
  7.     If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
  8.  
  9. '***********************************************
  10. ' Run Macro 15 in Access
  11. '***********************************************
  12.  
  13. Dim FSO
  14. Set objFSO = CreateObject("Scripting.FileSystemObject")
  15. 'On Error Resume Next
  16.  
  17. Set ObjAccess = CreateObject("Access.Application")
  18.  
  19. ObjAccess.OpenCurrentDatabase "\\ServerName\data\FolderName\Affiliate Incompletion Reporting.accdb"
  20. ObjAccess.DoCmd.RunMacro "15 - Just Update Status from Excel to Master Data"
  21. Set ObjAccess = Nothing
  22.  
  23.     For Each objConnection In ThisWorkbook.Connections
  24.         'Get current background-refresh value
  25.         'bBackground = objConnection.OLEDBConnection.BackgroundQuery
  26.  
  27.         'Temporarily disable background-refresh
  28.         'objConnection.OLEDBConnection.BackgroundQuery = False
  29.  
  30.         'Refresh this connection
  31.         objConnection.Refresh
  32.  
  33.         'Set background-refresh value back to original value
  34.         'objConnection.OLEDBConnection.BackgroundQuery = bBackground
  35.     Next
Aug 18 '20 #6

twinnyfo
Expert Mod 2.5K+
P: 3,529
Well the best way to answer this is, that however it is that Excel is accessing the data in Access for its queries is the same way you should be able to access the data in another table. "In general" it's gonna look something like this:

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rst As Recordset
  3. Dim fContinue As Boolean
  4.  
  5. Set db = OpenDatabase("\\Path\Folder\DBName.accdb")
  6. Set rst = db.OpenRecordset("YourTableName")
  7. With rst
  8.     Call .MoveFirst
  9.     fContinue = !Continue
  10.     Call .Close
  11. End With
  12. Set rst = Nothing
  13. Set db = Nothing
Of course you can change that value in a similar way:

Expand|Select|Wrap|Line Numbers
  1. With rst
  2.     Call .MoveFirst
  3.     Call .Edit
  4.     !Continue = Not !Continue
  5.     Call .Update
  6. End With
Hope this hepps!
Aug 19 '20 #7

Post your reply

Sign in to post your reply or Sign up for a free account.