473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

??? 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.

Dec 14 '06 #1
7 3695

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:
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.
Dec 14 '06 #2
"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

fc******@gmail.com wrote:
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.
Dec 14 '06 #3

ManningFan wrote:
"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?
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"

Dec 14 '06 #4
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:
"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

fc******@gmail.com wrote:
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.
Dec 14 '06 #5
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?

fc******@gmail.com wrote:
>
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.
Dec 14 '06 #6

ManningFan wrote:
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?

fc******@gmail.com wrote:

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.

Dec 15 '06 #7
Who said I didn't want to help? Lighten up a bit, Francis.

pi********@hotmail.com wrote:
>
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.
Dec 15 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Michael J. Astrauskas | last post by:
Does anyone have a function for manipulating GET variables in a URL? I want to be able to modify some parameters without affecting others. An example of what I'm looking for: Let's say the...
3
by: Mike Cocker | last post by:
Hello, I'm quite weak at PHP, so I was hoping to get some help understanding the below code. First off, I'm trying to create a "query form" that will allow me to display the results on my...
2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
13
by: Wescotte | last post by:
Here is a small sample program I wrote in PHP (running off Apache 1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data base is using DB2 V5R3M0. The client is WinXP machine using...
8
by: san | last post by:
Hi, I wanted to know if this is possible and if so, how do I do it. Say, I have a query "SELECT * FROM Table WHERE Column="some_value". This executes on a very large data set and I would like...
0
by: Rob | last post by:
I doubt this is the best way to do it, but what I came up with was to hide the XML in an HTML Comment then edit the file deleting the HTML stuff and keep the XML results. If anyone has a better...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
2
by: AJ | last post by:
Hi all, I have this monster query (at least i think it is). SELECT c.ID, c.Company_Name, p., 1 As QueryNbr FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID) LEFT JOIN Package...
3
by: cloh | last post by:
I have a pre-written Access query and one of the resulting columns is listed in this format: A B C A B C A B
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.