 |
|
| Computers Forum Index » Computer - Databases - MS Access » Capturing ODBC errors in Access 2003... |
|
Page 1 of 1 |
|
| Author |
Message |
| Bobby... |
 Posted: Mon Oct 19, 2009 11:09 am |
|
|
|
Guest
|
Hi,
This must be a problem which lots of people get on a daily basis, but
so far I haven't been able to find a solution.
I am using Access 2003 as a Front end to SQL Server. My SQL server
table has three fields which do not allow nulls. If the user does not
fill in the fields in Access, he gets a nasty ODBC error. I want to
capture that error and give the user an easier to understand message.
The OnError event in Access is 0 when this error occurs, as if it
doesn't recognise that there has been an error.
Can anybody tell me how I can do this? Surely I'm not the first person
to ever encounter this??
Thanks a lot
Colin |
|
|
| Back to top |
|
|
|
| Roger... |
| Posted: Mon Oct 19, 2009 6:03 pm |
|
|
|
Guest
|
On Oct 19, 5:09 am, Bobby <bob... at (no spam) blueyonder.co.uk> wrote:
Quote: Hi,
This must be a problem which lots of people get on a daily basis, but
so far I haven't been able to find a solution.
I am using Access 2003 as a Front end to SQL Server. My SQL server
table has three fields which do not allow nulls. If the user does not
fill in the fields in Access, he gets a nasty ODBC error. I want to
capture that error and give the user an easier to understand message.
The OnError event in Access is 0 when this error occurs, as if it
doesn't recognise that there has been an error.
Can anybody tell me how I can do this? Surely I'm not the first person
to ever encounter this??
Thanks a lot
Colin
in your form's beforeUpdate() event, check that the fields have valid
data, else display error message and cancel the event |
|
|
| Back to top |
|
|
|
| Rich P... |
| Posted: Tue Oct 20, 2009 12:12 am |
|
|
|
Guest
|
On Error goto ErrLbl
will work if you use ADO against your sql server from Access. Make a
reference to "Microsoft ActiveX Data Objects 2.x Library" (needs to be
2.5 or higher)
Sub SampleADOusage()
On Error GoTo Errlbl
Dim cmd As New ADODB.Command, j As Long
'--using integrated security
'cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=YourSvr;Database=YourDB;Trusted_Connection=Yes"
'--using Sql Server security
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yoursvr;Database=yourDB;UID=aaa;password=password;"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Insert Into tblx(fld0, fld1, fld2) " _
& "Select '" & txt1 & "', '" & txt2 & "', '" & txt3 & "'"
cmd.Execute j, , adExecuteNoRecords
Debug.Print "Records Affected: " & j
Exit Sub
ErrLbl:
MsgBox Err.Description
End Sub
Rich
*** Sent via Developersdex http://www.developersdex.com *** |
|
|
| Back to top |
|
|
|
| MGFoster... |
| Posted: Tue Oct 20, 2009 5:15 am |
|
|
|
Guest
|
Bobby wrote:
Quote: Hi,
This must be a problem which lots of people get on a daily basis, but
so far I haven't been able to find a solution.
I am using Access 2003 as a Front end to SQL Server. My SQL server
table has three fields which do not allow nulls. If the user does not
fill in the fields in Access, he gets a nasty ODBC error. I want to
capture that error and give the user an easier to understand message.
The OnError event in Access is 0 when this error occurs, as if it
doesn't recognise that there has been an error.
Can anybody tell me how I can do this? Surely I'm not the first person
to ever encounter this??
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
If you're using DAO you can get the ODBC errors in the Errors
collection. Like this:
err_:
' get the ODBC errors
strError = "ODBC errors: " & vbCrLf
Dim e As Variant
For Each e In Errors
strError = strError & e & vbCrLf
Next e
MsgBox "Error: " & strError
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSt0l2YechKqOuFEgEQK0jwCfSTB1uRAQZc6/bM8vgFguMvONy+MAoNM/
3Ln81K1xeb/Gl+p6QC+6SsB+
=yH/q
-----END PGP SIGNATURE----- |
|
|
| Back to top |
|
|
|
| Bobby... |
| Posted: Tue Oct 20, 2009 3:01 pm |
|
|
|
Guest
|
Quote: -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
If you're using DAO you can get the ODBC errors in the Errors
collection. �Like this:
err_:
� � � �' get the ODBC errors
� � �strError = "ODBC errors: " & vbCrLf
� � �Dim e As Variant
� � �For Each e In Errors
� � � � �strError = strError & e & vbCrLf
� � �Next e
� � �MsgBox "Error: " & strError
HTH,
--
Hi,
Sorry to be stupid, but where would I put the above code? It seems as
if Access doesn't even recognise that there has been an error.
Certainly it does not fire the On Error event.
Thanks to everybody for their help.
Colin |
|
|
| Back to top |
|
|
|
| MGFoster... |
| Posted: Wed Oct 21, 2009 5:15 am |
|
|
|
Guest
|
Bobby wrote:
Quote: -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
If you're using DAO you can get the ODBC errors in the Errors
collection. �Like this:
err_:
� � � �' get the ODBC errors
� � �strError = "ODBC errors: " & vbCrLf
� � �Dim e As Variant
� � �For Each e In Errors
� � � � �strError = strError & e & vbCrLf
� � �Next e
� � �MsgBox "Error: " & strError
HTH,
--
Hi,
Sorry to be stupid, but where would I put the above code? It seems as
if Access doesn't even recognise that there has been an error.
Certainly it does not fire the On Error event.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hmmm, seems to work for me. But, I haven't done this since Acc97. Ex:
Private Sub RunODBCqry()
On Error GoTo err_
' do ODBC stuff
exit_:
exit sub
err_:
' get the ODBC errors
strError = "ODBC errors: " & vbCrLf
Dim e As Variant
For Each e In Errors
strError = strError & e & vbCrLf
Next e
MsgBox "Error: " & strError
End Sub
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSt6Lm4echKqOuFEgEQL2CACg2vnpnnIl3oPuWoqjO2yrjiJ7wzkAoJaa
susVdsaTA0Mfey67z8OA1+Yf
=0/8h
-----END PGP SIGNATURE----- |
|
|
| Back to top |
|
|
|
| Chuck Grimsby... |
| Posted: Sat Oct 24, 2009 11:15 pm |
|
|
|
Guest
|
On Oct 20, 11:18 pm, MGFoster <m... at (no spam) privacy.com> wrote:
Quote: Bobby wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
If you're using DAO you can get the ODBC errors in the Errors
collection. Like this:
err_:
' get the ODBC errors
strError = "ODBC errors: " & vbCrLf
Dim e As Variant
For Each e In Errors
strError = strError & e & vbCrLf
Next e
MsgBox "Error: " & strError
HTH,
--
Hi,
Sorry to be stupid, but where would I put the above code? It seems as
if Access doesn't even recognise that there has been an error.
Certainly it does not fire the On Error event.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hmmm, seems to work for me. But, I haven't done this since Acc97. Ex:
Private Sub RunODBCqry()
On Error GoTo err_
' do ODBC stuff
exit_:
exit sub
err_:
' get the ODBC errors
strError = "ODBC errors: " & vbCrLf
Dim e As Variant
For Each e In Errors
strError = strError & e & vbCrLf
Next e
MsgBox "Error: " & strError
End Sub
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSt6Lm4echKqOuFEgEQL2CACg2vnpnnIl3oPuWoqjO2yrjiJ7wzkAoJaa
susVdsaTA0Mfey67z8OA1+Yf
=0/8h
-----END PGP SIGNATURE-----
Personally, I use DBEngine.Errors(0).Description to get to the error
string, then use a select case on that to figure out what to do...
But, whatever!
Works fine in versions up to 2003. |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Fri Dec 11, 2009 2:45 am
|
|