This week I am working on bug fixes for an Access/SQL database application, for a new client. This application was written by a different consultant, and I hope I never meet this person.
Let me say, at the outset, that every time I get a plumber, electrician, carpet cleaner, or furnace/AC repair guy into my house, he looks at my {insert appropriate article here} and says that “the last guy that worked on this thing was a total idiot”. Sometimes I point out to that person that they were actually the last person to work on this thing, so why were you such an idiot a year ago? Fun with service guys.
But I digress. (oh, and I'm going to say “he“ whenever I am referring to this previous developer. I have no idea who this person is, nor their gender.)
This app has an Access front end, and a SQL Server backend. Well, pseudo-SQL Server. This guy was an Oracle developer, I am sure of it. Tell-tale signs like the lack of IDENTITY columns but a stored procedure that returns a Sequence number. The use of Numeric columns and not int. Or else this guy just didn't know too much about SQL Server in the first place, such as a complete disregard for RAISERROR, and using stored procedure output parameters to send exception messages back to the client.
And for my current pain, this guy didn't know about text columns. He has several columns that are varchar(8000) that the client wants to be unlimited length.
Not a problem, you might say. Just change the table schema and “Blob's your uncle”. I thought so too. More on this later.
As for the front end, the Access database has one local table. And no attached tables whatsoever. It has one query object that gets its Connect property set with an ODBC connection string on startup, and is left open as a global variable throughout the life of the project. (Oh, this guy has no concept of coupling and cohesion either, global variables all over the place, most are initialized during startup with strings loaded from the results of a stored procedure call).
The forms use no binding whatsoever. In the Load event, the global query object has its SQL property built to call a stored procedure that returns a single record. Then each of the field values are assigned to the controls. What passes for boolean (or bit) columns are stored on the server as char(1) and contain either 'Y' or 'N' and there is tons of code just to interpret these values and set checkbox controls on or off (can you say “factoring”, children? I knew you could...)
To save the contents of a large textbox control to the corresponding varchar(8000) column is a convoluted mess (I told you I was coming back to this).
First, because he is building all stored proc calls in SQL and not using any Parameters (no ADO or Command objects used anywhere), he sends the large textbox values to the server separately. First, he wants to get rid of all the nasty characters that might cause execution problems, so he sets up two loops (for i = 1 to len(theString) for j=32 to 255) and checks each character of the string individually to see if it falls within the desired range, and otherwise replaces it with a space, using Mid$ and so on.
I optimized this routine by simply looking for the characters we didn't want (0-31) and using the Replace string function to do all the search/replace on the string in one call. Oh, and just to be sure, I did a replace on the double quote (char(34)) and replaced it with (char(34) & char(34)). That was missing from the original code, and it pretty critical to success, don't you think?
Now that the string is properly escaped, he breaks it up into ten 100 character chunks, and calls a stored procedure that has ten varchar(100) parameters, a key, and a column name, and stores the values into a permanent work table. Rinse and repeat until you have gone through the entire string. (oh, I will have to move my double-quote replacement routine. It could barf when the 100-character boundary is between the double-double...)
Then the rest of the form row is sent to the server via a stored procedure. That stored procedure calls another stored procedure for each varchar(8000) column and reassembles the 100-character chunks in the work table into a single varchar(8000) output parameter.
Then the main stored procedure uses the input parameters and the results of the extended text procs to insert or update the row into the table.
See? I told you it was convoluted.
The moral of the story is: know and use the capabilities of the systems you choose for development.
Binding in Access forms is goodness.
Linked tables in Access is goodness.
Stored procedures are goodness.
Text columns are goodness.
Access queries and Parameters are goodness. Use them. Let the tools do the character escaping for you.
Don't try to make SQL Server give you a Sequence number like Oracle does. Just use the Identity property on your column.
Avoid using global variables wherever possible. Use local variables 90% of the time, module scope variables 9.99% of the time, and global variables 0.01% of the time. If you think you need to use a global variable, just say no, walk away, sleep on it, call your sponsor, go to a meeting, drink some coffee, do whatever, just don't DO IT.
Some people might look at this rant and say that Access isn't a REAL development environment, so you might expect some shoddy code. I disagree. Access has a bad rap because too many Excel-macro experts decided they should try to build something in Access without knowing anything about normalization or good programming practices. Or someone who was used to working with Oracle and perhaps VB or Delphi or PowerBuilder wanted this contract to build an Access front end for a SQL Server backend.
This developer didn't use any of the features of Access that makes Access a nice development environment to work with. And this developer didn't use any of the features of SQL Server that make it a good RDBMS either. At least my new client didn't let this developer get to Strike Three.
Mike