Hi all,

Thanks for any help in advance.

I have a procedure that runs a daily update for an application. This
procedure has a number of queries that from time to time cause an error. I
am familiar with error handling and am using the err.description to get the
reason for the error, but I need to find a way to capture which query caused
the error.

I thought about doing this:

On Error GoTo HandleError

'Delete all records from tblProjectInfoTemp.
str_error = "qryPITempDelete"
DoCmd.OpenQuery "qryPITempDelete"

'Append records from Project Office to tblProjectInfoTemp.
str_error = "qryPITempAppend"
DoCmd.OpenQuery "qryPITempAppend"

HandleError:

str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ,
error_message , error_action ) " & _
"SELECT " & rst!process_id & ", 3, '" &
Err.Description & "','" & str_error & "' "

DoCmd.RunSQL str_sql
DoCmd.SetWarnings True


I know the syntax for the insert on this type of SQL statement should
include the Values syntax instead of Select, but will change that later.

Please let me know if there is an easier way to accomplish this. There is
one process that runs up to 180 queries and to do this for each one would be
painstakingly slow.

Hi,
I think you already capturing query name in error_action. so this field can
help you to find which one cause the error

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


"PiB311" <(E-Mail Removed)> wrote in message
news:87C58D7F-2D28-4911-B26A-(E-Mail Removed)...
> Hi all,
>
> Thanks for any help in advance.
>
> I have a procedure that runs a daily update for an application. This
> procedure has a number of queries that from time to time cause an error.
> I
> am familiar with error handling and am using the err.description to get
> the
> reason for the error, but I need to find a way to capture which query
> caused
> the error.
>
> I thought about doing this:
>
> On Error GoTo HandleError
>
> 'Delete all records from tblProjectInfoTemp.
> str_error = "qryPITempDelete"
> DoCmd.OpenQuery "qryPITempDelete"
>
> 'Append records from Project Office to tblProjectInfoTemp.
> str_error = "qryPITempAppend"
> DoCmd.OpenQuery "qryPITempAppend"
>
> HandleError:
>
> str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ,
> error_message , error_action ) " & _
> "SELECT " & rst!process_id & ", 3, '" &
> Err.Description & "','" & str_error & "' "
>
> DoCmd.RunSQL str_sql
> DoCmd.SetWarnings True
>
>
> I know the syntax for the insert on this type of SQL statement should
> include the Values syntax instead of Select, but will change that later.
>
> Please let me know if there is an easier way to accomplish this. There is
> one process that runs up to 180 queries and to do this for each one would
> be
> painstakingly slow.
>

Unfortunately I cannot think of any alternative

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

PiB311 wrote:
> Hi all,
>
> Thanks for any help in advance.
>
> I have a procedure that runs a daily update for an application. This
> procedure has a number of queries that from time to time cause an error. I
> am familiar with error handling and am using the err.description to get the
> reason for the error, but I need to find a way to capture which query caused
> the error.
>
> I thought about doing this:
>
> On Error GoTo HandleError
>
> 'Delete all records from tblProjectInfoTemp.
> str_error = "qryPITempDelete"
> DoCmd.OpenQuery "qryPITempDelete"
>
> 'Append records from Project Office to tblProjectInfoTemp.
> str_error = "qryPITempAppend"
> DoCmd.OpenQuery "qryPITempAppend"
>
> HandleError:
>
> str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ,
> error_message , error_action ) " & _
> "SELECT " & rst!process_id & ", 3, '" &
> Err.Description & "','" & str_error & "' "
>
> DoCmd.RunSQL str_sql
> DoCmd.SetWarnings True
>
>
> I know the syntax for the insert on this type of SQL statement should
> include the Values syntax instead of Select, but will change that later.
>
> Please let me know if there is an easier way to accomplish this. There is
> one process that runs up to 180 queries and to do this for each one would be
> painstakingly slow.
>

Thanks for the help! Anyone else having difficulty posting replies i this
forum? I can post questions but get errors on the response.

"John Spencer MVP" wrote:

> Unfortunately I cannot think of any alternative
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
> PiB311 wrote:
> > Hi all,
> >
> > Thanks for any help in advance.
> >
> > I have a procedure that runs a daily update for an application. This
> > procedure has a number of queries that from time to time cause an error. I
> > am familiar with error handling and am using the err.description to get the
> > reason for the error, but I need to find a way to capture which query caused
> > the error.
> >
> > I thought about doing this:
> >
> > On Error GoTo HandleError
> >
> > 'Delete all records from tblProjectInfoTemp.
> > str_error = "qryPITempDelete"
> > DoCmd.OpenQuery "qryPITempDelete"
> >
> > 'Append records from Project Office to tblProjectInfoTemp.
> > str_error = "qryPITempAppend"
> > DoCmd.OpenQuery "qryPITempAppend"
> >
> > HandleError:
> >
> > str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ,
> > error_message , error_action ) " & _
> > "SELECT " & rst!process_id & ", 3, '" &
> > Err.Description & "','" & str_error & "' "
> >
> > DoCmd.RunSQL str_sql
> > DoCmd.SetWarnings True
> >
> >
> > I know the syntax for the insert on this type of SQL statement should
> > include the Values syntax instead of Select, but will change that later.
> >
> > Please let me know if there is an easier way to accomplish this. There is
> > one process that runs up to 180 queries and to do this for each one would be
> > painstakingly slow.
> >
>