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]

I have been struggling with this problem as well. When using acces
ADP’s in 2000 I would declare an ADODB result set with events and the
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 applicatio
framework at all. There is no way to get the error that acces
displays to you.

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

ado errors
http://tinyurl.com/bzjhb

This makes me crazy. I am in disbelief that is the only mechanism
have to handle ADO errors. It makes it vary difficult to utilize th
constraints set up in the SQL server. I keep poking around the we
thinking this has to be wrong or something. Unfortunately, it seem
that others have come to the same conclusion. If someone has a bette
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

The first bug that blows my mind is, when editing your data source i
takes that last character of your SQL statement sometimes. Even afte
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 name
on order by clause the SQL statement errors when the form runs.
Considering that the editor is always trying to edit them to be thi
way this it really becomes annoying. I have seen new users to acces
send hours trying to figure out what happened

The third one is that the diagrams you make in the enterprise manage
crash access. The ones created in Access 2003 don’t work in th
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 interrupte
during startup. Despite my best attempts I have never repaired on
successfully. If I didn’t use a source control system my project woul
have been corrupted and rendered useless several times. You could sa
don’t kill your task and this won’t happen. However, you only get on
chance to learn this lesson. Sometimes I still kill the thing and tak
a chance because I am crazy too.

I have been in the UNIX world and the Windows world; and, I must sa
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
new version. I guess I should be a good programmer and use .net o
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]

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

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

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

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