XL97: General ODBC Error Running Macro from Excel for Macintosh

ID: Q184590

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

When you run a macro in Microsoft Excel 97 for Windows, you may receive the following error:

   Run-time error '1004'
   General ODBC Error

and you can click either End or Debug. If you click Debug, the Microsoft Visual Basic Editor opens and the line containing the Refresh method is selected.

CAUSE

This error message may occur if you have a macro that you created in Microsoft Excel 98 Macintosh Edition, and you run it in Microsoft Excel 97 for Windows.

Because the names of the ODBC drivers that ship with these two programs are different, the macro may work on one platform, but not the other.

This behavior is by design of Microsoft Excel.

WORKAROUND

Microsoft provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

The following Excel 98 Macintosh Edition macro resulted from recording a macro while retrieving data from the Customer.dbf table:

 Sub Macro1()

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

       .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")

        .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 97, 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, 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.

Additional query words: XL98 query XL97

Keywords          : kbdta kbdtacode OffVBA xlquery KbVBA xlvbmigrate 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: May 18, 1999