Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 131383 - Last Review: September 13, 2006 - Revision: 2.3
Error in Recorded QueryGetData Function with ODBC 2.0 Drivers
This article was previously published under Q131383
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:
-or-
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.
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
For more information about the QueryGetData Function, choose the Search
button in the Visual Basic Reference and type:
QueryGetData Function
APPLIES TO
- Microsoft Excel 5.0 Standard Edition
- Microsoft Excel 5.0c
- Microsoft Excel 95 Standard Edition
| kbcode kbprb kbprogramming KB131383 |
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please