Excel/Access Question

[Allen Geddes]

I have a bunch of queries in Access that I run daily. I also have an Excel
spreadsheet, and in that spreadsheet, I need to know how many results each
Access Query produced, and put that number in a different cell for each
Access Query.

Right now, I run each query manually, and type the number of records it
produced into my Excel spreadsheet... I'm looking for a way using VBA to
automatically input that number into Excel. Any ideas? Thanks in advance!!

 

[UglyChicken]

I have a bunch of queries in Access that I run daily. I also have an Excel
spreadsheet, and in that spreadsheet, I need to know how many results each
Access Query produced, and put that number in a different cell for each
Access Query.

Right now, I run each query manually, and type the number of records it
produced into my Excel spreadsheet... I'm looking for a way using VBA to
automatically input that number into Excel.  Any ideas?  Thanks in advance!!

Create some queries that count the number of records ie something
like:
SELECT COUNT(*) AS MyCount FROM TABLE1

Then in Excel create a sheet and select Date-->Import External Data--

Import Data

Go and find your database and then link to the count query

 

[David]

Hi,

Export the whole thing to Excel, then create formulas below the data with
CountIf. It depends on what you are trying to count, but muliple formulas
referencing, in the formula, different items in a column is not hard or even
different items in different columns, again is not hard. Giving us an example
of what your are trying to count would be good. This would possibly reduce
the number of exports to different files. Depends on the data and what you
are trying to achieve in the end.

1 z
2 s
3 s
=countif(A1:A3, 1) "=1"
=countif(A1:A3, 2) "=1"
=countif(B1:B3, z) "=1"
=countif(B1:B3, s) "=2"

David

 

[Allen Geddes]

Thanks for the replies guys! I was able to do it using ADO and some queries
like UglyChicken suggested... but I don't have to import the entire database
every time I want to do it... so I'm happy! Thanks again!