Access/Excel VBA Question

[Allen Geddes]

I have a query in Access, and I'd like to be able to return the number of
records that the query produces to a cell in Excel.

Is there any way of doing this with VBA in either Access or Excel? Thanks
in advance for your help!!

 

[Michel Walsh]

In Excel-VBA, opens a recordset on an SQL statement like: SELECT COUNT(*)
FROM yourAccessSavedQueryNameHere
and read the value under the first record, first field, that this query
return. Something like:



Dim Cnn AS ADODB.Connection
Set Cnn = New ADODB.Connection
Cnn = "PROVIDER= ... " ' by default, the connection string
Cnn.Open
Debug.Assert Cnn.= adStateOpen
yourCell = Cnn.Execute("SELECT COUNT(*) FROM somewhere").Fields(0).Value
Cnn.Close
Set Cnn = Nothing





Vanderghast, Access MVP

 

[Allen Geddes]

That worked!! Thank you so much for your help!!

In Excel-VBA, opens a recordset on an SQL statement like: SELECT COUNT(*)
FROM yourAccessSavedQueryNameHere
and read the value under the first record, first field, that this query
return. Something like:



Dim Cnn AS ADODB.Connection
Set Cnn = New ADODB.Connection
Cnn = "PROVIDER= ... " ' by default, the connection string
Cnn.Open
Debug.Assert Cnn.= adStateOpen
yourCell = Cnn.Execute("SELECT COUNT(*) FROM somewhere").Fields(0).Value
Cnn.Close
Set Cnn = Nothing





Vanderghast, Access MVP

 

[Allen Geddes]

One more question... I can get it to work for one specific query, but I
can't seem to get it to loop through all of the queries in my Access
Database. Here's my code for one specific query (Testing123), if that will
help:


Sub CountQueryResults()

' Requires Reference to Microsoft ActiveX Data Objects 2.x Library

Dim DBFullName As String
Dim Cnct As String
Dim Connection As ADODB.Connection
Dim Col As Integer

Cells.Clear

' Database information
DBFullName = "C:\Documents and Settings\Allen Geddes\My Documents\Test
Database.accdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Worksheets("Sheet1").Cells(1, 1).Value = Connection.Execute("SELECT
COUNT(*) FROM Testing123").Fields(0).Value

Connection.Close
Set Connection = Nothing

End Sub

 

[Michel Walsh]

Since we cannot use a query/table name as parameter of an SQL statement, I
suspect we have to built a different string each time:


Worksheets("Sheet1").Cells(1, i).Value = Connection.Execute("SELECT
COUNT(*) FROM " & MyQueriesNames(i} ).Fields(0).Value



where MyQueriesNames is an array holding the names of the (SELECT) queries
you want to test.


Vanderghast, Access MVP

 

[Allen Geddes]

Ahh, good idea. So, is there any way to load the query names into the array
automatically, or will that have to be done manually? Thanks again!

 

[Allen Geddes]

I mean to say, can I read the query names from Access using ADO, and load
them into the array? Or do I just have to have a list of the names of the
queries in my code, or in a range in my spreadsheet, and load the array with
that list? Thanks again!

 

[Michel Walsh]

You can try to decipher the Jet system table MSysObjects (fields Name and
Type, among others), and pump the names ... with a query


Vanderghast, Access MVP

 

[Michel Walsh]

Forgot to mention that you probably want ONLY select-type query, not Delete,
Update, etc., which may be much harder than just getting the list of name of
all the queries.

Vanderghast, Access MVP

 

[Allen Geddes]

Okay, thank you very much for all of your help!!!!!