Solved

Ensuring all processes are complete before continuing

Posted on 2016-10-21
6
31 Views
Last Modified: 2016-10-21
I am writing a function via MS-Access VBA that generates several files and stores them in a folder (PDF, XLSX etc).  It then creates an email and attaches all of the files within the specified folder (I am using EASendMail to do this).  On my PC it works just fine, but I'm a little concerned that on a slower PC/network, Access (or the PC itself) may still be creating the files (XLS, PDF etc.) when the command to attach all of the files is issued (jumping ahead of itself).

Ideally, I'd like someone to come back to me and say that Access will not start work on the email/attach function until the previous processes have all finished, or before issuing the "oSmtp.AddAttachments" command, you should do x to ensure everything has been done.

I know I could check that the files in the folder exist before attaching (but if I don't have to do this, I'd rather not).
0
Comment
Question by:Andy Brown
  • 2
  • 2
  • 2
6 Comments
 
LVL 19
ID: 41853895
You can use a Transaction.  You can manage transaction processing during a session defined by a DBEngine  Workspace object -- for instance: start (BeginTrans), Rollback, and commit (CommitTrans)

here is a help page:
https://msdn.microsoft.com/en-us/library/office/ff835327(v=office.15).aspx
1
 

Author Comment

by:Andy Brown
ID: 41853900
Thank you - That's a good idea, but I'm not too sure how to implement it (as I'm not writing data as such).  

Ideally, it will be something like

Transaction begin
Create files
When files are actually sitting in the folder and everything is done
Transaction ends
Attach files from folder
0
 
LVL 19
ID: 41853933
you're welcome

here is a help page for CreateWorkspace -- but I don't think you need to do that as there is a default workspace
https://msdn.microsoft.com/en-us/library/office/ff821374(v=office.15).aspx

btw, currentdb is like DBEngine(0)(0) -- just that currentdb is a pointer so more up-to-date when it is initialized

I think you can simply do something like this:
Set myDb = DBEngine.OpenDatabase(sName)
https://msdn.microsoft.com/en-us/library/office/ff193474.aspx

use db.execute

then either
DBEngine.CommitTrans
or
DBEngine.Rollback
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41854048
Transaction control has nothing to do with this. It only controls updates to the database, totally unrelated to external files.

Your only option is to keep track of the files in a loop, then proceed.

/gustav
0
 

Author Closing Comment

by:Andy Brown
ID: 41854055
Thanks Gustav - I'll start writing the code......
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41854067
You may be able to use the Sleep function here:

Modern/Metro style message box and input box for Microsoft Access 2013

Go to paragraph Simulating dialogue mode for code for a reliable loop that doesn't kill the machine.

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now