Hein-
I was thinking RecordsAffected is an ADO property, but it's also available
in DAO. There's a good example in Help:
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim strSQLChange As String
Dim strSQLRestore As String
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
' Could also Set dbsNorthwind = CurrentDb
With dbsNorthwind ' Define and execute an action query.
strSQLChange = "UPDATE Employees " & _
"SET Country = 'United States' " & _
"WHERE Country = 'USA'"
.Execute strSQLChange
' Print report of contents of the Employees
' table.
Debug.Print _
"RecordsAffected after executing query " & _
"from Database: " & .RecordsAffected
End With
So, you can open an output recordset on a table and write the query name and
the RecordsAffected to it. Your code might look like:
Dim db As DAO.Database, rstOut As DAO.Recordset, qd As DAO.QueryDef
Set db = CurrentDb
Set rstOUT = db.OpenRecordset("tblResults")
For Each qd In db.QueryDefs
If Left(qd.Name) = "Qu" Then
' Execute the query
qd.Execute
' Start a new record
rstOut.Addnew
' Set the name
rstOut!QueryName = qd.Name
' Set the update count
rstOut!Records = qd.RecordsAffected
' Write the row
rstOut.Update
End If
Next qd
' Close out
rstOut.Close
Set rstOut = Nothing
Set qd = Nothing
Set db = Nothing
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
> -----Original Message-----
> From: [email protected]
> [mailto:[EMAIL PROTECTED] On Behalf Of heinvdvegt
> Sent: Friday, August 05, 2005 9:00 AM
> To: [email protected]
> Subject: [ms_access] Re: rowcount from several tables/queries
>
>
> John, thanks for your reply,
> Your suggestion sound good, but how can I accomplish this.
> I'm but a beginning Access'er with so far little knowledge of VBA
> Is it e.g. possible to store the results in a table (queryname and
> numbver of rows?)
>
> How you can help me?
> Thanks
> Hein
>
> PS
> I see you use ADO, while in most code I try to read I see DAO. Can
> you tell me the difference?
>
>
>
>
> --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
> > I believe you can also execute each query as a Command object in
> ADO and
> > check the RowsAffected property.
> >
> > John Viescas, author
> > "Building Microsoft Access Applications"
> > "Microsoft Office Access 2003 Inside Out"
> > "Running Microsoft Access 2000"
> > "SQL Queries for Mere Mortals"
> > http://www.viescas.com/
> >
> > > -----Original Message-----
> > > From: [email protected]
> > > [mailto:[EMAIL PROTECTED] On Behalf Of Robin
> > > Sent: Friday, August 05, 2005 4:02 AM
> > > To: [email protected]
> > > Subject: [ms_access] Re: rowcount from several tables/queries
> > >
> > >
> > > Hello Hein,
> > >
> > > First of all, excuse me for my little joke.
> > >
> > > You need to use a function to step through your queries and
> return
> > > the record count. Because your prefixing your query names
> > > with "QuAppend" it makes life a little easier. This means that a
> > > varying number of queries wont effect the function.
> > >
> > > First we need to set up a variable for the querydefs:
> > >
> > > Function RecCount() As String
> > >
> > > Dim qry As QryDef
> > > Dim rs As DAO.Recordset
> > > Dim ret As String
> > >
> > > 'Then we step through the query collection
> > >
> > > For Each qry In CurrentDb.QryDefs
> > > Set rs = CurrentDb.OpenRecordset(qry)
> > > rs.MoveLast
> > > ret = ret & qry.Name & " = " & rs.RecordCount & vbCrLf
> > > rs.Close
> > > Set rs = Nothing
> > > Next qry
> > >
> > > RecCount = ret
> > >
> > > End Function
> > >
> > > Right, now that was straight of the top of my head so I'm not
> sure
> > > it'll work (haven't tried it).
> > >
> > > Give it a try and if it doesn't work let me know and I'll put up
> > > some proven code.
> > >
> > > Regards
> > > Robin.
> > >
> > >
> > > --- In [email protected], "heinvdvegt" <[EMAIL PROTECTED]>
> > > wrote:
> > > > Yes, please.
> > > > If you can help me I would be most greatful
> > > > Regards Hein
> > > >
> > > >
> > > > --- In [email protected], "Robin" <[EMAIL PROTECTED]>
> wrote:
> > > > > Hello Hein,
> > > > >
> > > > > Of course it can be done. Do you want to know how?
> > > > >
> > > > > Regards
> > > > > Robin.
> > > > >
> > > > >
> > > > > --- In [email protected], "heinvdvegt"
> <[EMAIL PROTECTED]>
> > > > > wrote:
> > > > > > Hallo,
> > > > > > I hope somebody can help me with my next question.
> > > > > >
> > > > > > In one of my my databases I need to transfer quite often
> > > > > > large quantities of data from TbTemp and TbTempMiles to
> > > TbTimes.
> > > > > >
> > > > > > To do this I have several queries that all start
> > > with "QuAppend"
> > > > > > followed with a distance.
> > > > > >
> > > > > > Before I start shifting the data I would like to see how
> many
> > > > > > rows/records there are in each query, as well as there are
> in
> > > > > > TbTimes. Reason for this is that I want to check if
> everything
> > > > > goes
> > > > > > according to plan.
> > > > > >
> > > > > > Now for the question: is it possible to write a procedure
> that
> > > > > gives
> > > > > > me all this rowcounts. The outcome can be stored in a
> table or
> > > > > > query, I have no preference.
> > > > > >
> > > > > > An extra problem is that the number of queries will grow.
> My
> > > > idea
> > > > > is
> > > > > > that if I make sure that each query starts with "QuAppend"
> it
> > > > > > will/must automatically be involved in this procedure
> > > > > >
> > > > > > Can it be done??
> > > > > >
> > > > > > Thanks in advance for your help
> > > > > > Hein
> > >
> > >
> > >
> > >
> > > ------------------------ Yahoo! Groups Sponsor
> > > --------------------~-->
> > > <font face=arial size=-1><a
> > > href="http://us.ard.yahoo.com/SIG=12hu2o0ug/M=362131.6882499.7
> > > 825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123239775
> > > /A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org
> > > ">Get Bzzzy! (real tools to help you find a job) Welcome to
> > > the Sweet Life - brought to you by One Economy</a>.</font>
> > > --------------------------------------------------------------
> > > ------~->
> > >
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >
>
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> <font face=arial size=-1><a
> href="http://us.ard.yahoo.com/SIG=12h1bt6q4/M=362131.6882499.7
> 825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123257664
> /A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org
> ">Get Bzzzy! (real tools to help you find a job) Welcome to
> the Sweet Life - brought to you by One Economy</a>.</font>
> --------------------------------------------------------------
> ------~->
>
>
> Yahoo! Groups Links
>
>
>
>
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
<font face=arial size=-1><a
href="http://us.ard.yahoo.com/SIG=12heo2m68/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123263731/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org
">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life
- brought to you by One Economy</a>.</font>
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
