Need help desperately, Problem Generating Errors for currentdb.execute

[frank]

Below is some code for doing and insert into one of my tables. The
inserts do not work because a duplicate key exists, which I want to
happen. The problem is, I cannot get access to return an error for this
condition.

What can I do to get access to return an error on my query?
Thanks

-------------------------------
CODE
-------------------------------

On Error GoTo sql_error_err

Set Mydb = CurrentDb
Mydb.Execute sql, dbFailOnError

sql_error_exit:
Exit Sub

sql_error_err:
MsgBox Err.Number
MsgBox Err.Description
Debug.Print Err.Description
Debug.Print Err.Description
Debug.Print Err.Source
Resume sql_error_exit

[Steve Jorgensen]

If your back-end is Jet, that should work fine, though it will display the
same error message twice in the Immediate window. If you are using an ODBC
back-end, then you might need to loop through the Errors collection.

The only issue with the Errors collection is that it holds the last JET error
info even if a later non-JET error is what you're currently handling. The way
to check that is if the Errors collection has >=1 items, and the last item's
..Number is the same as Err.Number, then the Errors collection refers to the
current VB error.

On Wed, 28 Apr 2004 10:59:34 -0400, frank <zi***@zip.com> wrote:

Below is some code for doing and insert into one of my tables. The
inserts do not work because a duplicate key exists, which I want to
happen. The problem is, I cannot get access to return an error for this
condition.

What can I do to get access to return an error on my query?
Thanks

-------------------------------
CODE
-------------------------------

On Error GoTo sql_error_err

Set Mydb = CurrentDb
Mydb.Execute sql, dbFailOnError

sql_error_exit:
Exit Sub

sql_error_err:
MsgBox Err.Number
MsgBox Err.Description
Debug.Print Err.Description
Debug.Print Err.Description
Debug.Print Err.Source
Resume sql_error_exit

[Allen Browne]

How many groups did you post this to?

[frank]

Two groups.

Allen Browne wrote:

How many groups did you post this to?

[frank]

I am pretty new to vba, I am not quite sure how to do what you are
explaining.

Steve Jorgensen wrote:

If your back-end is Jet, that should work fine, though it will display the
same error message twice in the Immediate window. If you are using an ODBC
back-end, then you might need to loop through the Errors collection.

The only issue with the Errors collection is that it holds the last JET error
info even if a later non-JET error is what you're currently handling. The way
to check that is if the Errors collection has >=1 items, and the last item's
.Number is the same as Err.Number, then the Errors collection refers to the
current VB error.

On Wed, 28 Apr 2004 10:59:34 -0400, frank <zi***@zip.com> wrote:

Below is some code for doing and insert into one of my tables. The
inserts do not work because a duplicate key exists, which I want to
happen. The problem is, I cannot get access to return an error for this
condition.

What can I do to get access to return an error on my query?
Thanks

-------------------------------
CODE
-------------------------------

On Error GoTo sql_error_err

Set Mydb = CurrentDb
Mydb.Execute sql, dbFailOnError

sql_error_exit:
Exit Sub

sql_error_err:
MsgBox Err.Number
MsgBox Err.Description
Debug.Print Err.Description
Debug.Print Err.Description
Debug.Print Err.Source
Resume sql_error_exit

[Steve Jorgensen]

A JET back-end means you're using a plain ol' MDB file, either the local MDB
or another MDB with links. ODBC means you're linked to some kind of SQL
server. Everything else you need to understand what I've written here, you
should be able to get by reading the help on the Errors collection.

On Wed, 28 Apr 2004 11:26:43 -0400, frank <zi***@zip.com> wrote:

I am pretty new to vba, I am not quite sure how to do what you are
explaining.

Steve Jorgensen wrote:

If your back-end is Jet, that should work fine, though it will display the
same error message twice in the Immediate window. If you are using an ODBC
back-end, then you might need to loop through the Errors collection.

The only issue with the Errors collection is that it holds the last JET error
info even if a later non-JET error is what you're currently handling. The way
to check that is if the Errors collection has >=1 items, and the last item's
.Number is the same as Err.Number, then the Errors collection refers to the
current VB error.

On Wed, 28 Apr 2004 10:59:34 -0400, frank <zi***@zip.com> wrote:

Below is some code for doing and insert into one of my tables. The
inserts do not work because a duplicate key exists, which I want to
happen. The problem is, I cannot get access to return an error for this
condition.

What can I do to get access to return an error on my query?
Thanks

-------------------------------
CODE
-------------------------------

On Error GoTo sql_error_err

Set Mydb = CurrentDb
Mydb.Execute sql, dbFailOnError

sql_error_exit:
Exit Sub

sql_error_err:
MsgBox Err.Number
MsgBox Err.Description
Debug.Print Err.Description
Debug.Print Err.Description
Debug.Print Err.Source
Resume sql_error_exit

[frank]

No, it is a plain ol MDB file :)

Steve Jorgensen wrote:

A JET back-end means you're using a plain ol' MDB file, either the local MDB
or another MDB with links. ODBC means you're linked to some kind of SQL
server. Everything else you need to understand what I've written here, you
should be able to get by reading the help on the Errors collection.

On Wed, 28 Apr 2004 11:26:43 -0400, frank <zi***@zip.com> wrote:

I am pretty new to vba, I am not quite sure how to do what you are
explaining.

Steve Jorgensen wrote:

If your back-end is Jet, that should work fine, though it will display the
same error message twice in the Immediate window. If you are using an ODBC
back-end, then you might need to loop through the Errors collection.

The only issue with the Errors collection is that it holds the last JET error
info even if a later non-JET error is what you're currently handling. The way
to check that is if the Errors collection has >=1 items, and the last item's
.Number is the same as Err.Number, then the Errors collection refers to the
current VB error.

On Wed, 28 Apr 2004 10:59:34 -0400, frank <zi***@zip.com> wrote:

Below is some code for doing and insert into one of my tables. The
inserts do not work because a duplicate key exists, which I want to
happen. The problem is, I cannot get access to return an error for this
condition.

What can I do to get access to return an error on my query?
Thanks

-------------------------------
CODE
-------------------------------

On Error GoTo sql_error_err

Set Mydb = CurrentDb
Mydb.Execute sql, dbFailOnError

sql_error_exit:
Exit Sub

sql_error_err:
MsgBox Err.Number
MsgBox Err.Description
Debug.Print Err.Description
Debug.Print Err.Description
Debug.Print Err.Source
Resume sql_error_exit

[Steve Jorgensen]

Then what is it that's not working about your original code?

On Wed, 28 Apr 2004 11:41:52 -0400, frank <zi***@zip.com> wrote:

No, it is a plain ol MDB file :)

Steve Jorgensen wrote:

A JET back-end means you're using a plain ol' MDB file, either the local MDB
or another MDB with links. ODBC means you're linked to some kind of SQL
server. Everything else you need to understand what I've written here, you
should be able to get by reading the help on the Errors collection.

On Wed, 28 Apr 2004 11:26:43 -0400, frank <zi***@zip.com> wrote:

I am pretty new to vba, I am not quite sure how to do what you are
explaining.

Steve Jorgensen wrote:
If your back-end is Jet, that should work fine, though it will display the
same error message twice in the Immediate window. If you are using an ODBC
back-end, then you might need to loop through the Errors collection.

The only issue with the Errors collection is that it holds the last JET error
info even if a later non-JET error is what you're currently handling. The way
to check that is if the Errors collection has >=1 items, and the last item's
.Number is the same as Err.Number, then the Errors collection refers to the
current VB error.

On Wed, 28 Apr 2004 10:59:34 -0400, frank <zi***@zip.com> wrote:

>Below is some code for doing and insert into one of my tables. The
>inserts do not work because a duplicate key exists, which I want to
>happen. The problem is, I cannot get access to return an error for this
>condition.
>
>What can I do to get access to return an error on my query?
>
>
>Thanks
>
>-------------------------------
>CODE
>-------------------------------
>
>On Error GoTo sql_error_err
>
>Set Mydb = CurrentDb
>Mydb.Execute sql, dbFailOnError
>
>
>
>sql_error_exit:
> Exit Sub
>
>sql_error_err:
> MsgBox Err.Number
> MsgBox Err.Description
> Debug.Print Err.Description
> Debug.Print Err.Description
> Debug.Print Err.Source
> Resume sql_error_exit
>

[David W. Fenton]

frank <zi***@zip.com> wrote in
news:WE******************@bignews6.bellsouth.net:

sql_error_err:
MsgBox Err.Number
MsgBox Err.Description
Debug.Print Err.Description
Debug.Print Err.Description
Debug.Print Err.Source
Resume sql_error_exit

Why not:

MsgBox Err.Number & ": " & Err.Description

That's the way I always do it.

Then you can handle the error like this:

Select Case Err.Number
Case 123 <-this would be the error you want to handle
Resume Next <-this would ignore the error
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume sql_error_exit
End Select

I can't see why you'd have a problem, except for the fact that
you're splitting up the notification of errors into multiple parts.
So far as I know, if an insert fails, it just fails, with a single
error number, not with different errors for the different kinds of
reasons it can fail. But I haven't checked.

Reading between the lines it seems you want to discard the
duplicates. I would never do it that way, as it always seems
dangerous, and it bothers me esthetically to knowingly cause an
error. Instead, I'd write my INSERT SQL with an outer join to the
destination table so that any duplicates would be eliminated from
the append operation.

That way, any other errors that might happen in the insert operation
will be for some other reason, a reason that you might want to
handle for its own sake.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc