XL98: General ODBC Error Running Macro from Excel for Windows

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following Excel 97 macro resulted from recording a macro while retrieving data from the Customer.dbf table:

 Sub Macro1()

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
      "ODBC;CollatingSequence=ASCII;" & _
      "DBQ=D:\Program Files\Microsoft Office\Office;" & _
      "DefaultDir=D:\Program Files\Microsoft Office\Office;Delete" _
      ), Array("d=1;Driver={Microsoft dBase Driver (*.dbf)};" & _
      "DriverId=533;FIL=dBase III;ImplicitCommitSync=Yes;" & _
      "MaxBufferSize=512;MaxScanRows=8;P"), Array( _
      "ageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;" & _
      "UserCommitSync=Yes;")), Destination:=Range("A1"))

      .Sql = Array( _
      "SELECT Customer.CUSTMR_ID, Customer.COMPANY, Customer.CITY," & _
      "Customer.REGION" & Chr(13) & "" & Chr(10) & _
      "FROM `D:\Program Files\Microsoft Office\Office`\Customer.dbf " & _
      "Customer")

        .FieldNames = True
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .HasAutoFormat = True
        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SavePassword = True
        .SaveData = True
    End With
End Sub
				

If you run Macro1 in Excel 98 Macintosh Edition, you will receive the error message described in the "Symptoms" section. To adapt this macro so it will run in both Excel 97 for Windows and Excel 98 Macintosh Edition, change it to the following:

 Sub cross_plat()

 Dim mytable As QueryTable
 Dim opsys As String

 opsys = Application.OperatingSystem

 If InStr(opsys, "Windows") > 0 Then

   Set mytable = ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
      "ODBC;CollatingSequence=ASCII;" & _
      "DBQ=D:\Program Files\Microsoft Office\Office;" & _
      "DefaultDir=D:\Program Files\Microsoft Office\Office;Delete" _
      ), Array("d=1;Driver={Microsoft dBase Driver (*.dbf)};" & _
      "DriverId=533;FIL=dBase III;ImplicitCommitSync=Yes;" & _
      "MaxBufferSize=512;MaxScanRows=8;P"), Array( _
      "ageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;" & _
      "UserCommitSync=Yes;")), Destination:=Range("A1"))

      mytable.Sql = Array( _
      "SELECT Customer.CUSTMR_ID, Customer.COMPANY, Customer.CITY," & _
      "Customer.REGION" & Chr(13) & "" & Chr(10) & _
      "FROM `D:\Program Files\Microsoft Office\Office`\Customer.dbf " & _
      "Customer")
 Else

    Set mytable = ActiveSheet.QueryTables.Add(Connection:= _
       "ODBC;DRIVER={Microsoft 3.01 dBASE PPC};DATABASE=" & _
       "HD:Microsoft Office 98:Sample Files:Sample Databases" _
       , Destination:=Range("A1"))

       mytable.Sql = Array( _
       "SELECT CUSTOMER.CUSTMR_ID, CUSTOMER.COMPANY, CUSTOMER.CONTACT," & _
       "CUSTOMER.CON_TITLE, CUSTOMER.ADDRESS, CUSTOMER.CITY," & _
       "CUSTOMER.REGION, CUSTOMER.ZIP_CODE, CUSTOMER.COUNTRY," & _
       "CUSTOMER.PHONE, CUSTOMER.FAX" & vbLf & "FROM CUSTOMER CUSTOMER")

 End If

   With mytable
      .FieldNames = True
      .RefreshStyle = xlInsertDeleteCells
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .RefreshOnFileOpen = False
      .HasAutoFormat = True
      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SavePassword = True
      .SaveData = True
   End With
 End Sub
				

NOTE: The change in the Cross_Plat macro is the addition of an If-Then-Else structure that tests for the operating system under which Excel is running. Based on the operating system, the macro will use one of two connection strings.