Determine if records qualified

[Chris]

Hi,
I have a routine which runs numerous queries via the DoCmd.OutputTo command,
so that they can be written to.xls spreadsheets and e-mailed to recipients .
E.g.:

DoCmd.OutputTo acOutputQuery, myQueryName, acFormatXLS, myFileName, False

I want after the query runs to determine if any records qualified. I don't
want to send an empty spreadsheet file via e-mail, and I don't want to open
the query as a recordset first to get a record count because some of these
queries take a long time to run and it would be wasteful of time /
resources.

What approach do you recommend? Can I open the spreadsheet file and get a
row count?

 

[Graham Mandeno]

Hi Chris,

Well, you could use automation to open the resulting Excel document and
check its contents, but can you not design a simpler query with only one or
two fields and no calculated columns, to check whether any records are being
selected? Then you could avoid completely creating the XLS file if it were
not necessary.

 

[Chris]

Boy, would I love that. Unfortunately, several of these queries are
performing "unmatched" results with tables that have several hundred
thousand rows in them, sometimes over 1 million. I can't see a way to
simplify this.

What would be the automation steps to inspect the Excel document? I'd like
to at least look into this. I'd check the record size of the resulting
file, but I'm not sure it's granular enough (especially since the varying
number of columns in the output will result in varying sized output).

Hi Chris,

Well, you could use automation to open the resulting Excel document and
check its contents, but can you not design a simpler query with only one or
two fields and no calculated columns, to check whether any records are being
selected? Then you could avoid completely creating the XLS file if it were
not necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I have a routine which runs numerous queries via the DoCmd.OutputTo
command,
so that they can be written to.xls spreadsheets and e-mailed to recipients
.
E.g.:

DoCmd.OutputTo acOutputQuery, myQueryName, acFormatXLS, myFileName, False

I want after the query runs to determine if any records qualified. I
don't
want to send an empty spreadsheet file via e-mail, and I don't want to
open
the query as a recordset first to get a record count because some of these
queries take a long time to run and it would be wasteful of time /
resources.

What approach do you recommend? Can I open the spreadsheet file and get a
row count?

 

[Graham Mandeno]

Hi Chris

The following function will tell you the number of rows used in an Excel
worksheet:

Public Function RowsInExcelSheet(sFileName As String, _
Optional vSheet As Variant = 1) As Long
Dim oApp As Excel.Application
Dim oDoc As Excel.Workbook
Set oApp = CreateObject("Excel.Application")
Set oDoc = oApp.Workbooks.OPEN(sFileName, ReadOnly:=True)
RowsInExcelSheet = oDoc.Worksheets(vSheet).UsedRange.Rows.Count
oDoc.Close
oApp.Quit
Set oDoc = Nothing
Set oApp = Nothing
End Function

The second argument can be either the name or the index number of the sheet.
If you don't supply it, it will check the first sheet.

You can add the error handling <g>.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Boy, would I love that. Unfortunately, several of these queries are
performing "unmatched" results with tables that have several hundred
thousand rows in them, sometimes over 1 million. I can't see a way to
simplify this.

What would be the automation steps to inspect the Excel document? I'd
like
to at least look into this. I'd check the record size of the resulting
file, but I'm not sure it's granular enough (especially since the varying
number of columns in the output will result in varying sized output).

Hi Chris,

Well, you could use automation to open the resulting Excel document and
check its contents, but can you not design a simpler query with only one or
two fields and no calculated columns, to check whether any records are being
selected? Then you could avoid completely creating the XLS file if it were
not necessary.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I have a routine which runs numerous queries via the DoCmd.OutputTo
command,
so that they can be written to.xls spreadsheets and e-mailed to recipients
.
E.g.:

DoCmd.OutputTo acOutputQuery, myQueryName, acFormatXLS, myFileName, False

I want after the query runs to determine if any records qualified. I
don't
want to send an empty spreadsheet file via e-mail, and I don't want to
open
the query as a recordset first to get a record count because some of these
queries take a long time to run and it would be wasteful of time /
resources.

What approach do you recommend? Can I open the spreadsheet file and
get a
row count?

 

[Jamie Collins]

The following function will tell you the number of rows used in an Excel
worksheet:

Public Function RowsInExcelSheet(sFileName As String, _
Optional vSheet As Variant = 1) As Long
Dim oApp As Excel.Application
Dim oDoc As Excel.Workbook
Set oApp = CreateObject("Excel.Application")

<snip>

Why use automation to read data in a closed Excel workbook when a
query would do? Try this:

Public Function RowsInExcelSheet( _
ByVal FileName As String, _
Optional ByVal SheetName As String) As Long

Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strSql1 As String

Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<FULL_FILENAME>;" & _
"Extended Properties='Excel 8.0'"

Const SQL As String = "" & _
"SELECT COUNT(*) FROM [<TABLE_NAME>];"

Const DEFAULT_TABLE_NAME As String = "" & _
"A:A"

' Build connection string
strCon = CONN_STRING
strCon = Replace(strCon, _
"<FULL_FILENAME>", FileName)

' Build sql
strSql1 = SQL
If Len(SheetName) > 0 Then
strSql1 = Replace(strSql1, _
"<TABLE_NAME>", SheetName & "$")
Else
strSql1 = Replace(strSql1, _
"<TABLE_NAME>", DEFAULT_TABLE_NAME)
End If

' Open connection to workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.CursorLocation = 3 ' client-side
.ConnectionString = strCon

.Open
Set rs = .Execute(strSql1)

End With

With rs

.ActiveConnection = Nothing
Con.Close

RowsInExcelSheet = rs(0)

End With

End Function


Jamie.

--