Visual Basic not waiting for query to fully evaluate???

  • Thread starter Thread starter Vince
  • Start date Start date
V

Vince

Hello All,

I am using Visual Basic to open a saved query and then save
information in the query to an array for later use. The problem is
that the same query shows different results when opened directly vs.
when opened by Visual Basic. It is as if Visual Basic is not letting
the query fully evaluate before processing records.

The query is a subtotal query that contains several criteria set up as
"where" in the group-by box. Most of the criteria are based on one
table, one criteria is based on a second joined table. When the query
is opened directly this last criteria is correctly evaluated and the
proper records are shown. When opened in VB it is as if this criteria
did not exist. The query otherwise shows correct information except
it includes records that should not be there based on the last
criteria.

Is there a way to force visual basic to wait until a query is fully
opened before executing code that uses the resulting records. Or is
there something else that I am missing.

Any help would be greatly appreciated.

Thanks,

Vince

Partial code Follows---------------------------------
Public aWork(10) as Integar

Sub LoadWorkArray(WorkQuery as string) 'WorkQuery = query name
Dim WorkLastRecord as Integar
Dim X as Integar

Set SLWork = New ADODB.Recordset

SLWork.Open WorkQuery, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

WorkLastRecord = SLWork.RecordCount

ReDim aWork(WorkLastRecord + 1)

With SLWork
.MoveFirst
For X = 1 To WorkLastRecord
aWork(X) = ![Attending Number]
.MoveNext
Next X
.Close
End With

End sub
 
Vince said:
Hello All,

I am using Visual Basic to open a saved query and then save
information in the query to an array for later use. The problem is
that the same query shows different results when opened directly vs.
when opened by Visual Basic. It is as if Visual Basic is not letting
the query fully evaluate before processing records.

The query is a subtotal query that contains several criteria set up as
"where" in the group-by box. Most of the criteria are based on one
table, one criteria is based on a second joined table. When the query
is opened directly this last criteria is correctly evaluated and the
proper records are shown. When opened in VB it is as if this criteria
did not exist. The query otherwise shows correct information except
it includes records that should not be there based on the last
criteria.

Is there a way to force visual basic to wait until a query is fully
opened before executing code that uses the resulting records. Or is
there something else that I am missing.

Any help would be greatly appreciated.

Thanks,

Vince

Partial code Follows---------------------------------
Public aWork(10) as Integar

Sub LoadWorkArray(WorkQuery as string) 'WorkQuery = query name
Dim WorkLastRecord as Integar
Dim X as Integar

Set SLWork = New ADODB.Recordset

SLWork.Open WorkQuery, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

WorkLastRecord = SLWork.RecordCount

ReDim aWork(WorkLastRecord + 1)

With SLWork
.MoveFirst
For X = 1 To WorkLastRecord
aWork(X) = ![Attending Number]
.MoveNext
Next X
.Close
End With

End sub
Click to expand...


When you open most types of recordsets, the RecordCount property is not
accurate yet. More often than not, it's 1 -- the number of records in the
recordset that have been accessed so far. Other times it may be -1,
indicating that the number of records isn't yet known. To get an accurate
count, you have to move to the end of the recordset, like this:

SLWork.Open WorkQuery, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

With SLWork
If Not .EOF Then
.MoveLast
WorkLastRecord = .RecordCount
ReDim aWork(WorkLastRecord + 1)
.MoveFirst
For X = 1 To WorkLastRecord
aWork(X) = ![Attending Number]
.MoveNext
Next X
End If
.Close
End With
 
Hello All,
Click to expand...
I am using Visual Basic to open a saved query and then save
information in the query to an array for later use.  The problem is
that the same query shows different results when opened directly vs.
when opened by Visual Basic.  It is as if Visual Basic is not letting
the query fully evaluate before processing records.
Click to expand...
The query is a subtotal query that contains several criteria set up as
"where" in the group-by box.  Most of the criteria are based on one
table, one criteria is based on a second joined table.  When the query
is opened directly this last criteria is correctly evaluated and the
proper records are shown.  When opened in VB it is as if this criteria
did not exist.  The query otherwise shows correct information except
it includes records that should not be there based on the last
criteria.
Click to expand...
Is there a way to force visual basic to wait until a query is fully
opened before executing code that uses the resulting records.  Or is
there something else that I am missing.
Click to expand...
Any help would be greatly appreciated.


Partial code Follows---------------------------------
Public aWork(10) as Integar
Click to expand...
Sub LoadWorkArray(WorkQuery as string)  'WorkQuery = query name
Dim WorkLastRecord as Integar
Dim X as Integar
Click to expand...
Set SLWork = New ADODB.Recordset
Click to expand...
SLWork.Open WorkQuery, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Click to expand...
WorkLastRecord = SLWork.RecordCount
Click to expand...
ReDim aWork(WorkLastRecord + 1)
Click to expand...
With SLWork
   .MoveFirst
   For X = 1 To WorkLastRecord
       aWork(X) = ![Attending Number]
      .MoveNext
   Next X
   .Close
End With
Click to expand...
End sub
Click to expand...

When you open most types of recordsets, the RecordCount property is not
accurate yet.  More often than not, it's 1 -- the number of records in the
recordset that have been accessed so far.  Other times it may be -1,
indicating that the number of records isn't yet known.  To get an accurate
count, you have to move to the end of the recordset, like this:

    SLWork.Open WorkQuery, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic

    With SLWork
        If Not .EOF Then
            .MoveLast
            WorkLastRecord = .RecordCount
            ReDim aWork(WorkLastRecord + 1)
            .MoveFirst
            For X = 1 To WorkLastRecord
                aWork(X) = ![Attending Number]
                .MoveNext
            Next X
        End If
        .Close
    End With

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...


Hello Dirk, Thanks for your response. I did try the movelast as you
suggested but it did not make a difference, actually the recordcount
was returning the correct number of records shown, however the records
shown included records that should not have been there.

I have figured what the problem was courtesy of the comp.databases.ms-
access group.

One of my criteria, the one the VB was ignoring, was a Not Like
"*string*" expression. Apparently ADODB.Recordset totally ignores an
expression with an * and uses a % as the wildcard character. Changed
it to the % and now it works. I had spent a lot of time looking at a
lot of things but never thought of this.

Thanks,

Vince
 
I'm surprised that it was giving you a correct record count even without the
MoveLast -- I wouldn't count on it in general. Yes, the wild-card
characters in ADO are different from those in DAO, so that explains the
different results.
 
Back
Top