Error handling at the database level

Question

Hi,

I have done a simple inventory system in VB 6.0 using Access 2003.

But now I have the requirement to modify the system so it could handle the input errors at the database level.I know this sounds bit crazy but that's my assignment requirement.

For example if a user has entered a string value where a numeric should come, this error should be handled at the database level using exceptions.

I want to know whether Access is capable of doing that?

and can we use stored procedures with Access?

Thanks.

Regards!

Answer 1

If it's just a matter of string vs. number, you can handle that with a mask on the field.  I.E. if your text field is for a social security number enter 000-00-0000;;_ in the input mask property of the field.  You can select from several preformatted masks or create your own.

Answer 2

You can certainly do this in Access 2010 at the table level. Access 2010 has table triggers and you can even run validation code and cancel a update.

 

However, doing this in VB6, now that I don't know if that is possible. (VB6 is now 11 years old).

 

To get an idea of stored procedures in access 2010, you can see this article of mine here:

http://www.members.shaw.ca/AlbertKallal/searchw/WebSoundex.htm

 

However, you need the latest version of the data engine (ACE) to do this. Those stored procedures and new table triggers do work, and will work even with VB6. However, I don't know if trapping of update errors as result of a trigger canceling the update works in VB6.

 

So, the new version of the access data engine (ACE) can do what you want. I just not tested what happens and if the errors can be trapped by error handling in VB6 when you use these new features. Note that these new features can run on machines without access having been installed. While the new data engine can be installed separately, I quite sure you need access to edit + setup + create these new table triggers and stored procedures.

 

--
Albert D. Kallal  (Access MVP)
Edmonton, Alberta Canada
[e-mail]

 

Answer 3

Hi,

Thanks alot Mr.Kallal

I know VB is old now.but with the time limitations I have I can't implement it in another language.

That is why I'm trying this way.

By the way does this "access data engine" comes with Office 2010 or should I install it separately?

Thanks

Answer 4

Well, VB is old, but so is access! (so, no hard feelings on that issue!)

 

One of the GREAT things about the JET data base (the access database engine), that every copy of windows since windows 2000 has a copy pre-installed. You never had to install anything, and liking your VB6.exe would run without any additional software.

 

For the new stored procedures and triggers version you need the 2010 version of JET engine which is now called ACE.

 

You don't need office 2010 (nor to install office 2010). You can get this engine installed 3 ways:

 

1) Install access 2010 (installing office is not enough as far as I know)

 

2) Install free Access 2010 runtime (this is free copy of access that lets you run access applications without having to purchase access).

 

3) Download the ACE engine. (this is your best choice)

 

download link:

 

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en

 

 

So, you don't need access 2010 or to install office 2010 to get this new version of jet (now called ACE). However, to use the new table triggers and stored procedures I am reason sure you need access. Once the triggers + stored procures are written, then they will work, and work EVEN on machines without access, you just have to ensure the above data engine is installed. Any update or row of data modified in VB6 will in fact cause the triggers + stored procedure code to run.

--
Albert D. Kallal  (Access MVP)
Edmonton, Alberta Canada
[e-mail]