TransferSpreadsheet/TransferText: SQL Size Limit?

P

PeteCresswell

I've got a little routine that accepts a pointer to an opening report
and does a TransferSpreadsheet using the report's .RecordSource as the
input.

If a global variable is set, Report_Open calls this routine and then
cancels the report.

Works for 29 out of thirty reports - but abends with ""The table name
you entered doesn't follow {application name} object-naming rules." on
one report.

If I intercept the .RecordSource SQL and paste it into a new query,
the query runs ok and there do not seem tb any non-standard table
names.

But it's quite a bit of SQL. TransferText throws the same error, and
an Immediate window cannot handle it without inserting paragraph
breaks.

I'm wondering if it's something about the size of the SQL.

Anybody been here?

For the masochistly-inclined, here's the SQL - followed by the
routine.

SQL:
=================================================
SELECT qryTradingAccounts_Active.TradingAccountID,
qryTradingAccounts_Active.CashBeginDayAmount,
qryTradingAccounts_Active.CashBeginDayDate,
qryTradingAccounts_Active.ClientName_Moxy, Val([SumOfParAmount_Net] &
"") AS AccountPar, tblStrategy.StrategyShortName,
tblStrategy.StrategyLongName,
qryTradingAccounts_Active.TradingAccountName,
tblTradingAccount_1.TradingAccountName AS ParentName,
qryTradingAccounts_Active.SpecialInstructions,
qryTradingAccounts_Active.TradingAccountNumber, IIf((Val
([SumOfParAmount_Net] & "")+Val([tblTradingAccount].
[CashBeginDayAmount] & ""))>0,Val([tblTradingAccount].
[CashBeginDayAmount] & "")/(Val([SumOfParAmount_Net] & "")+Val
([tblTradingAccount].[CashBeginDayAmount] & "")),0) AS PercentAccount
FROM tblStrategy INNER JOIN (((qryTradingAccounts_Active LEFT JOIN
qryHoldings_ByTradingAccount_Immediate ON
qryTradingAccounts_Active.TradingAccountID =
qryHoldings_ByTradingAccount_Immediate.TradingAccountID) INNER JOIN
tblTradingAccountStrategy ON
qryTradingAccounts_Active.TradingAccountID =
tblTradingAccountStrategy.TradingAccountID) LEFT JOIN
tblTradingAccount AS tblTradingAccount_1 ON
qryTradingAccounts_Active.TradingAccountID_LadderParent =
tblTradingAccount_1.TradingAccountID) ON tblStrategy.StrategyID =
tblTradingAccountStrategy.StrategyID
WHERE (tblTradingAccount.CashBeginDayAmount<>0) AND
(tblTradingAccount.CashBeginDayAmount Is Not Null) AND
(tblStrategy.StrategyID=2)
=================================================

Routine:
=================================================
Public Function Excel_QuickAndDirtyRenditionOfReportRecordSource(ByRef
theReport As Report)
20000 DebugStackPush mModuleName & ":
Excel_QuickAndDirtyRenditionOfReportRecordSource"
20001 On Error GoTo
Excel_QuickAndDirtyRenditionOfReportRecordSource_err

' PURPOSE: To provide a means for a Report_Open() event to send
the report's
' output to Excel instead of rendering it on the MS
Access report.
' ACCEPTS: - Pointer to the calling report
'
' NOTES: 1) We would *like* to check the RS to see if there are
any records and
' issue a notification instead of just hitting the
user with an empty
' spreadsheet. However, some queries will contain
references to
' fields on forms (e.g. Forms!frmHome!txtAsOfDate) and
these references
' will not resolve at the VBA level - although for
some reason
' DoCmd.TransferSpreadsheet does not have any problem
with them.

20002 Dim myRS As DAO.Recordset
Dim mySS As Excel.Application

Dim tempPath As String
Dim xlsPath As Variant
Dim okToProceed As Boolean

' ------------------------------------------------
' Get path to user's Temp() dir on Citrix server

20010 tempPath = windowsTempPath_Get()

20020 If IsNull(tempPath) Then
20021 BugAlert True, "Unable to retrieve path to Windows' 'Temp'
directory."
20029 Else
' ------------------------------------------------
' - Concoct destination path for spreadsheet
' - Use report's .RecordSource to create spreadsheet

20030 With theReport
'20031 Set myRS = CurrentDb.OpenRecordset(.RecordSource,
dbOpenSnapshot, dbForwardOnly)
'20032 If ((myRS.BOF = True) And (myRS.EOF = True)) Then
'20033 MsgBox "There are no records that qualify for this
report.", vbExclamation, "Cannot Create Excel Spreadsheet"
'20034 Else
20035 xlsPath = tempPath & .Caption & "." & CurrentUserGet() &
"." & Format$(Now(), "yyyy mm-dd hh-nn-ss") & ".xls"
'20036 DoCmd.TransferText acExportDelim, , .RecordSource,
xlsPath
20036 DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel7, .RecordSource, xlsPath
20037 okToProceed = True
'20038 End If
20039 End With
20099 End If

' ------------------------------------------------
' - Remove any default sheets that the user's Excel
' defaults may have created
' - Re-enable event handling
' - Open up the spreadsheet we just created
' - Clear the flag that causes reports tb rendered
' in Excel (so subsequent "Print" or "Preview"
' clicks on the same report will not force Excel)

20110 If okToProceed = True Then
20111 If SpreadSheetOpen_Existing(xlsPath, mySS) = True Then
20112 On Error Resume Next
20113 With mySS
20119 .Application.DisplayAlerts = False

20120 .Worksheets("Sheet1").Delete
20121 .Worksheets("Sheet2").Delete
20129 .Worksheets("Sheet3").Delete

20130 .Application.DisplayAlerts = True
20131 .Application.EnableEvents = True
20132 End With
20133 On Error GoTo
Excel_QuickAndDirtyRenditionOfReportRecordSource_err

20134 FollowHyperlink xlsPath
20139 End If

20990 gSendReportToExcel = False
20999 End If

Excel_QuickAndDirtyRenditionOfReportRecordSource_xit:
DebugStackPop
On Error Resume Next
Set mySS = Nothing
myRS.Close
Set myRS = Nothing
Exit Function

Excel_QuickAndDirtyRenditionOfReportRecordSource_err:
BugAlert True, "Report='" & theReport.Name & "', SQL='" & CurrentDb
().QueryDefs(theReport.RecordSource).SQL & "', xlsPath='" & xlsPath &
"'."
Resume Excel_QuickAndDirtyRenditionOfReportRecordSource_xit
End Function
=================================================
 
D

Dirk Goldgar

PeteCresswell said:
I've got a little routine that accepts a pointer to an opening report
and does a TransferSpreadsheet using the report's .RecordSource as the
input.

If a global variable is set, Report_Open calls this routine and then
cancels the report.

Works for 29 out of thirty reports - but abends with ""The table name
you entered doesn't follow {application name} object-naming rules." on
one report.

If I intercept the .RecordSource SQL and paste it into a new query,
the query runs ok and there do not seem tb any non-standard table
names.

But it's quite a bit of SQL. TransferText throws the same error, and
an Immediate window cannot handle it without inserting paragraph
breaks.

I'm wondering if it's something about the size of the SQL.

Anybody been here?

For the masochistly-inclined, here's the SQL - followed by the
routine.

SQL:
=================================================
SELECT qryTradingAccounts_Active.TradingAccountID,
qryTradingAccounts_Active.CashBeginDayAmount,
qryTradingAccounts_Active.CashBeginDayDate,
qryTradingAccounts_Active.ClientName_Moxy, Val([SumOfParAmount_Net] &
"") AS AccountPar, tblStrategy.StrategyShortName,
tblStrategy.StrategyLongName,
qryTradingAccounts_Active.TradingAccountName,
tblTradingAccount_1.TradingAccountName AS ParentName,
qryTradingAccounts_Active.SpecialInstructions,
qryTradingAccounts_Active.TradingAccountNumber, IIf((Val
([SumOfParAmount_Net] & "")+Val([tblTradingAccount].
[CashBeginDayAmount] & ""))>0,Val([tblTradingAccount].
[CashBeginDayAmount] & "")/(Val([SumOfParAmount_Net] & "")+Val
([tblTradingAccount].[CashBeginDayAmount] & "")),0) AS PercentAccount
FROM tblStrategy INNER JOIN (((qryTradingAccounts_Active LEFT JOIN
qryHoldings_ByTradingAccount_Immediate ON
qryTradingAccounts_Active.TradingAccountID =
qryHoldings_ByTradingAccount_Immediate.TradingAccountID) INNER JOIN
tblTradingAccountStrategy ON
qryTradingAccounts_Active.TradingAccountID =
tblTradingAccountStrategy.TradingAccountID) LEFT JOIN
tblTradingAccount AS tblTradingAccount_1 ON
qryTradingAccounts_Active.TradingAccountID_LadderParent =
tblTradingAccount_1.TradingAccountID) ON tblStrategy.StrategyID =
tblTradingAccountStrategy.StrategyID
WHERE (tblTradingAccount.CashBeginDayAmount<>0) AND
(tblTradingAccount.CashBeginDayAmount Is Not Null) AND
(tblStrategy.StrategyID=2)
=================================================

Routine:
=================================================
Public Function Excel_QuickAndDirtyRenditionOfReportRecordSource(ByRef
theReport As Report)
20000 DebugStackPush mModuleName & ":
Excel_QuickAndDirtyRenditionOfReportRecordSource"
20001 On Error GoTo
Excel_QuickAndDirtyRenditionOfReportRecordSource_err

' PURPOSE: To provide a means for a Report_Open() event to send
the report's
' output to Excel instead of rendering it on the MS
Access report.
' ACCEPTS: - Pointer to the calling report
'
' NOTES: 1) We would *like* to check the RS to see if there are
any records and
' issue a notification instead of just hitting the
user with an empty
' spreadsheet. However, some queries will contain
references to
' fields on forms (e.g. Forms!frmHome!txtAsOfDate) and
these references
' will not resolve at the VBA level - although for
some reason
' DoCmd.TransferSpreadsheet does not have any problem
with them.

20002 Dim myRS As DAO.Recordset
Dim mySS As Excel.Application

Dim tempPath As String
Dim xlsPath As Variant
Dim okToProceed As Boolean

' ------------------------------------------------
' Get path to user's Temp() dir on Citrix server

20010 tempPath = windowsTempPath_Get()

20020 If IsNull(tempPath) Then
20021 BugAlert True, "Unable to retrieve path to Windows' 'Temp'
directory."
20029 Else
' ------------------------------------------------
' - Concoct destination path for spreadsheet
' - Use report's .RecordSource to create spreadsheet

20030 With theReport
'20031 Set myRS = CurrentDb.OpenRecordset(.RecordSource,
dbOpenSnapshot, dbForwardOnly)
'20032 If ((myRS.BOF = True) And (myRS.EOF = True)) Then
'20033 MsgBox "There are no records that qualify for this
report.", vbExclamation, "Cannot Create Excel Spreadsheet"
'20034 Else
20035 xlsPath = tempPath & .Caption & "." & CurrentUserGet() &
"." & Format$(Now(), "yyyy mm-dd hh-nn-ss") & ".xls"
'20036 DoCmd.TransferText acExportDelim, , .RecordSource,
xlsPath
20036 DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel7, .RecordSource, xlsPath
20037 okToProceed = True
'20038 End If
20039 End With
20099 End If

' ------------------------------------------------
' - Remove any default sheets that the user's Excel
' defaults may have created
' - Re-enable event handling
' - Open up the spreadsheet we just created
' - Clear the flag that causes reports tb rendered
' in Excel (so subsequent "Print" or "Preview"
' clicks on the same report will not force Excel)

20110 If okToProceed = True Then
20111 If SpreadSheetOpen_Existing(xlsPath, mySS) = True Then
20112 On Error Resume Next
20113 With mySS
20119 .Application.DisplayAlerts = False

20120 .Worksheets("Sheet1").Delete
20121 .Worksheets("Sheet2").Delete
20129 .Worksheets("Sheet3").Delete

20130 .Application.DisplayAlerts = True
20131 .Application.EnableEvents = True
20132 End With
20133 On Error GoTo
Excel_QuickAndDirtyRenditionOfReportRecordSource_err

20134 FollowHyperlink xlsPath
20139 End If

20990 gSendReportToExcel = False
20999 End If

Excel_QuickAndDirtyRenditionOfReportRecordSource_xit:
DebugStackPop
On Error Resume Next
Set mySS = Nothing
myRS.Close
Set myRS = Nothing
Exit Function

Excel_QuickAndDirtyRenditionOfReportRecordSource_err:
BugAlert True, "Report='" & theReport.Name & "', SQL='" & CurrentDb
().QueryDefs(theReport.RecordSource).SQL & "', xlsPath='" & xlsPath &
"'."
Resume Excel_QuickAndDirtyRenditionOfReportRecordSource_xit
End Function
=================================================
Click to expand...


I'm puzzled, Pete. I don't believe that TransferSpreadsheet is supposed to
allow you to export a SQL statement. So far as I know, it requires the name
of a table or a stored query. Are you saying that, with other reports, you
pass an SQL statement in the TableName argument of TransferSpreadsheet, and
it works? Or are those reports using a table or stored query as their
recordsources?
 
P

PeteCresswell

I'm puzzled, Pete.  I don't believe that TransferSpreadsheet is supposed to
allow you to export a SQL statement.  So far as I know, it requires thename
of a table or a stored query.  Are you saying that, with other reports,you
pass an SQL statement in the TableName argument of TransferSpreadsheet, and
it works?  Or are those reports using a table or stored query as their
recordsources?
Click to expand...

I think you have nailed it.

All the other reports do, indeed, use either a named query or table as
their .RecordSource.

This report's Report_Open references a model query, extracts it's SQL,
and concocts a custom WHERE clause based on user specs gathered
earlier in the process.

Then it sets it's own .RecordSource to that SQL - which works for the
report.....

I guess my workaround is to identify the situation and then figure out
how to make a temporary query object in such a way that the
application doesn't bloat with repeated uses of the routine.
 
D

Dirk Goldgar

PeteCresswell said:
All the other reports do, indeed, use either a named query or table as
their .RecordSource.

This report's Report_Open references a model query, extracts it's SQL, and
concocts a custom WHERE clause based on user specs gathered earlier in the
process.


Then it sets it's own .RecordSource to that SQL - which works for the
report.....

I guess my workaround is to identify the situation and then figure out how
to make a temporary query object in such a way that the application
doesn't bloat with repeated uses of the routine.
Click to expand...

I would probably have an all-purpose querydef named, maybe, "qryExport", and
use that for such exports. The process would be to set the SQL for that
querydef and then do the export:

Dim strExport As String

With theReport

If .RecordSource Like "SELECT *" Then
CurrentDb.QueryDefs("qryExport").SQL = .RecordSource
strExport = "qryExport"
Else
strExport = .RecordSource
End If

DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel7, _
strExport, _
xlsPath

End With

I don't know how much that will contribute to bloating.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Field Size Dilemma 12
CDO: Anybody Using It To Send eMail? 30
How to use a combo box variable in a VBA module 1
A general question about report recordset binding and dataavailability 7
TransferSpreadsheet Fails Within Transaction? 1
CompactDataBase runtime 3356 0

Top