Do not run TransferSpreadSheet if Query has no data

[Dave_R]

I am using the transferspreadsheet command to export data from access to excel.

Dim strDocName As String
Dim strFileName As String

strDocName = "qryTest"
strFileName = "C:\TestFile.XLS"

strSQL = "SELECT * FROM Table_Name;"
For Each qdef In CurrentDb.QueryDefs

If qdef.Name = strDocName Then 'check if query exists
CurrentDb.QueryDefs.Delete qdef.Name 'delete old query
End If

Next qdef

Set qdf = dbs.CreateQueryDef(strDocName, strSQL)

DoCmd.OpenQuery strDocName, acViewNormal, acEdit 'Open query to run
DoCmd.Close acQuery, strDocName, acSaveYes 'close and save query
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strDocName, strFileName,True, "A-Z"


It creates a worksheet named A-Z.
If the query is empty, I dont want to export.
How to check if the query is empty?

 

[Allen Browne]

Use DLookup() on the primary key.
If it returns Null, there are no records.

As a further refinement, you might consider creating the query for export
(so it's always there), and reassigning its SQL property if you need to do
that.

This kind of thing:

Currentdb.QueryDefs("Query1").SQL = strSql
If Not IsNull(DLookup("ID", "Query1")) Then
DoCmd.Transfer...
End If

 

[Dave_R]

Thanks for the reply.
This was just an example of one sql. I am using about 8 sql, that means I
have to create 8 queries. There are 3 subs that create 3 XL files and 1 sub
has 4 sqls.

strSQL1 = "SELECT fld1, fld2 FROM Table_Name Where fld1 = 'criteria1';"
For Each qdef In CurrentDb.QueryDefs

If qdef.Name = strDocName Then 'check if query exists
CurrentDb.QueryDefs.Delete qdef.Name 'delete old query
End If

Next qdef

Set qdf = dbs.CreateQueryDef(strDocName, strSQL1)

DoCmd.OpenQuery strDocName, acViewNormal, acEdit 'Open query to run
DoCmd.Close acQuery, strDocName, acSaveYes 'close and save query
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strDocName, strFileName,True, "A=1"

strSQL2 = "SELECT fld11, fld12 FROM Table_Name Where fld11 = 'criteria2';"
For Each qdef In CurrentDb.QueryDefs

If qdef.Name = strDocName Then 'check if query exists
CurrentDb.QueryDefs.Delete qdef.Name 'delete old query
End If

Next qdef

Set qdf = dbs.CreateQueryDef(strDocName, strSQL2)

DoCmd.OpenQuery strDocName, acViewNormal, acEdit 'Open query to run
DoCmd.Close acQuery, strDocName, acSaveYes 'close and save query
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strDocName, strFileName,True, "A-2"

And so on......
Do you think I should still create a query?
~Dave

 

[Allen Browne]

Entirely up to you.

I generally use just one query for this kind of export. There's no problem
with assigning a new SQL statement to it several times in the one loop.

But if you prefer to create multiple queries on the fly and test whether
they exist and delete them if they do, that's workable too. Just seems to me
to be more work and more querydefs to manage.

 

[Dave_R]

Thank you for the reply. You are right. I changed the code, created one query
and assigned different sql to that query and tested for null records.
~Dave