Error Trapping when getting external Data

[wsummers]

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

[sebastienm]

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

[wsummers]

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