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 »
Migrating your Access database to SQL Server requires planning. Here is an overview.
If you have two unbound text boxes on a form, and the likely scenario is for a Date Range for Report Criteria, you may want to warn the user when the End Date is before the Start Date but get the wrong result. In this application we are using the date format dd/mm/yyyy as set… 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 »
SQL Server provides system views to display meta data like Tables, Columns and Constraints. The following View or Stored Procedure brings together some of these system views to show your FK contraints. Note the terminology – The referenced table and column as the “parent” in the relationship while the “child” is termed the Parent as… Read More »
To change the default properties for controls in forms and reports using VBA you can use the code below. For example, if you decide to change the default font or font size in your application, rather than manually opening each form and changing the defaults you can loop throught the AllForms collection, open each form… 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 »
Historically, Microsoft have introduced a significant bug in an Office release about once a year. Lately it has been more often. I think that we can all accept that bugs will happen. The main issue here is communication. Microsoft have never been good at communication. Never will be. It’s not in their nature.To start, they… Read More »
The Advantages of a Relational Database Application over Spreadsheets Why should I use Access instead of Excel to run my business? One of your major goals in management is to create systems that allow you to step away from micro managing while knowing that your business is run the way you want it to be… Read More »
Passing data to or from Xero is done by first specifying an endpoint (EP). EPs roughly translate to tables but each EP can involve more than one table. For example a response from the Invoices EP can also give you aggregated totals for Payments and Credit Notes applied to each invoice. Your API call can… Read More »
I’m currently working on an interface between an Application built using MS Access and the Xero Accounting application using Xero’s API. It’s a seriously large application not some dinky little Access app. Xero are moving to oAuth 2.0 authentication. The deprecation of oAuth 1.0, which used Open-SSL for authentication, has presented some challenges. With 2.0… Read More »
Test and Release Each client has a person responsible for testing and then releasing new versions – The Client’s Project (or Application) Manager. This is important as it ensures that – a) The Client has the opportunity to test in their environment for critical use cases before THEY release b) They share responsibility for any… Read More »
Custom Software Solutions for Your Business Designed to Run Your Business the way you want it run. When you can’t find, or don’t want an “Out of the Box” solution telling you how to run Your Business. With 30 years of experience running and advising business as a Business Analyst and Project Manager and 25 years… 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 »