XL98: General ODBC Error Running Macro from Excel for Windows
ID: Q184591
|
The information in this article applies to:
-
Microsoft Excel 98 Macintosh Edition
SYMPTOMS
When you run a macro in Microsoft Excel 98 Macintosh Edition, 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 may occur if you have a macro that you created in Microsoft
Excel 97 for Windows and you run it in Microsoft Excel 98 Macintosh
Edition.
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.
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/overview/overview.asp
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.
STATUS
This is by design of Microsoft Excel.
Additional query words:
XL98 query XL97
Keywords : kbdta kbdtacode xlvbahowto OffVBA xlquery xlvbmigrate
Version : MACINTOSH:
Platform : MACINTOSH
Issue type : kbprb