Download Sample Access File: MS Access Vs Text file
Text file can be used in combination of MS Access in the method of transferring, output, or linking. Some time we want to same certain data in separate file from Access for tracking purpose and don’t want to save it in Access file due to the size of database. The certain data is not used in the production or activity in the production program. We can save this data in the Text file that we can bring it back any time or link it to different program. The most benefit of text file is using a small size of disk space.
Forms and Highlight functions in this sample file include:
1. Form Export User LogIn Logout Time to Text
This form is sending the data to text when the users log in or log out. It saves the login ID, Login or logout type, login-out date/time, and login from what program. Every time the users login/out, it will call function from Module to save the information into the text file. It appends to the existing data on text file. This text file is recording all user login/logout times that we can retrieve this information later if we want.
2. Form Export Using Docmd.OutputTo method
There are three buttons on this form that show how to use the Docmd.OutputTo command to export data from table, query, and report to text file. The output will display differently in text file as shown below.
Example of data exporting from Table: The output command will export data from every field in that table. This method will save all data from table. Every time we click on button Export Table to Text or execute the function, it will export data to that file and overwrite the existing data on that file (have same filename).
Example of data exporting from Query: The output command will export data from every field in that query. We can choose certain fields in the query to export. Per example below, I choose only three fields in query; customer_ID, customerName, and Address.
Example of data exporting from Report: The output command will export data from every field and report format in that report. The format on the text file will be similar to the report format. This method is not popular for exporting to text. Instead, exporting report to PDF is more popular to keep the same format on PDF file.
3.Form Export Using Recordset method
This method is the most popular for exporting data from MS Access to text file. This form shows three different ways by exporting data from table, query, and from SQL to text file. It uses a comma to separate between fields in text file.
Example of exporting data from Table: the command will open a recordset of table then export all fields of each record by using a loop function. The example below is exporting all fields from table customer.
Example of exporting data from Query: the command will open a recordset of query then export all fields and all records from the query. We can choose certain fields in the query to export. Per example below, I choose only three fields in query; customer_ID, customerName, and Address.
Example of exporting data from SQL statement: the command will open a recordset from a SQL statement then export the data to text file. The example below is exporting data from SQL statement “Select customerName, Customer_phone from tbl_customer.” There are only two fields (CustomerName and phone number) from customer table that exported to text file.
4. Form Import Using Docmd TransferText method
There is only one button on this form that show how to use the Docmd.TransferText command to import data from a text file to MS Access table. The example below shows the data in table ImportLogInOut from a text file “ExportUserLogIn-Out.”
Download file contains:
4 tables, 1 query, 3 Forms, 2 Reports, and 1 Module
