Creating a query in Microsoft Access is simple and most people can learn to do it in a relatively short period of time. However, using query in Microsoft Access has two major drawbacks:
- If you want to create a parameter query and the parameters are saved in a table, it will be very slow to run such query. It can take ten times more time to run such query. If you convert the regular query into SQL query, it will avoid such problem.
- Your query is subject to the mercy of the users. Users may accidentally or intentionally change your query. If you want to maintain the integrity of the Access file, it is better to embed your query into your Visual Basic codes and then save your file into MDE or ACCDE format.
Please follow the below steps to convert a regular query to an embedded SQL query.
Step 1: Create a regular query in Microsoft Access.
Step 2: Click the “View” command on the ribbon and then choose the SQL View and you will see how the query look like in SQL coding.
Step 3: Copy the entire query using CTRL+A (select all) and CTRL+C (copy).
Step 4: If you want to create a new VB module, please click the “Create” command on the ribbon, then click the “Macro” and choose “Module” option to open the “Module”. You can also use an existing module, just open it and then put your SQL query in it.
Step 5: Create a new Subroutine in the module and paste the SQL query in it. In the below example, I created a new subroutine called “SearchByAcctNum()” and I pasted the SQL query in it. After you paste the SQL query, you will see that it is marked as red color because the VB editor regards your statement contains error in syntax. There are two reasons for the syntax error:
- It is not a VB statement. You need to add “DoCmd.RunSQL” at the front and then a parenthesis and a double quotation mark. So the VB statement should look like this at the beginning:
DoCmd.RunSQL (“SELECT ….. “)
- The SQL query code is too long and split into several lines for the VB editor to recognize its entity. You need to split the SQL statement by inserting & “ at the beginning and ” _ at the end. Please see below illustration.
Step 6: Once you are done with the subroutine and confirm that there is no error, you can put the name of the subroutine to the table of “Switchboard Items”. Your subroutine will be activated by the users when the users click the button on the Switchboard.
Step 7: Go to the “Switchboard Item” on the Tables through the navigation pane.
Step 8: In the Switchboard table, put the “8” under the “Command” field (to activate a VB Module) and put the name of the subroutine under the “Argument.
Step 9: If you create a new module, Access will ask you if you want to save the new module. Click “Yes” and give the new module a name and click “OK’ to save.
