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…
Microsoft Access saves information under the following file formats: File Format Extension Access Project (2007, 2010) .adp Access Blank Project Template (2007, 2010) .adn Access Database (2007, 2010, 2013, 2016) .accdb Access Record-Locking Information (2007, 2010, 2013, 2016) laccdb Access Web App Reference .accdw Access Signed Packages .accdc Access Database, used for Add-ins .accda Access…
VBA is quite very loose in what it deems “truthy” and “falsy”. For example, this procedure can produce surprising result:
|
1 2 3 4 5 |
If Me.MyBitField.Value = True Then 'It is true Else 'It is false End If |
At the first glance, it seems expected that if the bit field is set to 1, it would succeed. But in fact, it wouldn’t because True is equivalent to -1, not 1. Unless we…
Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers. We do this by creating a public function with the VBA programming language and save it inside a global module within the database. Today I am going to explain how custom functions can be…
Symptoms Whenever you run a SQL statement you get a “Too Few Parameters, expected X.” Cause Most common cause is misspelling or missing fields in the SQL statement. Resolution Check the SQL statement. If necessary, copy and paste to an Access query and test in the query to get it to highlight which part of…
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,…
What is the SQL Server equivalent to Access IIf() function? Access SQL IIf([ResultValue]>=1, [Result Value], Null) SQL Server CASE WHEN [Result Value]>=1 THEN [Result Value] ELSE NULL 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…
There are cases where a table’s primary key is referred to often in the code. In fact, it might have some special meanings in code. It is highly encouraged to avoid the use of IDENTITY or AutoNumber (or any auto-incrementing schema) for those keys because it is also usually desirable to create a VBA Enum…
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:
|
1 2 3 4 |
SELECT c.CompanyID, c.CompanyName FROM Companies; |
And only display CompanyName in a textbox but not show the CompanyID….