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

On Dec 28, 4:19 pm, Vince <VArgenzi... (AT) yahoo (DOT) com> wrote:
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.

On Dec 28, 4:41 pm, lyle <lyle.fairfi... (AT) gmail (DOT) com> wrote:
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.

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" <VArgenziano (AT) yahoo (DOT) com> wrote

On Dec 28, 6:14*pm, "Mark" <mreed1... (AT) btinternet (DOT) com> wrote:
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

On Dec 28 2007, 8:23*pm, "David W. Fenton"
<XXXuse... (AT) dfenton (DOT) com.invalid> wrote:
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

On Jan 1, 4:30*pm, Vince <VArgenzi... (AT) yahoo (DOT) com> wrote:
Excuse me, I meant to say I had tried opening the recordset as its
default object type and got the same results.

On Jan 1, 5:19*pm, "David W. Fenton" <XXXuse... (AT) dfenton (DOT) com.invalid>
wrote:
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