By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Visual Basic Not Waiting for Query to fully evaluate???

P: n/a
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
Dec 28 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I'm no expert but would it not be worth adding a line before you go to the
beginning of the recordset to go to the end of the recordset. I think this
would force the query to be fully executed before evaluating the values
returned from it.

Something like:

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

Regards,

Mark

"Vince" <VA*********@yahoo.comwrote in message
news:46**********************************@e10g2000 prf.googlegroups.com...
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

Dec 28 '07 #2

P: n/a
On Dec 28, 6:14*pm, "Mark" <mreed1...@btinternet.comwrote:
I'm no expert but would it not be worth adding a line before you go to the
beginning of the recordset to go to the end of the recordset. I think this
would force the query to be fully executed before evaluating the values
returned from it.

Something like:

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

Regards,

Mark

"Vince" <VArgenzi...@yahoo.comwrote in message

news:46**********************************@e10g2000 prf.googlegroups.com...
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- Hide quoted text -

- Show quoted text -
Hello Lyle,

You got it with using an "*" vs "%". The query was using a Not Like
"*string*" expression. Changed it to use % instead and now it works.
You do not know how long I have been looking at everything but the
string. While the query opens normally from Access the ADODB
apparently totally ignores an expression that uses an *.

Thanks for everybody's help.

Vince
Dec 28 '07 #3

P: n/a
Vince <VA*********@yahoo.comwrote in
news:38**********************************@e4g2000h sg.googlegroups.com
:
You got it with using an "*" vs "%". The query was using a Not
Like "*string*" expression. Changed it to use % instead and now
it works. You do not know how long I have been looking at
everything but the string. While the query opens normally from
Access the ADODB apparently totally ignores an expression that
uses an *.
Why are you using ADO? Makes no sense to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Dec 29 '07 #4

P: n/a
On Dec 28 2007, 8:23*pm, "David W. Fenton"
<XXXuse...@dfenton.com.invalidwrote:
Vince <VArgenzi...@yahoo.comwrote innews:38**********************************@e4g200 0hsg.googlegroups.com
:
You got it with using an "*" vs "%". *The query was using a Not
Like "*string*" expression. *Changed it to use % instead and now
it works. You do not know how long I have been looking at
everything but the string. *While the query opens normally from
Access the ADODB apparently totally ignores an expression that
uses an *.

Why are you using ADO? Makes no sense to me.

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/
Hi David,

It was a way of opening a query inside VB to populate an array. I
also tried opening the query as the DAO recordset type of object and
got the same results. I had done this in a number of other queries
but this is the first one I had a problem with. I would be open to a
better approach.

Thanks,

Vince
Jan 1 '08 #5

P: n/a
On Jan 1, 4:30*pm, Vince <VArgenzi...@yahoo.comwrote:
On Dec 28 2007, 8:23*pm, "David W. Fenton"

<XXXuse...@dfenton.com.invalidwrote:
Vince <VArgenzi...@yahoo.comwrote innews:38**********************************@e4g200 0hsg.googlegroups.com
:
You got it with using an "*" vs "%". *The query was using a Not
Like "*string*" expression. *Changed it to use % instead and now
it works. You do not know how long I have been looking at
everything but the string. *While the query opens normally from
Access the ADODB apparently totally ignores an expression that
uses an *.
Why are you using ADO? Makes no sense to me.
--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/

Hi David,

It was a way of opening a query inside VB to populate an array. *I
also tried opening the query as the DAO recordset type of object and
got the same results. *I had done this in a number of other queries
but this is the first one I had a problem with. *I would be open to a
better approach.

Thanks,

Vince- Hide quoted text -

- Show quoted text -
Excuse me, I meant to say I had tried opening the recordset as its
default object type and got the same results.
Jan 1 '08 #6

P: n/a
Vince <VA*********@yahoo.comwrote in
news:58**********************************@e50g2000 hsh.googlegroups.co
m:
Excuse me, I meant to say I had tried opening the recordset as
its default object type and got the same results.
The default object type depends on what references your database
has. Which references you get by default depends on which version of
Access you created the MDB with.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 1 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.