|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
at work we use an access database that calls various executables that
run our routines through an ODBC link. the problem comes from running
the executables. this has been an evolution to say the least.
1. Shell ... this worked great i think it still works but my boss had
heard that due to security stuff that the shell routine was going to be
removed from access. well it hasn't and still works great but we have
already migrated to using an API call.
2. API version 1 called the utility ran fine but was slow. this code
came from Microsoft support and was flawed to say the least. a 45
second operation would take 4 min 50 sec. this was problematic when
some users of our software have to run the routines for hours. this
increased processing to a day. i found that access was put in to an
infinite do loop that continuously checked for termination.
3. API version 2 i thought was great we managed to get the performance
back that we had started with. changed from a loop to using INFINITE as
part of the WaitForSingleObject command. i was happy users were happy
bosses were happy. but this process locked access so that it couldn't
be used during processing. since we were updating lots of data in
tables i didn't mind since people shouldn't mess with the data while it
is being processed in my opinion.
4. API version 3 someone forwarded me the following code to run the
executable and not lock the database but it seems to be flawed. if we
run a utility multiple times in a row. process wait for completion
change some data and process again and do this a few times it seems that
things don't close gracefully. access will be locked up beyond repair
and will usually have to go to the task manager to close access. since
users want to be able to manipulate there database while it is
processing for long periods i would like to see if i can fix this code.
Public Function ExecFPS(cmdline$)
On Local Error GoTo TheError
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim CmdBuf As String
CmdBuf = FPS_Exec_Path + "\" + cmdline$
Dim ptrhand As Long
Dim msg As String
start.cb = Len(start)
CreateProcessA 0&, CmdBuf, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&,
0&, start, proc
ptrhand = OpenProcess(SYNCHRONIZE, False, proc.hProcess)
If ptrhand <> False Then
WaitForSingleObject ptrhand, INFINITE
CloseHandle ptrhand
End If
CloseHandle (proc.hProcess)
TheEnd:
Exit Function
TheError:
msg = "Error Information..." & vbCrLf & vbCrLf
msg = msg & "Function: ExecFPS" & vbCrLf
msg = msg & "Description: " & err.Description & vbCrLf
msg = msg & "Error #: " & Format$(err.Number) & vbCrLf
MsgBox msg, vbInformation, "ExecFPS"
Resume TheEnd
End Function
any assistance is appreciated. if i can't get this code to work as i
think it should i will have to go back to an older version that just
locks the database while it processes. thanks in advance.
--
Charles E. Vopicka's (Chuck) : chuck[ at ]forestbiometrics.com
Database Management, GIS Specialist and Research Assistant
Forest Biometrics Research Institute
University of Montana - College of Forestry and Conservation
Missoula, MT 59812
United States of America
Phone:
(406)243-4526
(406)243-4264
(406)549-0647 (Home)
:-) HAVE A NICE DAY (-:
"UNLESS" (The Lorax, by Dr. Seuss)
|
|
Good grief, what a lot of work for nothing. I'd go back to Shell if I were
you. It isn't going to be removed. After all, it's still there in VB.Net,
so why would they bother removing it from VBA?
"Charles E. Vopicka" wrote:
[Quoted Text] > at work we use an access database that calls various executables that
> run our routines through an ODBC link. the problem comes from running
> the executables. this has been an evolution to say the least.
>
>
> 1. Shell ... this worked great i think it still works but my boss had
> heard that due to security stuff that the shell routine was going to be
> removed from access. well it hasn't and still works great but we have
> already migrated to using an API call.
>
>
> 2. API version 1 called the utility ran fine but was slow. this code
> came from Microsoft support and was flawed to say the least. a 45
> second operation would take 4 min 50 sec. this was problematic when
> some users of our software have to run the routines for hours. this
> increased processing to a day. i found that access was put in to an
> infinite do loop that continuously checked for termination.
>
>
> 3. API version 2 i thought was great we managed to get the performance
> back that we had started with. changed from a loop to using INFINITE as
> part of the WaitForSingleObject command. i was happy users were happy
> bosses were happy. but this process locked access so that it couldn't
> be used during processing. since we were updating lots of data in
> tables i didn't mind since people shouldn't mess with the data while it
> is being processed in my opinion.
>
>
> 4. API version 3 someone forwarded me the following code to run the
> executable and not lock the database but it seems to be flawed. if we
> run a utility multiple times in a row. process wait for completion
> change some data and process again and do this a few times it seems that
> things don't close gracefully. access will be locked up beyond repair
> and will usually have to go to the task manager to close access. since
> users want to be able to manipulate there database while it is
> processing for long periods i would like to see if i can fix this code.
>
>
> Public Function ExecFPS(cmdline$)
>
> On Local Error GoTo TheError
>
> Dim proc As PROCESS_INFORMATION
> Dim start As STARTUPINFO
> Dim CmdBuf As String
> CmdBuf = FPS_Exec_Path + "\" + cmdline$
> Dim ptrhand As Long
> Dim msg As String
>
> start.cb = Len(start)
> CreateProcessA 0&, CmdBuf, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&,
> 0&, start, proc
>
> ptrhand = OpenProcess(SYNCHRONIZE, False, proc.hProcess)
>
> If ptrhand <> False Then
> WaitForSingleObject ptrhand, INFINITE
> CloseHandle ptrhand
> End If
>
> CloseHandle (proc.hProcess)
>
> TheEnd:
>
> Exit Function
>
> TheError:
>
> msg = "Error Information..." & vbCrLf & vbCrLf
> msg = msg & "Function: ExecFPS" & vbCrLf
> msg = msg & "Description: " & err.Description & vbCrLf
> msg = msg & "Error #: " & Format$(err.Number) & vbCrLf
> MsgBox msg, vbInformation, "ExecFPS"
> Resume TheEnd
>
> End Function
>
>
>
> any assistance is appreciated. if i can't get this code to work as i
> think it should i will have to go back to an older version that just
> locks the database while it processes. thanks in advance.
>
> --
> Charles E. Vopicka's (Chuck) : chuck[ at ]forestbiometrics.com
>
> Database Management, GIS Specialist and Research Assistant
>
> Forest Biometrics Research Institute
> University of Montana - College of Forestry and Conservation
> Missoula, MT 59812
> United States of America
>
> Phone:
> (406)243-4526
> (406)243-4264
> (406)549-0647 (Home)
>
> :-) HAVE A NICE DAY (-:
>
> "UNLESS" (The Lorax, by Dr. Seuss)
>
|
|
We can also ask ourselves why the need of using an Access database for doing
this kind of stuff.
Maybe that writing a VB/VB.NET, a C++/C# or even a WSH script shell would be
a beter idea then dealing with API calls from Access' VBA.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Baz" <Baz[ at ]discussions.microsoft.com> wrote in message
news:46623BEF-2622-4C53-BAA6-D92E5F9504E8[ at ]microsoft.com...
[Quoted Text] > Good grief, what a lot of work for nothing. I'd go back to Shell if I
> were
> you. It isn't going to be removed. After all, it's still there in
> VB.Net,
> so why would they bother removing it from VBA?
>
> "Charles E. Vopicka" wrote:
>
>> at work we use an access database that calls various executables that
>> run our routines through an ODBC link. the problem comes from running
>> the executables. this has been an evolution to say the least.
>>
>>
>> 1. Shell ... this worked great i think it still works but my boss had
>> heard that due to security stuff that the shell routine was going to be
>> removed from access. well it hasn't and still works great but we have
>> already migrated to using an API call.
>>
>>
>> 2. API version 1 called the utility ran fine but was slow. this code
>> came from Microsoft support and was flawed to say the least. a 45
>> second operation would take 4 min 50 sec. this was problematic when
>> some users of our software have to run the routines for hours. this
>> increased processing to a day. i found that access was put in to an
>> infinite do loop that continuously checked for termination.
>>
>>
>> 3. API version 2 i thought was great we managed to get the performance
>> back that we had started with. changed from a loop to using INFINITE as
>> part of the WaitForSingleObject command. i was happy users were happy
>> bosses were happy. but this process locked access so that it couldn't
>> be used during processing. since we were updating lots of data in
>> tables i didn't mind since people shouldn't mess with the data while it
>> is being processed in my opinion.
>>
>>
>> 4. API version 3 someone forwarded me the following code to run the
>> executable and not lock the database but it seems to be flawed. if we
>> run a utility multiple times in a row. process wait for completion
>> change some data and process again and do this a few times it seems that
>> things don't close gracefully. access will be locked up beyond repair
>> and will usually have to go to the task manager to close access. since
>> users want to be able to manipulate there database while it is
>> processing for long periods i would like to see if i can fix this code.
>>
>>
>> Public Function ExecFPS(cmdline$)
>>
>> On Local Error GoTo TheError
>>
>> Dim proc As PROCESS_INFORMATION
>> Dim start As STARTUPINFO
>> Dim CmdBuf As String
>> CmdBuf = FPS_Exec_Path + "\" + cmdline$
>> Dim ptrhand As Long
>> Dim msg As String
>>
>> start.cb = Len(start)
>> CreateProcessA 0&, CmdBuf, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&,
>> 0&, start, proc
>>
>> ptrhand = OpenProcess(SYNCHRONIZE, False, proc.hProcess)
>>
>> If ptrhand <> False Then
>> WaitForSingleObject ptrhand, INFINITE
>> CloseHandle ptrhand
>> End If
>>
>> CloseHandle (proc.hProcess)
>>
>> TheEnd:
>>
>> Exit Function
>>
>> TheError:
>>
>> msg = "Error Information..." & vbCrLf & vbCrLf
>> msg = msg & "Function: ExecFPS" & vbCrLf
>> msg = msg & "Description: " & err.Description & vbCrLf
>> msg = msg & "Error #: " & Format$(err.Number) & vbCrLf
>> MsgBox msg, vbInformation, "ExecFPS"
>> Resume TheEnd
>>
>> End Function
>>
>>
>>
>> any assistance is appreciated. if i can't get this code to work as i
>> think it should i will have to go back to an older version that just
>> locks the database while it processes. thanks in advance.
>>
>> --
>> Charles E. Vopicka's (Chuck) : chuck[ at ]forestbiometrics.com
>>
>> Database Management, GIS Specialist and Research Assistant
>>
>> Forest Biometrics Research Institute
>> University of Montana - College of Forestry and Conservation
>> Missoula, MT 59812
>> United States of America
>>
>> Phone:
>> (406)243-4526
>> (406)243-4264
>> (406)549-0647 (Home)
>>
>> :-) HAVE A NICE DAY (-:
>>
>> "UNLESS" (The Lorax, by Dr. Seuss)
>>
|
|
|