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 ...

MS Project shift the whole schedule

Posted on June 3, 2017 by Wyman W

This MS Project tutorial explains how to shift the whole schedule using Move Project or shift part of the schedule or tasks in Microsoft Project. MS Project shift the whole schedule Before a project begins, it is common to postpone the project schedule due to many reasons, especially when a schedule requires many stakeholders to review and approve. Therefore it is highly likely that we need to adjust our schedule repeatedly before our project begins. This kind of moving schedule action is best...

MS Project delete Summary Task without deleting subtasks

Posted on May 30, 2017 by Wyman W

This MS Project tutorial explains how to delete Summary Task without deleting subtasks in Microsoft Project. Microsoft Project delete Summary Task Suppose we have created a Summary Task for Task 1 to 3. If we decide to delete the Summary Task, highlight row 1, then press keyboard Delete button. You would receive a warning message that deleting Summary Task will also delete all subtasks. If we click OK, task 1 to task 3 will be deleted. However, somet...

Access VBA loop through Table records

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

This Access VBA tutorial explains how to loop through Table records or Query records in Microsoft Access. You may also want to read: Access VBA loop through all Tables using DAO.TableDef Access VBA loop through Table records Suppose we have an Acess Table or Query and you want to loop through the records (in Excel terms, to loop the values in each row), we have to make use of Recordset objects, which has different Methods to help us manipulate data in a database at the record level. I...

Microsoft Access produce Cartesian product with Cross Join

Posted on February 8, 2017February 15, 2017 by Wyman W

This Microsoft Access tutorial explains how to produce Cartesian product with Cross Join in Access Query. You may also want to read: Create Cartesian product with Excel Query Microsoft Access Cartesian product with Cross Join In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join. Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cartesian product. Most...

Solution to Access Error 3047 Record is too large

Posted on February 5, 2017September 24, 2017 by Wyman W

This Microsoft Access tutorial explains how to work around the error 3047 Record is too large when using DoCmd.TransferSpreadSheet Method. Access Error 3047 Record is too large Although Microsoft Access is a database management system, unfortunately it does not allow users to put too much data in the database. According to Access 2016 specifications , Access Table record is limited to 4000 characters, and the total size of Access is 2GB. If you create a Table that contains a record with ...

Access replace Crosstab Query with Expression

Posted on February 5, 2017 by Wyman W

This Microsoft Access tutorial explains how to replace Crosstab Query with Expression in order to add multiple aggregate values. Access replace Crosstab Query with Expression In previous post, I have introduced how to use Crosstab Query to build a matrix report. However, Crosstab Query has a limitation that it can only add one aggregate value in the report, which is the Sum of amount in the above example. I also don't like the fact that I need to define the order of the month in the Pro...

Access VBA change Query criteria using QueryDef.SQL Property

Posted on February 1, 2017 by Wyman W

This Access VBA tutorial explains how to change Query criteria in VBA using QueryDef.SQL Property. You may also want to read: Create Query using CreateQueryDef Access VBA - change Query criteria It is easy to change Query criteria in Query View, but sometimes your criteria may depend on a variable that is not a constant. In Access VBA, you can change the Query criteria using QueryDef.SQL Property. In fact, QueryDef.SQL does not really update just the update the criteria of the statement,...

Quickly search email in Outlook using criteria

Posted on January 29, 2017 by Wyman W

This Microsoft Outlook tutorial explains how to search email in Outlook using criteria and logical operators. Search email in Outlook If you receive dozens of emails everyday, it is extremely difficult to search a specific email or relevant emails. I have seen most people would sort by date and then sort by sender / receiver to search email, but it is extremely time consuming. Microsoft Outlook has a Search Tool which allows you specify the criteria, but that is still not quick enough to nav...

Access VBA create Query using CreateQueryDef

Posted on January 18, 2017February 1, 2017 by Wyman W

This Access tutorial explains how to create Query in Access VBA using CreateQueryDef Method. You may also want to read: Change Query criteria Access VBA create Query Sometimes we don't want to create Query in Query Design View, because you may create a Query with criteria depending on a variable. To create Query in Access VBA, you can directly write a SQL statement in Access VBA, and then create a Query using the SQL statement with CreateQueryDef Method. Syntax of CreateQueryDef expres...