Microsoft KB Archive/131383

= Error in Recorded QueryGetData Function with ODBC 2.0 Drivers =

Article ID: 131383

Article Last Modified on 9/13/2006

-

APPLIES TO


 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 95 Standard Edition

-



This article was previously published under Q131383





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

