I am using the following statement to get external OBDC data for refreshing
an Excel 2003 pivot table:

Sheets("Source PivotTable").PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM Data Data" & Chr(13) &
"" & Chr(10) & "WHERE" _
, _
mySQLWhere1 _
, _
mySQLWhere2 & Chr(13) & "" & Chr(10) & "ORDER BY " _
, _
"Data.Material, Data.`Serial No`, Data.`Receive Date` DESC"), _
Connection:=Array(Array("ODBC;DSN=MS Access Database;DBQ=" & myPath
& myFileName & ";"), _
Array("DefaultDir=" & myPath & ";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;"), _
Array("PageTimeout=5;"))

I need code to trap the VBA Error 1004 message I get when no data is found.
Does anyone have any suggestions?

Thanks
Will

Hi,
Use the On Error statement to trap the error. Something like:

Just before the code, do a
On Error Resume Next
'....
and after the code, trap the error and process depending on the number /
description
'...
If Err.Number=1004 and Err.Description like "*ODBC*" then
msgbox "ODBC error"
exit sub ' stop processing of sub
elseif err<0 then 'other error
msgbox "an unknown error has occured: " & err.number
exit sub 'stop processing of sub
else 'no error
'nothing particular
end if

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"wsummers" wrote:

I am using the following statement to get external OBDC data for refreshing
an Excel 2003 pivot table:

Sheets("Source PivotTable").PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM Data Data" & Chr(13) &
"" & Chr(10) & "WHERE" _
, _
mySQLWhere1 _
, _
mySQLWhere2 & Chr(13) & "" & Chr(10) & "ORDER BY " _
, _
"Data.Material, Data.`Serial No`, Data.`Receive Date` DESC"), _
Connection:=Array(Array("ODBC;DSN=MS Access Database;DBQ=" & myPath
& myFileName & ";"), _
Array("DefaultDir=" & myPath & ";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;"), _
Array("PageTimeout=5;"))

I need code to trap the VBA Error 1004 message I get when no data is found.
Does anyone have any suggestions?

Thanks
Will

Hi Sebastian

Thanks for your suggestion.

I originally tried something very similar but for this specific error the
err.number is not passed back to the function. It comes across as 0 with no
description. May have something to do with it being an SQL/OBDC error which
I'm not sure how to identify. Any other ideas?

Regards
Will

"sebastienm" wrote:

Hi,
Use the On Error statement to trap the error. Something like:

Just before the code, do a
On Error Resume Next
'....
and after the code, trap the error and process depending on the number /
description
'...
If Err.Number=1004 and Err.Description like "*ODBC*" then
msgbox "ODBC error"
exit sub ' stop processing of sub
elseif err<0 then 'other error
msgbox "an unknown error has occured: " & err.number
exit sub 'stop processing of sub
else 'no error
'nothing particular
end if

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"wsummers" wrote:

I am using the following statement to get external OBDC data for refreshing
an Excel 2003 pivot table:

Sheets("Source PivotTable").PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM Data Data" & Chr(13) &
"" & Chr(10) & "WHERE" _
, _
mySQLWhere1 _
, _
mySQLWhere2 & Chr(13) & "" & Chr(10) & "ORDER BY " _
, _
"Data.Material, Data.`Serial No`, Data.`Receive Date` DESC"), _
Connection:=Array(Array("ODBC;DSN=MS Access Database;DBQ=" & myPath
& myFileName & ";"), _
Array("DefaultDir=" & myPath & ";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;"), _
Array("PageTimeout=5;"))

I need code to trap the VBA Error 1004 message I get when no data is found.
Does anyone have any suggestions?

Thanks
Will