handling ado errors

[Marius Kavaliauskas]

I have ADP (Access 2003) nad I have difficulty handling ADO errors.

I have table that has some fields that have to be unique. When enters
udplicate data in these fields, he gets an error:

Violation of Unique key constraint 'XXX'. Cannot insert duplicate key in
object 'YYYY'

The main problem is that Form_Error is called and DataErr has the value
2627. How to get the real problem, because I want to thranslate errors to
Lithuanian language?

 

[Philipp Stiefel]

I have ADP (Access 2003) nad I have difficulty handling ADO errors.

I have table that has some fields that have to be unique. When enters
udplicate data in these fields, he gets an error:

Violation of Unique key constraint 'XXX'. Cannot insert duplicate key in
object 'YYYY'

The main problem is that Form_Error is called and DataErr has the value
2627. How to get the real problem, because I want to thranslate errors to
Lithuanian language?

Have you thought about translating the SQL-Server sysmessages table?
SQL-Server by itself does support localized errormessages and it does
support the Lithuanian-Langugage (mslangID = 1063). Unfortunately
AFAIK Microsoft does not supply a localized version of sysmessages
in Lithuanian.

If you add translated messages to the sysmessages-table of SQL-Server
and set the appropriate language for the SQL-Server Logins you may
solve your problem without changing a single line of code in you
application.

Read the chapter "SQL Server Language Support" in the SQL-Server
documentation for further information.

Cheers
Phil

 

[Marius Kavaliauskas]

I have tried to do as you say, but... access sets language back to english
and displays english error.

 

[Philipp Stiefel]

I have tried to do as you say, but... access sets language back to english
and displays english error.

One of my customers has experienced that problem as well.

My solution to that problem was to (re)set the language setting
right after the startup of my application.

CurrentProject.Connection.Execute "SET LANGUAGE German"

But I haven't received any feedback from the customer if this
has solved the problem for good.

Cheers
Phil

 

[Malcolm Cook]

hmmm

ADP creates connections on the fly of its own (to deal with subforms and
other cruft) that may not get the advantage of this approach.

hmmm

 

[Philipp Stiefel]

ADP creates connections on the fly of its own (to deal with
subforms and other cruft) that may not get the advantage of this
approach.

Well, yep, this is true. I'm quite aware that this approach is
not a perfect solution. Nevertheless that problem usually should
not be real issue. The new connections Access opens at will, are
only used for read-operations. IIRC any write-access to SQL-Server
will use the main-connection, which uses the correct language.
It should be extremly rare that an read-operation will cause an
SQL-Server-Error in a thorougly tested application.

Cheers
Phil

 

[chakatheapeman]

This makes me crazy.

I have been struggling with this problem as well. When using access ADP’s in 2000 I would declare an ADODB result set with events and then use it to respond to ado errors.

HOWTO: Trap for ADO Connection Errors Using WithEvents
http://support.microsoft.com/kb/q190991/

In access 2003 the errors collection is not exposed in the application framework at all. There is no way to get the error that access displays to you.

The work around I have seen for this is laborious. Basically you declare a connection and results set and try what access is trying to do and catch the errors and handle them in your own temporary ResultSet with its own errors collection. You probably also want to catch the application defined error in the error event in Access and do a ACDataErrContinue so that access doesn’t display the ado error to the user.

ado errors
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconadoerrors.asp

This makes me crazy. I am in disbelief that is the only mechanism I have to handle ADO errors. It makes it vary difficult to utilize the constraints set up in the SQL server. I keep poking around the web thinking this has to be wrong or something. Unfortunately, it seems that others have come to the same conclusion. If someone has a better way I would like to hear it.

 

[aaron.kempf]

microsoft is crazy for not taking ADP seriously.

that is the problem; kids are running the show in redmond. and they're
obviously excel dorks.. since Access ADP has a billion bugs in it still

 

[chakatheapeman]

I didn’t want to rant. But, yeah what’s going on in Redmond

I didn’t want to rant. But, yeah what’s going on in Redmond

The first bug that blows my mind is, when editing your data source it takes that last character of your SQL statement sometimes. Even after you just told it that you didn’t want to save your changes.

The second one is when field names are qualified with the table names on order by clause the SQL statement errors when the form runs. Considering that the editor is always trying to edit them to be this way this it really becomes annoying. I have seen new users to access send hours trying to figure out what happened

The third one is that the diagrams you make in the enterprise manager crash access. The ones created in Access 2003 don’t work in the enterprise manager. I haven’t ever looked for a fix or explanation, beyond service packing my SQL client. That didn’t fix anything.

Fourth access projects can get corrupted when they are interrupted during startup. Despite my best attempts I have never repaired one successfully. If I didn’t use a source control system my project would have been corrupted and rendered useless several times. You could say don’t kill your task and this won’t happen. However, you only get one chance to learn this lesson. Sometimes I still kill the thing and take a chance because I am crazy too.

I have been in the UNIX world and the Windows world; and, I must say that the Windows world seems to be terribly more productive. However, the lack of bug fixes and improvement to Access is disheartening to me. Office 2003, it is going to be 2006 soon and I have heard nothing of a new version. I guess I should be a good programmer and use .net or something. The problem is I can get allot more done with Access.

 

[aaron.kempf]

new version?

well all i know is that Access development team has more people than at
any time in it's existence.

i just hope that they keep ADP.. even if it means just moving the
coding to VB.net and keeping everything else identical.

something crazy is going on.
i just wish that MS would be more forthcoming. From everythign that
I've seen-- MS rants and raves about how Visual Studio Office Tools do
'wonderful wondeful things for Excel and Word and Powerpoint'.

WHO CARES ABOUT THOSE 3?

TELL US WHATS GOIN ON WITH ACCESS JERKS IN REDMOND!!!

Access is a LOT more productive than .NET. I know that first hand.
But Microsoft will make .NET easier to use.

for starters; dreamweaver has some awesome ASP.net wizards.. it just
breaks my heart that MS can't make a real web-dev program.. i mean.. i
haven't been able to stomach any of Microsoft's web development
products for 10 years for christ sakes.. i've been using NOTEPAD,
Homesite and Dreamweaver..

isn't it time for MS to take Access seriously?

All that they need to do is this:

a) take existing DAPs and turn them into forms
b) use Office Web Components for reports

make somethign JUST like Access-- but have it create webpages.. I'd
rather they do somehing webbased than something VB.net windows forms
based

but MS is run my excel dorks and C++ crackheads..

so I ask for Microsoft to immediately spin off Microsoft Access into a
seperate company.

your management of this product has been a travesty and you owe us all
a friggin refund.

-aaron

ps - create proc sphappy is my fave ADP bug.. I can't abbreviate the
word PROCEDURE as PROC because of some jerry-rigged ADP bug and this
syntax is listed in Books Online.. so FRIGGIN FIX IT REDMOND

 

[chakatheapeman]

If they just exposed the ADO erros collection somehow, I might just stick with it

The inability to get the ADO errors collection totally Nerfs Access.

My past experience programming enterprise level J2EE applications made down scaling to Access a slightly uncomfortable. I made this decision to take advantage of RAD programming techniques, by crating a prototype application quickly.

I found my users like an application experience more then a web experience for business use. I know .Net can do all the things that you could in a traditional application in a browser. It provides tools that are not part of HTML like Combo Boxes. These objects are event driven and flexible. Web applications are traditionally user driven and unresponsive. When I worked with Java and struts you really couldn’t do it the way you can in an executable application. It allowed for more of an event driven model but nothing like what MS has done with .Net.

I am now refactiong my ADPs to clean them up before moving to a new application framework .Net. I am sure that I will have a learning curve. Part of me just wishes that Access could just be a little bit more. If they just exposed the ADO erros collection somehow, I might just stick with it for awhile longer. I think if MS does come up with a .Net version of Access they should just make it a developer tool that puts together a tool set for application development against data sources. I really don’t think I need a web based application but part of me says why not. I could get away from pushing out updates to nosey users.