Error Trapping when getting external Data
 

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

Error Trapping when getting external Data
 

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

Error Trapping when getting external Data
 

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