Hardware matters. Invest in a faster PC and your Microsoft Access databases will run faster. Be alert for sellers packaging fast processors with minimal memory and a slow hard drive to keep prices low. Look at all aspects of the purchase when database speed is a priority. A faster hard drive is worth the investment…
Before we create the Make Table Query, you might like to create a new blank database file. This is so we have a separate database into which we can paste the new table from the query. In this exercise I have called it HistoricData.accdb. Here is the procedure to create a Make Table Query: Open…
Sometimes we need to use fields from a RecordSource that aren’t shown on the form. It is legal to reference them directly even if they don’t have a control. For example, we can have a form bound to a RecordSource like so: SELECT c.CompanyID, c.CompanyName FROM Companies; And only display CompanyName in a textbox but…
In standard code, we make use of #LateBind conditional compilation constant, which is typically defined via project’s Conditional Compilation Argument in the dialog: NOTE: The dialog can be found via Tools → <project name> Properties, which is located below the Options menu item. The menu item caption will be different as it includes the project…
In Microsoft Access, a null value is a value that is not assigned or unknown. When working with databases, it’s important to understand how to use and identify null values in order to ensure accurate and efficient data management. The IS NULL operator is one of the most useful tools for working with null values…
Strong preference should be given to writing code that is self-documenting. A common approach is to write a lot of comments, but the problem with comments are twofold: It actually can harm the readability because it adds to the overall length of code to read and understand. It must be correct or it can end…
SQL Server does not allow for a dynamic set, which can be problematic if you need to pivot based on the data that comes from rows and you can have various values in the column. In general, you need a stored procedure and dynamic SQL in order to handle the dynamic pivots. This requires uses…
A common programming mistake is to hard-code literals that corresponds to some property. That is particularly true with certain controls like tab controls & pages. The tab control exposes a Value property that indicates the current tab and the pages contains a PageIndex property that indicates its position in the tabs of the control. Thus,…
Constraints that are created by SSMA or by user via SSMS tend to have ugly names. Worse, auto-generated names are not stable across backups. Meaning if you restore a new database based on a copy of another database, the constraint names will change. That create huge problems during migrations because scripts that references constraint will…
Once you have created an Access Database Application to be used by other people, it may well be important for you to prevent users from gaining access to any of its design features. By this I mean, you may not want users to modify your tables, forms and queries etc in design view (inadvertently or…