Why would you want to? Well, because you moving a whole lot of repetitive development from individual forms to a reusable class where it is shared by all your forms. What can I do with Sub Classes? Things like Enabling Save and Undo buttons when you edit (dirty) a form, disabling those buttons when you… Read More »
Relational Databases allow you to use Compound Primary Keys. But should you? Both Access and SQL Server allow you to create Compound Primary Keys. They may seem like a good idea for some tables. But don’t… It’s a trap. For example, you may be thinking of using a compound PK in a joining table like… Read More »
Stored Procedure to list Tables and Columns in your SQL Server Database If you want a list of the User Tables and Columns in your SQL Server Database you can use this Stored Procedure. Using the WITH keyword for Common Table Expresssions (CTEs) we begin by getting a list of tables from the system view… Read More »
Developing applicaitons requires a bit of thought, and experience. Here is one scenario and the solution. This sample application looks at how to allocate Items (Fixtures and Fittings) into the Rooms of a soon to be constructed Hotel. The intent being to compile purchase orders, and eventually distribute the physical items to their intended locations.… Read More »
To my way of thinking, there are two types of tables. Entity Tables and Transaction Tables. It is important to understand the difference. Entity Tables Describe an entity and its attributes. Customers are entities, Products are entities. Entity table records are mutable. That is to say, they can be edited. You can edit an entity’s… Read More »
The Access Query Designer does some great stuff but what it won’t do is an Outer Join. What is an Outer Join? If we have two tables, in this example Sales and Orders and we want to see a full list of customers who have placed Either an Order or a Sale and also see… Read More »
In its simplest form, an Access Application, or indeed any database application is made up of two components, Data and User Interface (UI). This is referred to as a Client Server architecture where the Data, otherwise known as the Backend (BE), is situated on a Server where it can be shared by all users (although… Read More »
A relational database allows you to ‘normalise’ your data. Normalising data means that you only have to store repeated information in one place. For example, if you are storing invoice data then each invoice for the same Customer does not need to store the address of the customer, this is held in a Customer table… Read More »
When Naming Objects, The first purpose of Naming Conventions is to ensure that the names of objects are unique. Access may allow you to give the same name as a table to a form or report (but not a query) but that doesn’t mean you should. At some point you may need to rename an object… Read More »
If you add, or edit, records in a Form then any Combo or List box based on that form’s Datasource is no longer up to date. I’ve seen many attempts to handle this and most are very clunky, others difficult to maintain. What we should be doing is requerying all the Lists in our Application when,… Read More »