• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

Ensuring all processes are complete before continuing

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
Andy Brown
Asked:
Andy Brown
  • 2
  • 2
  • 2
1 Solution
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Andy BrownDeveloperAuthor Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Gustav BrockCIOCommented:
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
 
Andy BrownDeveloperAuthor Commented:
Thanks Gustav - I'll start writing the code......
0
 
Gustav BrockCIOCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now