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

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

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

On Dec 28, 4:19 pm, Vince wrote:
> 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

I use ADODB a lot but I can't guess why you are getting more records
than yu think you should. Are you using wild cards such as "*";
generally ADODB will epxect "%".

Is this a DAO saved query?
If so have you Declared it as such, Initialized it and Debug.Print
(ed) its SQL. Is the SQL what you think it should be?

My method of getting an array of records (one field):

Dim Array0fSchools$()
Array0fSchools = Split(CurrentProject.Connection.Execute("SELECT
[Name] FROM Schools ORDER BY Name").GetString(adClipString, , "",
vbNewLine, ""), vbNewLine)

The last line is all one line.
If the SQL is complicated I build it and assing it to a string
variable.

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

On Dec 28, 4:41 pm, lyle wrote:
> On Dec 28, 4:19 pm, Vince wrote:
>
>
>
> > 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
>
> I use ADODB a lot but I can't guess why you are getting more records
> than yu think you should. Are you using wild cards such as "*";
> generally ADODB will epxect "%".
>
> Is this a DAO saved query?
> If so have you Declared it as such, Initialized it and Debug.Print
> (ed) its SQL. Is the SQL what you think it should be?
>
> My method of getting an array of records (one field):
>
> Dim Array0fSchools$()
> Array0fSchools = Split(CurrentProject.Connection.Execute("SELECT
> [Name] FROM Schools ORDER BY Name").GetString(adClipString, , "",
> vbNewLine, ""), vbNewLine)
>
> The last line is all one line.
> If the SQL is complicated I build it and assing it to a string
> variable.

I neglected to mention that this gives one extra empty element at the
end of the array, which can be dealt with any number of ways.

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

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" wrote in message
news:463889ab-b8df-4fd7-a442-0a3b084fc89d@e10g2000prf.google groups.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

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

On Dec 28, 6:14=A0pm, "Mark" wrote:
> 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
> =A0 =A0 .MoveLast
> =A0 =A0 .MoveFirst
> =A0 =A0 For X =3D 1 To WorkLastRecord
> =A0 =A0 =A0 =A0 aWork(X) =3D ![Attending Number]
> =A0 =A0 =A0 =A0.MoveNext
> =A0 =A0 Next X
> =A0 =A0 .Close
> =A0End With
>
> Regards,
>
> Mark
>
> "Vince" wrote in message
>
> news:463889ab-b8df-4fd7-a442-0a3b084fc89d@e10g2000prf.google groups.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. =A0The problem is
> > that the same query shows different results when opened directly vs.
> > when opened by Visual Basic. =A0It 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. =A0Most of the criteria are based on one
> > table, one criteria is based on a second joined table. =A0When the query=

> > is opened directly this last criteria is correctly evaluated and the
> > proper records are shown. =A0When opened in VB it is as if this criteria=

> > did not exist. =A0The 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. =A0Or 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) =A0'WorkQuery =3D query name
> > Dim WorkLastRecord as Integar
> > Dim X as Integar
>
> > Set SLWork =3D New ADODB.Recordset
>
> > SLWork.Open WorkQuery, CurrentProject.Connection, adOpenKeyset,
> > adLockOptimistic
>
> > WorkLastRecord =3D SLWork.RecordCount
>
> > ReDim aWork(WorkLastRecord + 1)
>
> > With SLWork
> > =A0 =A0.MoveFirst
> > =A0 =A0For X =3D 1 To WorkLastRecord
> > =A0 =A0 =A0 =A0aWork(X) =3D ![Attending Number]
> > =A0 =A0 =A0 .MoveNext
> > =A0 =A0Next X
> > =A0 =A0.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

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

Vince wrote in
news:38262989-0625-449d-8d44-4ea11e4df6e5@e4g2000hsg.googleg roups.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/

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

On Dec 28 2007, 8:23=A0pm, "David W. Fenton"
wrote:
> Vince wrote innews:38262989-0625-449d-8d44-4ea11e4=
df6e5@e4g2000hsg.googlegroups.com
> :
>
> > You got it with using an "*" vs "%". =A0The query was using a Not
> > Like "*string*" expression. =A0Changed it to use % instead and now
> > it works. You do not know how long I have been looking at
> > everything but the string. =A0While 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 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.com/=

> usenet at dfenton dot com =A0 =A0http://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

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

On Jan 1, 4:30=A0pm, Vince wrote:
> On Dec 28 2007, 8:23=A0pm, "David W. Fenton"
>
>
>
>
>
> wrote:
> > Vince wrote innews:38262989-0625-449d-8d44-4ea11=
e4df6e5@e4g2000hsg.googlegroups.com
> > :
>
> > > You got it with using an "*" vs "%". =A0The query was using a Not
> > > Like "*string*" expression. =A0Changed it to use % instead and now
> > > it works. You do not know how long I have been looking at
> > > everything but the string. =A0While 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 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.co=
m/
> > usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/
>
> Hi David,
>
> It was a way of opening a query inside VB to populate an array. =A0I
> also tried opening the query as the DAO recordset type of object and
> got the same results. =A0I had done this in a number of other queries
> but this is the first one I had a problem with. =A0I 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.

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

Vince wrote in
news:58d9285f-2ae9-4bae-b05f-9fa959c8e101@e50g2000hsh.google groups.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/

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

On Jan 1, 5:19=A0pm, "David W. Fenton"
wrote:
> Vince wrote innews:58d9285f-2ae9-4bae-b05f-9fa959c=
8e101@e50g2000hsh.googlegroups.co
> m:
>
> > Excuse me, =A0I 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 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.com/=

> usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/

I did check references and it does list DAO before Activex data
objects. This is the MS office original order and prior to this have
not really given it much thought. For my part the only time I use
recordset objects in VB is to open saved queries as described above so
it has not been an issue but I will be looking at it more due to the
problem that I had with my query's wildcard expression.

Thanks,

Vince

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

Vince wrote in
news:1be73b16-27e2-46c8-9b6c-6b5dd6611892@i3g2000hsf.googleg roups.com
:

> On Jan 1, 5:19 pm, "David W. Fenton"
> wrote:
>> Vince wrote
>> innews:58d9285f-2ae9-4bae-b05f-9fa959c
> 8e101@e50g2000hsh.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.
>
> I did check references and it does list DAO before Activex data
> objects. This is the MS office original order and prior to this
> have not really given it much thought. For my part the only time I
> use recordset objects in VB is to open saved queries as described
> above so it has not been an issue but I will be looking at it more
> due to the problem that I had with my query's wildcard expression.

I never ever use ADO, but, in fact, I preface all my variable
declarations with library:

Dim db As DAO.Database
Dim rs As DAO.Recordset

The first is not even necessary, as the Database data type is
specific to DAO, but it just makes the code clear and easy to use,
and I'm guaranteed it will never break as long as there's a DAO
reference.

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