Access 2002 Project and Error Code fom SQL Server

[S.L.]

I'm deciding my next application project, whether or not to use Access 2002
Project and SQL Server to develop.

Reason is I found KB-301693 "ACC2000: Form Error Event Does Not Return
Native SQL Server Errors in an Access Project". And it write "This problem
was corrected in Microsoft Access 2002.". For me, This is a big problem. If
it still exist in Access 2002, My program can not detect what error and
finally my users will call me for service all day/all night.

So, I 'ld like to ask you :

Have you found this problem still exist in Access 2002 ? It sounds I not
trust Microsoft. I can help asking this. It affect my life.

Regards,
Suntisuk

 

[Uwe Ricken]

Hi Suntisuk

where is the problem. If you fire a stored procedure with
a command object all native SQL-Errors are in the error-
collection of the command object.

e.g.

CREATE PROC dbo.sp_Test
AS
SET NOCOUNT ON

RAISERROR ('This is a joke from sql-server', 11, 1)

SET NOCOUNT OFF
GO

GRANT EXECUTE
ON dbo.sp_Test
TO public
GO

VB(A)-Code to fire the above procedure
On Error Resume Next
Dim cmd As New ADODB.Command

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.sp_Test"
.CommandType = adCmdStoredProc

.Execute
End With

' Now we move through each error resulting from
' the command-object
For Each objErr in cmd.Errors
MsgBox objErr.Description,,objErr.Number
Next objErr

HTH ;-)

--
Gruß, Uwe Ricken
MCP for SQL Server 2000 Database Implementation

GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
____________________________________________________
APP: http://www.AccessProfiPool.de
dbdev: http://www.dbdev.org
FAQ: http://www.donkarl.com/AccessFAQ.htm

 

[Vadim Rapp]

S> Have you found this problem still exist in Access
S> 2002 ?

to a degree. Dataerr returns different numbers for different errors, but
error(dataerr) returns "application-defined error" for all. But if you are
going to establish your own custom messages for each error anyways, then it
shouldn't be a showstopper.

Vadim

Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com