I am a Computer Science Uni graduate, working as an in-house programmer for a mid-sized business, using MS-Access as a front end, and SQL Server as the database backend. Putting aside the general IT cultural bias of loving to hate MS-Access, this combination often results in may real world / economical compromises and shortcuts as mentioned in the original post.

Long ago when I stared out I inherited a few MS-Access frontend apps with mdb backends. I cleaned them up, rewrote code, added indexes (there were none), added caching, etc.. when it became time to migrate to SQL Server, while I generally knew what I was doing, I still did my googling/research, read other peoples tips and various 'best practices' and thought about how I wanted to structure my programs and data, what problems I had already had, and how I may avoid them in the future, and how to best leverage the features and power of the SQL Server.

I read the tip about always doing Views on Tables, then linking to the View from the Front-End. This just made obvious sense to me ( me a lowly VBA code monkey 🙂 and I have always, always been doing it ever since. This layer of 'independence' between my front-end app and the actual data tables has allowed me to do some very nifty on the fly conversions, data cleanups, feature migration, and backward compatibility for users that dont need to or forget to upgrade, while pushing out new versions to my power user beta testers and then onto most regular users. I suppose the technique is like a poor-mans AppServer, it gains me some of the advantages of a 3-tier architecture, even though I am using a 2-tier/Client-Sever approach.

Basically the 'View of Tables' technique has has saved me time, struggle and effort, over and over again. I have not found performance to be a problem, as the View is almost always just a straight mapping (or very simple query manipulation) onto a Table. Most of my more complicated queries have ever only involved 3 or 4 tables, and these 'complicated' queries are Front-End data manipulation that usually only need small sub-sets of the tables data, and not SQL Server Views on Tables as a layer of independence.

Whenever I have coded a front-end query that ended up being slow, I have had many other techniques to fall back on (eg Local caching of just the relevant sub-set of data in the front end app, then running my query on the local cached data, Pass Thru Queries that could even use the real sql server table and not the views, Stored Procedures, Creating a static cached result set from a View/Query each night for slow running View/Queries that are based on large but infrequently changing tables)

Admittedly I work with smaller databases, and so do not really tax a decent SQL Server, nor have to face the issues that a DBA with large databases and transactional load may routinely face, but still I read your original post and was suprized to think that everyone was not already using this technique. Mr Codd who laided the foundations for relational databases back in the early 70's wrote of using Views for these reasons and more way back then.

I dont think I have ever regretted nor questioned my use of SQL Server Views-On-Tables.

Oh, and yes I would love the dbViewReader and dbViewWriter feature. I just assumed that there was a way to force a View to be read only (which is a slightly different thing to User security Role Membership) but I just had never got around to finding it

- Matt