??? Manipulating Query Results In VBA ???

I'm an experienced Access user, but very new to coding VBA in Access.
I'd like to do the following:

1) Develop a basic query in the query designer.
2) Call that query from a VBA script
3) Manipulate the data from the results set in the VBA script (i.e.
read data from fields into variables, etc.)

This conceptually seems like it should be an easy thing to do. However,
I'm finding it difficult to find articles or postings on the subject.

I'm finding a lot of posting that involves queries that are actually
written in the VBA code. But I don't want the SQL statements in my VBA.
I want them encapsulated.

The reason being, I have existing queries from another database. I'd
like to import those queries into my db, and then create VBA scripts to
run and perform analysis on the result set.

Can anyone provide a VBA code sample of how I can do this? If it's
complicated to do, and you don't have a sample handy, can you point
me to a good website where I might find one?

Thanks.

do you want to manually import the queries once-off?
if so just right click in the query window, select import and follow
the wizard.
However, if you want to dynamically import the queries using vba, then
this is a different matter. I have situations where sql statements are
stored in another database and imported and saved as local queries as
required. you may want to just use the import method - it's really down
to what you are trying to achieve.

after that it's a matter of learning how to open and loop through
recordsets with vba. there's TONS of great examples out there written
in both ADO and DAO - but this is the kind of thing you are looking for

'Begin aircode----------------

dim rst as dao.recordset
set rst = currentdb.openrecordset("YourQueryName")

if rst.eof then err.raise 1234,,"No Data in recordset"

rst.movefirst
do while not rst.eof
yourVariable1 = rst!QueryFieldName1
yourVariable2 = rst!QueryFieldName2

rst.movenext
loop

rst.close

'End aircode-----------------

On Dec 14, 4:23 pm, "fcolo...@gmail.com" <fcolo...@gmail.comwrote:

Quote:

I'm an experienced Access user, but very new to coding VBA in Access.
I'd like to do the following:
>
1) Develop a basic query in the query designer.
2) Call that query from a VBA script
3) Manipulate the data from the results set in the VBA script (i.e.
read data from fields into variables, etc.)
>
This conceptually seems like it should be an easy thing to do. However,
I'm finding it difficult to find articles or postings on the subject.
>
I'm finding a lot of posting that involves queries that are actually
written in the VBA code. But I don't want the SQL statements in my VBA.
I want them encapsulated.
>
The reason being, I have existing queries from another database. I'd
like to import those queries into my db, and then create VBA scripts to
run and perform analysis on the result set.
>
Can anyone provide a VBA code sample of how I can do this? If it's
complicated to do, and you don't have a sample handy, can you point
me to a good website where I might find one?
>
Thanks.

"I'm an experienced Access user, but very new to coding VBA in Access."

Huh? In other words, you're a n00b. People who think they're
"experienced" just because they've used a form or (at most) threw a few
haphazzard queries together are fooling no-one but themselves. You're
an "experienced" user when you know how to normalize data, when you
know how to set up a proper relational database and when you know how
to write proper SQL instead of letting a wizard do it for you. If you
don't know what a recordset is or how to call data from one, you're FAR
from "experienced".

That being said, the standard code would be:

Dim rec As Recordset
Dim db As Database
Dim strVar1, strVar2, strVar3 as String

Set db = CurrentDb
Set rec = db.OpenRecordset("qryYourQuery")

Do While rec.EOF = False
strVar1 = rec("Field1")
strVar2 = rec("Field2")
strVar3 = rec("Field3")

rec.MoveNext
Loop

If your query is going to return more than one record, you're going to
have to learn how to populate a multi-dimentional array with your query
results, because those variables (strVar1, etc...) are going to be
re-written for each new record in qryYourQuery

fcolon75@gmail.com wrote:

Quote:

I'm an experienced Access user, but very new to coding VBA in Access.
I'd like to do the following:
>
1) Develop a basic query in the query designer.
2) Call that query from a VBA script
3) Manipulate the data from the results set in the VBA script (i.e.
read data from fields into variables, etc.)
>
This conceptually seems like it should be an easy thing to do. However,
I'm finding it difficult to find articles or postings on the subject.
>
I'm finding a lot of posting that involves queries that are actually
written in the VBA code. But I don't want the SQL statements in my VBA.
I want them encapsulated.
>
The reason being, I have existing queries from another database. I'd
like to import those queries into my db, and then create VBA scripts to
run and perform analysis on the result set.
>
Can anyone provide a VBA code sample of how I can do this? If it's
complicated to do, and you don't have a sample handy, can you point
me to a good website where I might find one?
>
Thanks.

ManningFan wrote:

Quote:

"I'm an experienced Access user, but very new to coding VBA in Access."
>
Huh? In other words, you're a n00b. People who think they're
"experienced" just because they've used a form or (at most) threw a few
haphazzard queries together are fooling no-one but themselves. You're
an "experienced" user when you know how to normalize data, when you
know how to set up a proper relational database and when you know how
to write proper SQL instead of letting a wizard do it for you. If you
don't know what a recordset is or how to call data from one, you're FAR
from "experienced".

I think it's important to remember that there's always someone out
there more experienced and knowledgeable than we are. How is telling
someone one's personal standards for 'n00b'ness helpful?

Quote:

That being said, the standard code would be:
>
Dim rec As Recordset
Dim db As Database
Dim strVar1, strVar2, strVar3 as String
>
Set db = CurrentDb
Set rec = db.OpenRecordset("qryYourQuery")
>
Do While rec.EOF = False
strVar1 = rec("Field1")
strVar2 = rec("Field2")
strVar3 = rec("Field3")
>
rec.MoveNext
Loop

If you're using DAO. Not all of us do. But it's a good place to
start. If you have any knowledge of VB or VBA and its object model I'd
recommend opening up the VBA window, clicking Help, then 'Microsoft DAO
3.6' , and then the 'DAO Overview' in the table of contents. Better
still are numerous good books introducing VBA and DAO for various
versions of Access. My personal favorites would be any of the 'Access
Developer's Handbook' by Litwin, Getz et al. These can usually be
found used pretty inexpensively on Amazon.

Bruce

"A man is never so tall as when he stands on the chest of someone less
knowledgeable"

BillCo, thanks for the help. You gave me exactly the answer I was
looking for. I knew it had to be something simple like that.

Mr. ManningFan, you need to relax and not wrap so much of your self
worth into the fact that you're handy with Access. No one is
impressed. Belittling strangers looking to learn makes you look
pathetic.

ManningFan wrote:

Quote:

"I'm an experienced Access user, but very new to coding VBA in Access."
>
Huh? In other words, you're a n00b. People who think they're
"experienced" just because they've used a form or (at most) threw a few
haphazzard queries together are fooling no-one but themselves. You're
an "experienced" user when you know how to normalize data, when you
know how to set up a proper relational database and when you know how
to write proper SQL instead of letting a wizard do it for you. If you
don't know what a recordset is or how to call data from one, you're FAR
from "experienced".
>
That being said, the standard code would be:
>
Dim rec As Recordset
Dim db As Database
Dim strVar1, strVar2, strVar3 as String
>
Set db = CurrentDb
Set rec = db.OpenRecordset("qryYourQuery")
>
Do While rec.EOF = False
strVar1 = rec("Field1")
strVar2 = rec("Field2")
strVar3 = rec("Field3")
>
rec.MoveNext
Loop
>
If your query is going to return more than one record, you're going to
have to learn how to populate a multi-dimentional array with your query
results, because those variables (strVar1, etc...) are going to be
re-written for each new record in qryYourQuery
>
fcolon75@gmail.com wrote:

Quote:

I'm an experienced Access user, but very new to coding VBA in Access.
I'd like to do the following:

1) Develop a basic query in the query designer.
2) Call that query from a VBA script
3) Manipulate the data from the results set in the VBA script (i.e.
read data from fields into variables, etc.)

This conceptually seems like it should be an easy thing to do. However,
I'm finding it difficult to find articles or postings on the subject.

I'm finding a lot of posting that involves queries that are actually
written in the VBA code. But I don't want the SQL statements in my VBA.
I want them encapsulated.

The reason being, I have existing queries from another database. I'd
like to import those queries into my db, and then create VBA scripts to
run and perform analysis on the result set.

Can anyone provide a VBA code sample of how I can do this? If it's
complicated to do, and you don't have a sample handy, can you point
me to a good website where I might find one?

Thanks.

Yeah, but it's fun. I always like to bring "experienced" users back to
Earth. In the end I gave you the code you needed, didn't I?

fcolon75@gmail.com wrote:

Quote:

>
Mr. ManningFan, you need to relax and not wrap so much of your self
worth into the fact that you're handy with Access. No one is
impressed. Belittling strangers looking to learn makes you look
pathetic.

ManningFan wrote:

Quote:

Yeah, but it's fun. I always like to bring "experienced" users back to
Earth. In the end I gave you the code you needed, didn't I?
>
fcolon75@gmail.com wrote:

Quote:

Mr. ManningFan, you need to relax and not wrap so much of your self
worth into the fact that you're handy with Access. No one is
impressed. Belittling strangers looking to learn makes you look
pathetic.

That's kind of like saying "well, I served you your dinner. (But I
spit in it first.)" If you don't want to help, then just don't say
anything.