Shell Wait & SQL Update

[cwatchowski]

I am very new to VBA for Access, so I will need to be hand fed on th
answers to 2 questions. They are as follows:

1. I need to run an external application (VBScript) from my For
macro
and have VBA wait for it to complete before executing the nex
line.

2. From the same Form macro, I need to know how to update
table
using SQL. I tried to do this from my VBScript but had
contention
problem with the Access application I was currently in.

I hope that someone can help me being as I have been trying to figur
these
problems for about two weeks. Thanks in advance to anyone that ca
help

 

[Douglas J Steele]

See http://www.mvps.org/access/api/api0004.htm at "The Access Web" for how
to wait until the shelled process has completed.

Without seeing your VBScript (and knowing the actual error messages), I
can't comment why you were getting contention. I always use DAO (if you're
using Access 2000 or 2002, you'll have to go into Tools | References through
the VB Editor and add a reference if you haven't already done so):

Dim strSQL As String

strSQL = "INSERT INTO MyTable (MyTextField, MyNumericField) " & _
"VALUES (" & Chr$(34) & Me!FormField1 & Chr$(34) & ", " & _
Me!FormField2 & ")"
CurrentDb.Execute strSQL, dbFailOnError

Note the difference between the value being passed for MyTextField and for
MyNumericField: the values for text fields need to be enclosed in quotes
(Chr$(34) is ")

 

[cwatchowski]

Thanks for the help. I have been working with code that I got off of th
Internat for waiting on a shelled application to finish. I finally go
it working in my Access application. I do have one question, though
What is the argument 'dbFailOnError' and what is it used for? Again
thanks for all of your help

 

[Douglas J. Steele]

dbFailOnError means that if an error is encountered during the execution of
the SQL, a trappable error will be raised that you can intercept with your
error handler. It also means that the entire statement will be rolled back.