Featured Article
Migrating Access to SQL Server
Migrating your Access database to SQL Server requires planning. Here is an overview.
Server Object Dependencies
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 »
List SQL Server Foreign Keys
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 »
Access Form Control Default Properties
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 »
Compound Primary Keys
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 »
SQL Server Tables and Columns
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 »
Record Selecting in Multi User Applications
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 »
Access Bugs
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 »
Excel vs Access
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 »
Xero API – Endpoints
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 »
Xero API – Invoices
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 »
Managing Application Updates
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 »
What We Do
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 »
Table Types
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 »
Outer Joins in Access
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 »
Application Install Guide
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 »
Normalisation
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 »
Why Access
Microsoft Access provides two main functions. Firstly it is a database container, capable of storing your data. Secondly it is an application development tool which enables you to create forms and reports that make use of your data by presenting it to the user. In fact Access is considered a Rapid Application Development (RAD) tool. It’s… Read More »
Containers, Collections and Objects
An Access Database is a container. Within this container are collections of objects which include tables, queries, forms and reports. Objects can themselves be containers. For example a table contains columns (or fields) while forms and reports contain controls that display data. So we could iterate through our database’s Tables (TableDefs) and through each Table’s Fields like this… Read More »
Naming Conventions
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 »