You are currently offline, waiting for your internet to reconnect

XL98: General ODBC Error Running Macro from Excel for Windows

This article was previously published under Q184591
Retired KB Content Disclaimer
This 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.
SYMPTOMS
When you run a macro in Microsoft Excel 98 Macintosh Edition, you mayreceive the following error:
Run-time error '1004'
General ODBC Error
and you can click either End or Debug. If you click Debug, the MicrosoftVisual Basic Editor opens, and the line containing the Refresh method isselected.
CAUSE
This error may occur if you have a macro that you created in MicrosoftExcel 97 for Windows and you run it in Microsoft Excel 98 MacintoshEdition.

Because the names of the ODBC drivers that ship with these two programs aredifferent, the macro may work on one platform, but not the other.
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 whileretrieving 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 WithEnd Sub				
If you run Macro1 in Excel 98 Macintosh Edition, you will receive the errormessage described in the "Symptoms" section. To adapt this macro so it willrun in both Excel 97 for Windows and Excel 98 Macintosh Edition, change itto 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-Elsestructure that tests for the operating system under which Excel is running.Based on the operating system, the macro will use one of two connectionstrings.
STATUS
This is by design of Microsoft Excel.
XL98 query XL97
Properties

Article ID: 184591 - Last Review: 10/10/2006 16:49:30 - Revision: 3.2

  • Microsoft Excel 98 for Macintosh
  • kbdtacode kberrmsg kbfaq kbprb KB184591
Feedback