MS Access select the first record of each group using First Function

Posted on April 6, 2019 by Wyman W

This MS Access tutorial explains how to select the first record of each group using First Function. MS Access select the first record of each group When I create human resources report, I have always come across a situation where one employee has more than one records. For example, I want to create a staff list where one employee only has one row of record. If I add emergency contact field to the report and if an employee has more than one emergency contacts, then the Query will return one emp...

Access VBA delete Table records with SQL using DoCMD.RunSQL Method

Posted on March 20, 2019 by Wyman W

This Access VBA tutorial explains how to use VBA delete Table records with SQL without alert using DoCMD.RunSQL Method. You may also want to read: Access VBA run Query or run Action Query Access delete Table records In my previous post, I have explained how to delete Table records without using VBA, below is a quick recap. Suppose we have created a table called student in Access, which contains 5 student records. student Student ID Student Name 001 Apple 002 Bet...

Add sorting in Access Table field

Posted on September 21, 2017 by Wyman W

This Access tutorial explains how to add sorting in Access Table field. You may also want to read: Sort Data in Access Report Add Auto number in Access Query Add sorting in Access Table field It is easy to add sorting in Access Query field in Query Design View (set in the "sort" row), but to add sorting in Access Table field is done differently. Let's say we have the following employee table, where the Employee ID is not sorted. Although you can click on the arrow next the...

Access Case Sensitive Join Table (Inner Join, Left Join)

Posted on September 16, 2017September 16, 2017 by Wyman W

This Access tutorial explains how to perform case sensitive join table, such as case sensitive inner join and case sensitive left join. You may also want to read:Access StrComp Function to Compare text (case sensitive comparison) Access Case Sensitive Join Table In Microsoft Access, comparison of text is not case sensitive. For example, Access considers "AAA" and "aaa" are the same. To compare String in Expression, please refer to my previous post about how to use StrComp Function. The m...

Access StrComp Function to Compare text (case sensitive comparison)

Posted on September 15, 2017September 16, 2017 by Wyman W

This Access tutorial explains how to use Access StrComp Function to compare text, both case sensitive and case insensitive comparison. You may also want to read: Access Case Sensitive Join Table (Inner Join, Left Join) Access StrComp Function to Compare text (case sensitive comparison) In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, if you type formula ="A"="a" The formula returns TRUE. Vlookup also shows matching result even tho...

Access VBA import workbook to Access using Transferspreadsheet

Posted on August 28, 2017 by Wyman W

This Access tutorial explains how to import workbook to Access using DoCmd.Transferspreadsheet Method, such as importing specific worksheet to Access, importing multiple worksheets to Access. You may also want to read: Access VBA DoCmd.OutputTo Method Access Export all Queries to Excel Access VBA import workbook to Access using DoCmd.Transferspreadsheet Access VBA DoCmd.Transferspreadsheet Method is used to import workbook to Access and export Access to workbook, I have briefly demo...

Access VBA run Query or run Action Query

Posted on August 25, 2017March 20, 2019 by Wyman W

This Access VBA tutorial explains how to run Query and run Action Query in Access VBA. You may also want to read: Access VBA delete Table records with SQL using DoCMD.RunSQL Method Access VBA run Query or Action Query Imagine you have to run 10 Action Query (e.g. Create Table, Update, Delete Table) in a row to get the final result, you have to run each Action Query one by one and the most annoying part is to click the confirmation dialogs. With Access VBA we can run a series of Action Qu...

Access VBA loop through all Tables using DAO.TableDef

Posted on June 18, 2017June 18, 2017 by Wyman W

This Access VBA tutorial explains how to loop through all Tables using DAO.TableDef in Access. You may also want to read: Access VBA loop through Table records Access VBA loop through all Tables Let's say we use a Macro to import 100 Tables and then we want to delete them all. It is time consuming for us to delete one by one and there is a high risk of manual error of deleting the wrong one. In this case we want to write a Macro to loop through all Tables to delete all Tables that meet c...

Access VBA delete Table using DoCmd.DeleteObject Method

Posted on June 13, 2017June 18, 2017 by Wyman W

This Access VBA tutorial explains how to delete Table (delete single table, delete multiple tables) in VBA using DoCmd.DeleteObject Method. You may also want to read: Access delete table records Access VBA delete Table In Access VBA, deleting Table can be done by DoCmd.DeleteObject Method. It is an extremely simple and straight forward, the syntax is as below. In order to delete Table, use acTable in the ObjectType argument. DoCmd.DeleteObject(ObjectType, ObjectName) Name Requ...

Access VBA import txt using DoCmd.TransferText Method

Posted on June 12, 2017 by Wyman W

This Access VBA tutorial explains how to import txt using DoCmd.TransferText Method in one Table or separate Table. Microsoft Access - import TXT using DoCmd.TransferText Method In my previous post , I explained how to use DoCmd.TransferText Method to export queries to files such as txt, this tutorial explains how to import txt into Table. First prepare a TXT file such as below Then import the txt file manually. Before the final step, create a specification (which field is what data ...