Excel: Building a SQL Query Longer than 255 Characters

ID: Q88974

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 3.0, 4.0

SUMMARY

Microsoft Excel for the Macintosh limits the number of characters that can be typed into a cell or assigned to a string to 255 characters. One result of this is that SQL statements which are greater than 255 characters in length must be created and saved to a file before accessing them through a macro SQL.QUERY() statement.

MORE INFORMATION

The Database Access Macro allows accessing remote databases from within Microsoft Excel, including the submission of SQL (Structured Query Language) queries. In order to send a SQL statement longer than 255 characters within a macro statement, the query must be saved to a file and accessed by a special form of the SQL.QUERY() macro function.

To save a SQL query as a text file:

From the Data menu, choose SQL Query...

Type out the SQL statement in the Query Editor dialog box.

Click the Save button and save the query out to a filename.

The syntax for the macro function SQL.QUERY for accessing a SQL query which has been saved to a file is:


   =SQL.QUERY(3,"Hard Disk:QueryTextFile",1)

The first argument specifies the query type; 3 indicates that a text file will be the source for the SQL statement. The second argument contains the folder location and filename (QueryTextFile) of the SQL query. The '1' in the function above specifies that the information returned after processing the query should be returned to the current selection. This argument can be changed to whatever is appropriate. A fourth optional argument can also be included specifying a file name to store the returned information.

Reference(s):

"Microsoft Excel Database Access User's Guide," versions 3.0 and 4.0, pages 20 and 34

Additional query words: 3.0 4.00 dam dal

Keywords :
Version :
Platform :
Issue type :

Send feedback to MSDN.

Look here for MSDN Online resources.