473,728 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Capturing SQL error codes in Access

Hi
I'm using Access 2003 with SQL server 2000, linked via ODBC.

Can anybody tell me how to capture SQL error codes in Access? If this
is not possible, is there any way I can simply turn off SQL errors?

Can I use docmd.setwarnin gs false? If so which event would I put it
on. On error doesn't seem to work.

Thanks

Colin

Sep 13 '07 #1
3 6398
You will need to be a bit more specific here.

If you code:
Set rs = Currentdb.Openr ecordset("SELEC T DogID FROM Dogs")
you get an error if there is no table/query named Dogs, or of that table
does not contain a field named DogID. You can't recover from that error and
continue using the recordset: that would be meaningless.

Other errors occur if you try to move to a non-existent record, or a linked
table is disconnected, or you try to insert a record without supplying a
value for a required field, or you violate a validation rule or unique
index, or try to alter a foriegn key field to an invalid value, or ...

So, you need different approaches for different kinds of errors. The Error
event of the form catches the engine-level errors. The error handling in a
procedure handles other errors. The program options allow you to suppress
some confirmation messages, and SetWarnings handles some of those as well as
suppressing other things.

If you are talking about distinguishing confirmations and error warnings
when running action queries, this might help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bobby" <bo****@blueyon der.co.ukwrote in message
news:11******** **************@ o80g2000hse.goo glegroups.com.. .
Hi
I'm using Access 2003 with SQL server 2000, linked via ODBC.

Can anybody tell me how to capture SQL error codes in Access? If this
is not possible, is there any way I can simply turn off SQL errors?

Can I use docmd.setwarnin gs false? If so which event would I put it
on. On error doesn't seem to work.

Thanks

Colin
Sep 13 '07 #2
Greetings,

In your sql server stored procedures you can use Begin Transaction
Commit Transaction Rollback Transaction and raise errors as follows --
then in Access you can:

-----------------------------------------------
Create Proc...
...parameters
As
... initialize stuff

Begin Transaction

stuff to do here

Commit Transaction

GoTo Finally --Finally is just a label to go to -
--similar to placing an Exit Sub statement
--before the Error Label -
--which I call Catch:
Catch:
RaisError('Prob lem with Procedure, 16, 10)
Rollback Transaction

--if there were no errors in the processing of the
--procedure the GoTo Finally will bypass the Catch: block

Finally:
Return
GO

-----------------------------------------------

In the Catch block, the RaiseError function takes a custom message 'your
custom message' followed by some error codes

RaiseError('you r custom message', 16, 10)
Rollback Transaction

this will undo the entire processing as if it did not happen -- then
proceeds to exit by going to Finally: Return

Sometimes Access can read these error messages which will pop up in a
messagebox. Most of the time Access can't read these error messages
because Access is heavily subclassed (since Access is really an
application and not a full blown Programming environment).

To reliably trap all sql server error messages you should create an
application from a full blown programming environment like Java or .Net.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 13 '07 #3
On Sep 13, 10:29 am, Bobby <bob...@blueyon der.co.ukwrote:
Hi
I'm using Access 2003 with SQL server 2000, linked via ODBC.

Can anybody tell me how to capture SQL error codes in Access? If this
is not possible, is there any way I can simply turn off SQL errors?

Can I use docmd.setwarnin gs false? If so which event would I put it
on. On error doesn't seem to work.

Thanks

Colin
may depend on what you're doing

one way ...

Dim c As ADODB.Connectio n
Dim r As ADODB.Recordset
Set c = New ADODB.Connectio n
c.Open (CurrentProject .BaseConnection String)
On Error Resume Next
Set r = c.Execute("SELE CT * FROM Phantom")
Debug.Print c.Errors(0).Des cription
'Invalid object name 'Phantom'.

Sep 13 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
7233
by: TD | last post by:
This is the code under a command button - Dim ctl As Control For Each ctl In Me.Controls If ctl.BackColor <> RGB(255, 255, 255) Then ctl.BackColor = RGB(255, 255, 255) End If Next ctl
14
2544
by: Brent Burkart | last post by:
I am trying to capture the Windows Authenticated username, but I want to be able to capture the login name that exists in IIS, not Windows. In order to enter my company's intranet through the internet, they have to login. I want to be able to capture that login versus their Windows login because I need to know who they are from any computer rather than only their computer. Any ideas? Thanks
17
4332
by: Lauren Wilson | last post by:
Does anyone know if it is possible to capture FTP responses to various FTP commands when managing an FTP session from a VBA procedure? For example, if we try to login to an FTP server and the login info is incorrect, FTP replies with a message to that effect. I need to capture those kinds of responses and advise the user of an Access app what is happening. Thanks for all responses.
0
23501
by: HKSHK | last post by:
This list compares the error codes used in VB.NET 2003 with those used in VB6. Error Codes: ============ 3: This Error number is obsolete and no longer used. (Formerly: Return without GoSub) 5: Procedure call or argument is not valid. 6: Overflow. 7: Out of memory.
3
1994
by: MLH | last post by:
If Err.Number = 2001 Then I have the above line in a procedure I downloaded. I don't know what error that is exactly.
8
12476
by: PW | last post by:
Hi, There is code in Alison Balter's excellant "Mastering Access 2003" to create a list of error codes and descriptions but it only generates error messages 3 through 94. Is there a website with a list of all of the error messages (with descriptions?). Thanks, -paul
2
2815
by: GS | last post by:
How can one avoid capturing leading empty or blank lines? the data I deal with look like this "will be paid on the dates you specified. xyz supplier amount: $100.52 when: September 07, 2007 reference #: 0415 from: operating account
2
3153
by: RLN | last post by:
Luke Chung at FMS Inc provide this link in another thread that provided a really helpful .pdf file error listing: http://www.fmsinc.com/MicrosoftAccess/Errors/ErrorNumberAccess2007-2000. pdf Does anyone have a VBA example where an error reference table contains the error numbers and description of the errors listed in Luke's document? Thanks.
2
19483
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
8903
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8758
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9277
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9130
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8129
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6716
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4524
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3236
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.