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…
Use controls, not fields
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….
Use TryGet pattern for transient values
A number of objects may have extra properties or something where it might not exist for all instances. A good example might be finding Controlsource property on an Access.Control variable:
|
1 2 3 |
For Each ctl In Me.Controls Debug.Print ctl.ControlSource 'A potential error Next |
Not all controls have a ControlSource property and thus can fail to run. A common approach is to use OERN:
|
1 2 3 4 5 |
For Each ctl In Me.Controls On Error Resume Next Debug.Print ctl.ControlSource 'A potential error On Error GoTo 0 Next |
This will work…
Creating a Make Table Query
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…
Access DateValue() function to SQL Server equivalent
What is the SQL Server equivalent to Access DateValue() function? Access SQL DateValue([Event Timestamp]) SQL Server Equivalent cast([Event Timestamp] as date) convert(date, [Event Timestamp])
Access 2003 Technical Articles
In this section of the MSDN Library, you’ll find technical articles that demonstrate how to build and deploy complex Access 2003 runtime-based solutions, automate Access from another application; use the new XML features in Access 2003, and even how you can animate Access 2003. You can use the TOC to your left to navigate through…
Why does Excel have a problem when using code in Access?
When you use code to refer to Excel (and other programs as well) you need to be careful not to use any code that does not refer back to the instantiated application object. If you do so then you will find that Access instantiates another Excel (or other program) object which is not visible to…
How to Display a Form Automatically when your Application Opens
This is a quick tip on how to automatically display a form when the user opens your Access Application. In addition to improving User Friendliness, your database design will also appear much more professional. The step by step instructions below will display the Switchboard form in figure 1 immediately upon the application opening. This will…
Microsoft Access File Extensions
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…
Self-documenting code
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…