Linking a new View You can link to a SQL Server View in your Access Application using DoCmd.TransferDatabase in the same way that you would link to a table. In the following example you would use the name of the view for both the source table name and the table name (unless you wan’t to… Read More »
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 »
Listing Tables and Columns for SQL Server Views, Triggers and Stored Procedures If you want to know which server objects (Views, Procedures, Triggers or Functions) use which tables and columns then you’ve come to the right place. Maybe you need to replace or deprecate a column or two. To create this list we need –… 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 »
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 »