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: SQL Server View Primary Key in Access »
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: List SQL Server Foreign Keys »
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: SQL Server Tables and Columns »
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: Access Bugs »
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: Excel vs Access »
Migrating your Access database to SQL Server requires planning. Here is an overview.
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: Managing Application Updates »
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: Outer Joins in Access »
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: Containers, Collections and Objects »