Access/Excel VBA Question

  • Thread starter Thread starter Allen Geddes
  • Start date Start date
A

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!!
 
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
 
That worked!! Thank you so much for your help!!

Michel Walsh said:
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
Click to expand...
 
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
 
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
 
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!
 
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!
 
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
 
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
 
Back
Top