Error in Recorded QueryGetData Function with ODBC 2.0 Drivers
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
Version : WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform : WINDOWS winnt
Issue type : kbprb