Error in Recorded QueryGetData Function with ODBC 2.0 Drivers

• 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

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

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