Error Messages and Best Practices

[CPGDeveloper]

Hi Everyone --

I administer an application -- MS Access FE/Azure SQL BE -- would love to get people's opinions on best practices concerning Error Messages.

This application has, at the moment, has 120 different possible error messages, and I have put these error messages in a back end table, and written a public subroutine to access the proper message in code. Something like this:

Sub ErrorMessage(enum as Integer)

Dim emsg As String
Dim ers As New Adodb.recordset
Dim esql as String

esql = "SELECT message FROM temessage WHERE id = " & enum & ";"
ers.open currentproject.connection, esql
emsg = ers.Fields(0).value
ers.close
set ers = nothing

Msgbox emsg

End Sub

So in my application, when there is an instance to pop-up an error message, instead of

MsgBox "This is your error"

I use the subroutine to find the stored error message

Call errormessage(25) '25 being the 'This is your error' message

The idea being that if the messages changes, I simply change it in the table, as opposed to having to change the code and put out a new version. Is this overkill? How is this usually done? Any thoughts would be appreciated. Thanks.

 

[Micron]

Maybe a wee bit of overkill. You could simply DLookup() instead of opening/closing a recordset as well as making a connection.
Don't really need Call statement but it's not wrong either.

 

[CPGDeveloper]

Thanks for the feedback Micron -- I've gone back and forth about Dlookups -- one one hand its obviously less code to write, but I also somehow have it in my head that Dlookups are optimized for local tables, not so much for linked SQL Server tables -- but I'm not sure why i think that -- I also came across this article -- advocating for another alternative to dlookups, if anyone finds this of interest --

Alternative to DCount and DLookup with SQL Server Backend

One of the major issues we have encountered with Access is the use of DLookup and DCount when using SQL Server tables.

accessexperts.com

 

[mdmilner]

I like it. I don't consider it overkill at all, it's all about future maintenance.

Personally, and I'd like to assume others do the same thing, I control procedural level code through table values whenever I get to the point of assembling a single complete process. For example, enabling a specific location for a specific set of code/process but take that to the extreme in that everything that needs to be persistent and related to a given process is stored in a table. Usually it ends up as normalized 2-3 tables in a parent/child arrangement.

Mike

 

[CPGDeveloper]

Good to know Mike -- thanks for the feedback -- I'm also wondering if I should create a Public Enum for this, but at some point, I feel like that just becomes fancier hard coding. I suppose in any kind of development, we always have to assess if the amount of labor makes sense for what is being done.

 

[Micron]

I suspect that the slowness referred to was mainly due to doing domain aggregate functions, although they didn't say. Have to wonder what the outcome would have been by removing those and only using 1 lookup as you might be. I wouldn't put aggregating over table field(s) in the same boat as returning a single record, but have to admit I can't recall if I ever did DLookup on anything other than Access tables.

This was a draft on my phone. Maybe not timely but oh well ...