Need help desperately, Problem Generating Errors for currentdb.execute

**Steve Jorgensen** · Nov 12 '05, 11:10 PM

Re: Need help desperately, Problem Generating Errors for currentdb.execu te

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 <zippy@zip.co m> wrote:
[color=blue]
>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_er r:
> MsgBox Err.Number
> MsgBox Err.Description
> Debug.Print Err.Description
> Debug.Print Err.Description
> Debug.Print Err.Source
> Resume sql_error_exit
>[/color]

**Allen Browne** · Nov 12 '05, 11:10 PM

Re: Need help desperately, Problem Generating Errors for currentdb.execu te

How many groups did you post this to?

**frank** · Nov 12 '05, 11:10 PM

Re: Need help desperately, Problem Generating Errors for currentdb.execu te

Two groups.

Allen Browne wrote:
[color=blue]
> How many groups did you post this to?
>
>[/color]

**frank** · Nov 12 '05, 11:10 PM

Re: Need help desperately, Problem Generating Errors for currentdb.execu te

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

Steve Jorgensen wrote:
[color=blue]
> 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 <zippy@zip.co m> wrote:
>
>[color=green]
>>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.Execut e sql, dbFailOnError
>>
>>
>>
>>sql_error_exi t:
>> 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
>>[/color]
>
>[/color]

**Steve Jorgensen** · Nov 12 '05, 11:10 PM

Re: Need help desperately, Problem Generating Errors for currentdb.execu te

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 <zippy@zip.co m> wrote:
[color=blue]
>I am pretty new to vba, I am not quite sure how to do what you are
>explaining.
>
>Steve Jorgensen wrote:
>[color=green]
>> 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 <zippy@zip.co m> wrote:
>>
>>[color=darkred]
>>>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.Execu te sql, dbFailOnError
>>>
>>>
>>>
>>>sql_error_ex it:
>>> Exit Sub
>>>
>>>sql_error_er r:
>>> MsgBox Err.Number
>>> MsgBox Err.Description
>>> Debug.Print Err.Description
>>> Debug.Print Err.Description
>>> Debug.Print Err.Source
>>> Resume sql_error_exit
>>>[/color]
>>
>>[/color][/color]

**frank** · Nov 12 '05, 11:10 PM

Re: Need help desperately, Problem Generating Errors for currentdb.execu te

No, it is a plain ol MDB file :)

Steve Jorgensen wrote:
[color=blue]
> 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 <zippy@zip.co m> wrote:
>
>[color=green]
>>I am pretty new to vba, I am not quite sure how to do what you are
>>explaining.
>>
>>Steve Jorgensen wrote:
>>
>>[color=darkred]
>>>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 <zippy@zip.co m> 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.Execut e sql, dbFailOnError
>>>>
>>>>
>>>>
>>>>sql_error_e xit:
>>>> Exit Sub
>>>>
>>>>sql_error_e rr:
>>>> MsgBox Err.Number
>>>> MsgBox Err.Description
>>>> Debug.Print Err.Description
>>>> Debug.Print Err.Description
>>>> Debug.Print Err.Source
>>>> Resume sql_error_exit
>>>>
>>>
>>>[/color][/color]
>[/color]

**Steve Jorgensen** · Nov 12 '05, 11:11 PM

Re: Need help desperately, Problem Generating Errors for currentdb.execu te

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

On Wed, 28 Apr 2004 11:41:52 -0400, frank <zippy@zip.co m> wrote:
[color=blue]
>No, it is a plain ol MDB file :)
>
>Steve Jorgensen wrote:
>[color=green]
>> 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 <zippy@zip.co m> wrote:
>>
>>[color=darkred]
>>>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 <zippy@zip.co m> 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
>>>>>conditio n.
>>>>>
>>>>>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.Execu te 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
>>>>>
>>>>
>>>>[/color]
>>[/color][/color]

**David W. Fenton** · Nov 12 '05, 11:13 PM

Re: Need help desperately, Problem Generating Errors for currentdb.execu te

frank <zippy@zip.co m> wrote in
news:WEPjc.1551 8$7a5.5261@bign ews6.bellsouth. net:
[color=blue]
> 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[/color]

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

Need help desperately, Problem Generating Errors for currentdb.execute

Need help desperately, Problem Generating Errors for currentdb.execute

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment