PRB: Invalid Table Name Error with TransferSpreadsheet Action

Article ID: Q113299
Creation Date: 31-MAR-1994
Revision Date: 19-SEP-1996

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, and 2.0

SYMPTOMS

When you pass a long (more than 64 characters) SQL string as an argument to the TransferSpreadsheet action, you receive the following error message:

   Invalid Table Name

CAUSE

SQL strings passed directly to the TransferSpreadsheet action cannot be longer than 64 characters. Characters beyond 64 will be truncated. The TransferSpreadsheet action was not designed to handle SQL strings, although they will work if 64 characters or less.

RESOLUTION

Create a query using your SQL string and export that query using the TransferSpreadsheet action.

MORE INFORMATION

NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore when re-creating this code in Access Basic.

The following example demonstrates a SQL string that can be passed to the TransferSpreadsheet action:

   Function Xfer ()
      DoCmd TransferSpreadsheet a_export, 0, "Select * from_
       transactions where [tradedate] <= #01-01-93# ;",_
       "C:\EXCEL\TEST.XLS"
   End Function

The next example demonstrates a SQL string that cannot be successfully passed to the TransferSpreadsheet action:

   Function Xfer ()
      DoCmd TransferSpreadsheet a_export, 0, "Select * from_
       transactions where [tradedate] between #01-01-93# and_
       #7-24-93#;","C:\EXCEL\EARL.XLS"
   End Function

If you copy the SQL statement in the example above and paste it into a query, the query will run correctly.

REFERENCES

Microsoft Access "Language Reference," version 1.1, "TransferDatabase Action," pages 473-476

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.