using SQL with a query

[bradoirs]

I am very new to VBA and having a few problems - I have successful routines using loops on tables to compare and process information. I now need to carry out similar routines on queries and would also like to use SQL to reduce search times. I find the help system a foreign language so all assistance welcomed. My basics on tables is

Set db = CurrentDb
Set rst = db.OpenRecordset("tblproperties")
rst.MoveFirst
thisun = 0
thatun = 0
Do While rst.EOF = False
etc etc

How would I get to the same point for a query using SQL?

 

[dhookom]

get to the same point

what's your point? Your code has no value yet.

Duane
Hook'D on Access
MS Access MVP

 

[HarleyQuinn]

If I understand what you want correctly, you want to replace the value for tblproperties with a valid SQL statement e.g.

Set rst = db.OpenRecordset("SELECT * FROM YourTable WHERE YourField=1")

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[bradoirs]

What I am trying to achieve is to open a query, apply an SQL query to filter out unnecessary records then using loops to process the data. I can do it fine on a table but cannot get the SQL to work on a query

 

[HarleyQuinn]

Why do you want to open a query and then filter out records? Why not filter them out in the original query SQL?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[bradoirs]

Basically I import data from a number of sources and the data is all in slightly different formats so I use queries to standardise the data. I then merge the separate queries into a single table for my own use.

When I import data I have to import all the records but I only extract new records into my main table. What I want to do is to use the other records to check the validity of the data that I am holding - is price, balance, last contact date etc correct? Queries do not give me the flexibility or immediate reporting that I need so my long winded solution for the moment is to loop through my master table, determine the data source, open the appropriate data table and loop through to find the matching record then do whatever checks balances etc I need. This takes a long time even when I exit the loop when the record has been found and processed - I want to apply SQL to reduce the number of records to be looped through.

So that is my problem - I suspect many might say I am going about this in entirely the wrong way but I can only work with what I know. As the saying goes "a little knowledge is a dangerous thing".

 

[joelflorendo]

It sounds like at each point you are opening the whole table and looping through all the records to find the one you want, you could instead use an sql statement that will retrieve just the record you want.

use the sql example that HarleyQuinn provided with the correct WHERE clause to filter for the records you want.

Provide your code if you want some concrete examples.

 

[lameid]

Recordsets are a lot slower at managing data than queries. You should always try to do as much as possible with queries. As has been suggested use the where clause to filter your data first. The query designer is much more intuitive at first. You can use the deisiner to make your query and switch to the SQL view to have your sql statment.

Also it sounds like you may be able to use an update query or series of them to perform whatever your code is doing. Update queries have some unfortunate limitations that force the use of recordsets sometimes so it is impossible to say for sure. As Joel suggested providing code will help others help you. Some examples of your data and comments of how you know the records are new will be helpful too. I mention queries because, again, they are much faster.

 

[bradoirs]

Sorry just can't get it - I am getting paramater errors now. Can anybody put me out of my mysery and give me a snippet of code that will open a query and apply an sql query to it. Please... I need to see it working to figure it out - if I convert the query to a table it works fine but that is a long way round.

 

[lameid]

HarleyQuinn gave you an example of opening an SQL statement. You will of course have to modify it to use your table and criteria field and possibly edit the criteria.

If you still are having trouble, try posting the SQL statement you are trying to use.

 

[joelflorendo]

Maybe I'm misunderstanding what you are saying but you don't apply sql to an existing query. The query itself is already based on sql. You can either modify your sql to obtain the correct query or run the existing query and filter it for the records you want.

Try this:
Open your query in design mode and then hit the down arrow next to the view button. Switch to the SQL View. This will show you the sql behind the query. Copy this and put it into this code...

sSql = "<text copied from above>"
Set db = Currentdb
Set rst = db.OpenRecordset(sSql)

Do While rst.EOF = False
'loop through the recordset to see what you get
Loop

If you want a subset of this recordset, modify the sql using a where clause. If you're not sure what this should look like, you can just use design view and put a criteria in the design grid. Run the query to make sure it gets the records you want. Then open it in SQL view and repeat the steps above.

Or you can just open the recordset as is and filter it for the records you want. I don't use DAO recordsets very often so someone else might pitch in to show you how to use that or do a search on DAO filter to find out.