Error in Recorded QueryGetData Function with ODBC 2.0 Drivers

Last reviewed: September 3, 1997
Article ID: Q131383
The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, when you record a Visual Basic macro where you use the Get External Data command (Data menu), and you use either the Microsoft Excel Driver or the Microsoft Access driver from the Microsoft ODBC Driver Pack 2.0 to return data to your worksheet, when you run the recorded macro, you may receive either of the following error messages:

   Syntax Error

   -or-

   Variable not defined

CAUSE

This behavior occurs because the ConnectionStr argument for these ODBC Driver Pack 2.0 drivers contains more information than the version 1.0 drivers, causing the macro recorder to record a text string longer than 255 characters. When the 255 character limit is exceeded, the remaining information is not recorded in the macro. The amount of data that is recorded depends on the location of your database file (path) and the location of the default directory for the data source.

The following is an example of an incomplete QueryGetData function statement that may be recorded when you use the Microsoft Excel driver (version 2.0) to connect to the database DATA.XLS:

   Application.Run "QueryGetData", _
     ' Note that the following four lines appear as one recorded line
     "DSN=Microsoft Excel 5.0 Worksheet;DBQ=C:\EXCEL5C\DATA.XLS;
      DefaultDir=C:\EXCEL5C;Deleted=1;DriverId=22;
      FIL=excel 5.0;JetIniPath=odbcddp.ini;MaxScanRows=16;
      ReadOnly=1;Statistics=0;", _
      "",True,True,False, Range("S

The following is an example of a complete QueryGetData function statement that is recorded when you use the Microsoft Excel driver (version 1.0):

   Application.Run "QueryGetData", _
         "DSN=Excel files;DBQ=c:\excel5c;FIL=Excel;", _
         "", True, True, False, Range("Sheet1!$A$1"), True

Note that this problem does not occur with the Microsoft Text driver included in the ODBC Driver Pack 2.0.

RESOLUTION

To work around this problem, you can edit the recorded macro so that the syntax is correct, and all of the information appears as it should. The following are examples of correct and complete QueryGetData functions using the Microsoft Excel and Microsoft Access drivers from the ODBC Driver Pack 2.0:

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

Note that the following examples are meant to show the required arguments for the QueryGetData function, and will need to be modified to work correctly to return data from your database.

Microsoft Excel Driver

   Application.Run "QueryGetData", _
     ' Note that the following four lines appear as one recorded line
     "DSN=Microsoft Excel 5.0 Worksheet;DBQ=C:\EXCEL5C\DATA.XLS;
     DefaultDir=C:\EXCEL5C;Deleted=1;DriverId=22;
     FIL=excel 5.0;JetIniPath=odbcddp.ini;MaxScanRows=16;
     ReadOnly=1;Statistics=0;", _
     "",True,True,False, Range("Sheet1!$A$1"), True, False

Microsoft Access Driver

   Application.Run "QueryGetData", _
     ' Note that the following four lines appear as one recorded line
     "DSN=Access 2.0 - ODBC 2.0;DBQ=D:\ACCESS\DB1.MDB;
     DefaultDir=D:\ACCESS;Deleted=1;DriverId=25;
     FIL=MS Access;JetIniPath=odbcddp.ini;MaxBufferSize=512;
     PageTimeout=600;Statistics=0;UID=admin;", _
     "", True, True, False, Range("Sheet1!$A$1"), True, False

REFERENCES

For more information about the QueryGetData Function, choose the Search button in the Visual Basic Reference and type:

   QueryGetData Function


Additional query words:
Keywords : kbcode kbprg PgmOthr
Version : 5.0 5.0c 7.0
Platform : WINDOWS
Issue type : kbprb
Solution Type : kbworkaround


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.

Last reviewed: September 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.