VB doesn't wait until SQL-query has finished
Hi Folks,
I hope you can help me, maybe it's just one simple command I can't find in
my VB books.
I'm performing a SQL-query from VB 5 to a Access 97 dabatbase. Right after
that I call a dll that analyses the data from the table I just changed.
Here is the source code and I thought it would go just fine...
--------------------------------------------------------------------------
' open database and edit the content
Set rst = dbs.OpenRecordset("select * from stoerdat;")
If rst.EditMode = dbEditNone Then
rst.Edit
End If
For k = 1 To UBound(arrayMessages)
rst.AddNew
rst("Faultmessage") = arrayMessages(k).Msg
rst("Duration") = arrayMessages(k).Dur
rst("Starttime") = arrayMessages(k).startMsg
rst("Endtime") = arrayMessages(k).endMsg
rst("Profile") = arrayMessages(k).profile
rst("BrandNo") = arrayMessages(k).brandno
rst("Startbrand") = arrayMessages(k).startBrand
rst("Endbrand") = arrayMessages(k).endBrand
rst("Status") = arrayMessages(k).status
rst("startAuto") = arrayMessages(k).startAuto
rst("endAuto") = arrayMessages(k).endAuto
rst.Update
Next k
dbs.Close
'call dll for evaluation of the data
call statresult(AAV, AAM, AAS, ADV, ADM, ADS)
-------------------------------------------------------------------------
...but it doesn't!!
VB doesn't wait, until the SQL-query has finished. So the dll starts the
evaluation while Access is still changing the data of the table. I don't
have to tell you that I'm getting wrong results by this. For example the
table stoerdat contains 60.000 rows, but the dll is called at a time when
there are just 40.000 rows in that table.
How can I make VB wait, until Access finished the SQL-query??
Maybe there is a parameter like 'vbModal' for forms??!!??
Maybe I don't see the wood for the trees!!
Thanks a lot for your support,
Daniel Teufert
Re: VB doesn't wait until SQL-query has finished
On 23 Apr 2002 00:52:27 -0800, "Daniel Teufert" <teufert.daniel@do.khs-ag.com> wrote:
¤
¤ Hi Folks,
¤ I hope you can help me, maybe it's just one simple command I can't find in
¤ my VB books.
¤
¤ I'm performing a SQL-query from VB 5 to a Access 97 dabatbase. Right after
¤ that I call a dll that analyses the data from the table I just changed.
¤ Here is the source code and I thought it would go just fine...
¤
¤ --------------------------------------------------------------------------
¤ ' open database and edit the content
¤ Set rst = dbs.OpenRecordset("select * from stoerdat;")
¤
¤ If rst.EditMode = dbEditNone Then
¤ rst.Edit
¤ End If
¤
¤ For k = 1 To UBound(arrayMessages)
¤ rst.AddNew
¤
¤ rst("Faultmessage") = arrayMessages(k).Msg
¤ rst("Duration") = arrayMessages(k).Dur
¤ rst("Starttime") = arrayMessages(k).startMsg
¤ rst("Endtime") = arrayMessages(k).endMsg
¤ rst("Profile") = arrayMessages(k).profile
¤ rst("BrandNo") = arrayMessages(k).brandno
¤ rst("Startbrand") = arrayMessages(k).startBrand
¤ rst("Endbrand") = arrayMessages(k).endBrand
¤ rst("Status") = arrayMessages(k).status
¤ rst("startAuto") = arrayMessages(k).startAuto
¤ rst("endAuto") = arrayMessages(k).endAuto
¤
¤ rst.Update
¤ Next k
¤
¤ dbs.Close
¤
¤ 'call dll for evaluation of the data
¤ call statresult(AAV, AAM, AAS, ADV, ADM, ADS)
¤ -------------------------------------------------------------------------
¤
¤ ..but it doesn't!!
¤ VB doesn't wait, until the SQL-query has finished. So the dll starts the
¤ evaluation while Access is still changing the data of the table. I don't
¤ have to tell you that I'm getting wrong results by this. For example the
¤ table stoerdat contains 60.000 rows, but the dll is called at a time when
¤ there are just 40.000 rows in that table.
¤
¤ How can I make VB wait, until Access finished the SQL-query??
¤ Maybe there is a parameter like 'vbModal' for forms??!!??
¤ Maybe I don't see the wood for the trees!!
¤
The only thing I can think of here is that there are implicit transactions pending when you execute
your statresult function.
You might want to try wrapping your Update code between BeginTrans and CommitTrans (with the
dbFlushOSCacheWrites argument) to ensure that all updates have been committed.
Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)
Re: VB doesn't wait until SQL-query has finished
HI Daniel
if ur "call statresult" this call is to another dll which is making another
connection to Access db then the changes done by one connection are not avialable
for another connection to do processing so in ur case its not tht VB is not
waiting for query to finish but the problem is Access is not giving away
data for another connection. i bet if u run ur code in debug mode it will
work fine. Try to use same connection for working on ur data from VB code
and dll by passing connection object.
look at this article from msdn october 1999 collection
get back o me if u need more info there is nuthing like tht VB is not waiting
for query to finish or something like tht i can replicate the same problem
100 out of 100 times pal
PRB: Single-User Concurrency Problems With ADO and Jet
ID: Q216925
--------------------------------------------------------------------------------
The information in this article applies to:
Microsoft Visual Basic Professional and Enterprise Editions for Windows,
version 6.0
ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1
--------------------------------------------------------------------------------
SYMPTOMS
When using ADO against a Microsoft Jet database, you may notice that saving
information via the ADO Data control or an ADO Recordset is not immediately
visible to other ADO Data controls or Recordsets. The data is visible after
a few seconds delay.
CAUSE
Each object is opened on a different connection.
RESOLUTION
Make all ADO Recordset objects and ADO Data controls use the same connection.
STATUS
This behavior is by design.
MORE INFORMATION
Microsoft Jet maintains a separate cache for each connection to the engine.
By default, the cache times out after five seconds, which means that changes
made to an MDB file on one connection may not be visible on another connection
for up to five seconds.
This is different behavior than DAO, where all Connection or Database objects
default to share the same read cache.
This article outlines some strategies that allow you to share connections
between Recordset objects and the ADO Data control.
When opening an ADO Recordset object, it is easy to share a connection. You
can open a global connection object and reference it in the rs.Open method.
This method can provide application or form-wide connection sharing:
rs1.Open "Employees", cn
rs2.Open "Employees", cn
Another more limited method of connection sharing is to reference the ActiveConnection
property of an already open Recordset:
rs1.Open "Employees", "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=NWIND.MDB"
rs2.Open "Employees", rs1.ActiveConnection
You can open a Recordset on the same connection as an ADO Data control by
referencing the ActiveConnection property of the Recordset property:
rs.Open "Employees", ADODC1.Recordset.ActiveConnection
If the ADO Data control (ADODC2, ADODC3, and ADODC4 in the example below)
is using client-side cursors, then you can make it share an existing connection:
Set ADODC2.Recordset.ActiveConnection = cn
Set ADODC3.Recordset.ActiveConnection = rs.ActiveConnection
Set ADODC4.Recordset.ActiveConnection = ADODC1.Recordset.ActiveConnection
The next technique allows ADO Data controls to share connections and use
server-side cursors. It involves setting the ADO Data control's Recordset
property to an existing Recordset that you create using a shared Connection
object.
NOTE: You cannot substitute the connection if using server-side cursors (the
default):
rs.Open "Employees", cn
Set ADODC1.Recordset = rs
Steps to Reproduce Behavior
Open a new Project in Visual Basic 6.0 with a form (Form1).
Add a reference to Microsoft ActiveX Data Objects 2.0 Library.
Add the ADO Data Control (6.0) to the project.
Add the ADO Data control to the form and set the following properties:
Property Value
Name ADODC1
CommandType adCmdTable
ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Data Source=NWIND.MDB
CursorLocation adUseServer
CursorType adOpenStatic
LockType adLockOptimistic
RecordSource Employees
Add four textboxes to the form and set the following properties:
Property Value
Name FirstName
DataSource ADODC1
DataField FirstName
Property Value
Name LastName
DataSource ADODC1
DataField LastName
Property Value
Name SameConnection
Property Value
Name NewConnection
Add a CommandButton (Command1) to the form.
Add the following code to the form:
Option Explicit
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
' Save the data if necessary
If ADODC1.Recordset.EditMode <> adEditNone Then
ADODC1.Recordset.Update
End If
' Open Recordset on same connection as the data control
rs.Open "Employees", ADODC1.Recordset.ActiveConnection, , , adCmdTable
SameConnection.Text = rs!LastName
rs.Close
' Open recordset on a new connection
rs.Open "Employees", ADODC1.ConnectionString, , , adCmdTable
NewConnection.Text = rs!LastName
rs.Close
End Sub
Run the project and edit the Employee's last name.
Click on the CommandButton to save the data, and then read it. You should
see the edited value displayed in the SameConnection textbox and the original
value displayed in the NewConnection textbox.
Wait a few seconds and click the CommandButton again. The edited value should
now also show in the NewConnection text box. This is due to the cache timing
out.
NOTE: When using the ADO Data control in the manner described above, avoid
using the Refresh method:
ADODC1.Refresh
because this will result in a new connection. Use the Requery method of the
Recordset property instead:
ADODC1.Recordset.Requery
© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Malcolm Stewart, Microsoft Corporation
Additional query words:
Keywords : kbADO200 kbJET kbVBp kbVBp600 kbGrpVBDB
Version : WINDOWS:2.0,2.01,2.1,6.0
Platform : WINDOWS
Issue type : kbprb
Last Reviewed: April 3, 1999
--------------------------------------------------------------------------------
Send feedback to MSDN.Look here for MSDN Online resources.
"Daniel Teufert" <teufert.daniel@do.khs-ag.com> wrote:
>
>Hi Folks,
>I hope you can help me, maybe it's just one simple command I can't find
in
>my VB books.
>
>I'm performing a SQL-query from VB 5 to a Access 97 dabatbase. Right after
>that I call a dll that analyses the data from the table I just changed.
>Here is the source code and I thought it would go just fine...
>
>--------------------------------------------------------------------------
>' open database and edit the content
>Set rst = dbs.OpenRecordset("select * from stoerdat;")
>
> If rst.EditMode = dbEditNone Then
> rst.Edit
> End If
>
> For k = 1 To UBound(arrayMessages)
> rst.AddNew
>
> rst("Faultmessage") = arrayMessages(k).Msg
> rst("Duration") = arrayMessages(k).Dur
> rst("Starttime") = arrayMessages(k).startMsg
> rst("Endtime") = arrayMessages(k).endMsg
> rst("Profile") = arrayMessages(k).profile
> rst("BrandNo") = arrayMessages(k).brandno
> rst("Startbrand") = arrayMessages(k).startBrand
> rst("Endbrand") = arrayMessages(k).endBrand
> rst("Status") = arrayMessages(k).status
> rst("startAuto") = arrayMessages(k).startAuto
> rst("endAuto") = arrayMessages(k).endAuto
>
> rst.Update
> Next k
>
>dbs.Close
>
>'call dll for evaluation of the data
>call statresult(AAV, AAM, AAS, ADV, ADM, ADS)
>-------------------------------------------------------------------------
>
>...but it doesn't!!
>VB doesn't wait, until the SQL-query has finished. So the dll starts the
>evaluation while Access is still changing the data of the table. I don't
>have to tell you that I'm getting wrong results by this. For example the
>table stoerdat contains 60.000 rows, but the dll is called at a time when
>there are just 40.000 rows in that table.
>
>How can I make VB wait, until Access finished the SQL-query??
>Maybe there is a parameter like 'vbModal' for forms??!!??
>Maybe I don't see the wood for the trees!!
>
>Thanks a lot for your support,
>
>Daniel Teufert
