query to table 
Author Message
 query to table

I am a novice VB and module user and am having trouble
accessing the results of a query in a module. If I try to
access each resulting record of the query in my module by
using a loop I get 9000 more than are actually there.  I
am not sure how to properly open this query as a recordset
in my module(I think thats the problem).  I figure I
should try to write the query results out to a table and
just use the table but am unsure how to do that. thanks,


Sat, 03 Apr 2004 00:25:37 GMT  
 query to table
If it's a relatively simple query, then there's no need to
dump the results to a table. You should be able to access
each record like this:

*****

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("MyQueryName")

rst.MoveFirst

Do Until rst.EOF
   rst.Edit
   rst!FieldName = "SomeValue"
   (etc)
   rst.Update
Loop

rst.Close
Set rst = Nothing
Set dbs = Nothing

*****

If your using ADO instead of DAO then look up the syntax
and the idea is the same.

Quote:
>-----Original Message-----
>I am a novice VB and module user and am having trouble
>accessing the results of a query in a module. If I try to
>access each resulting record of the query in my module by
>using a loop I get 9000 more than are actually there.  I
>am not sure how to properly open this query as a
recordset
>in my module(I think thats the problem).  I figure I
>should try to write the query results out to a table and
>just use the table but am unsure how to do that. thanks,
>.



Sat, 03 Apr 2004 01:52:10 GMT  
 query to table

Quote:

>I am a novice VB and module user and am having trouble
>accessing the results of a query in a module. If I try to
>access each resulting record of the query in my module by
>using a loop I get 9000 more than are actually there.  I
>am not sure how to properly open this query as a recordset
>in my module(I think thats the problem).  I figure I
>should try to write the query results out to a table and
>just use the table but am unsure how to do that. thanks,

As John Osmond notes, you don't need a temp table. I strongly suspect
that you are seeing "extra" records because your query is not
correctly formed; perhaps you are including two tables without a join
clause.

Could you post the SQL of the query and/or the code that generates it,
up to the openrecordset action?

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Sat, 03 Apr 2004 04:31:58 GMT  
 query to table
The query should be fixed to do what you need, putting the
results of your query into a table will not improve matters.
Post your query's SQL so someone can  help you with it.

Marsh


Quote:
>I am a novice VB and module user and am having trouble
>accessing the results of a query in a module. If I try to
>access each resulting record of the query in my module by
>using a loop I get 9000 more than are actually there.  I
>am not sure how to properly open this query as a recordset
>in my module(I think thats the problem).  I figure I
>should try to write the query results out to a table and
>just use the table but am unsure how to do that. thanks,



Sat, 03 Apr 2004 05:28:27 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Querying a table to modify another table

2. Queries on tables from multiple databases

3. Querying undefined tables

4. Query leaves table exclusively locked

5. Query->Table (VBA?) Access 95

6. How to create a form to query a table

7. Query of Tables/Customized Fields

8. from access query to table in word

9. How do i query a table usind DAO?

10. Query from Tables

11. Error 3037 cant open any more queries or tables

12. how to query muliply tables from asp page ??

 

 
Powered by phpBB® Forum Software