First Published 7 Aug 2024


In addition to the large number of official VBA functions supported by Access, there are also a number of hidden items which are available to use.
Examples include: Wizhook, AccHitTest, accLocation , SaveAsText and LoadFromText.

Hidden items are not officially supported or documented. To view these items, you need to click Show Hidden Members in the Visual Basic Editor (VBE)


However, hidden members can be used whether or not the item is shown in the VBE.

In theory, as these are not offically supported, they could be removed at any time.
However, in the case of SaveAsText / LoadFromText, they are so useful and widely used that their removal is extremely unlikely.

Both SaveAsText and LoadFromText are members of the Application object in the standard Access reference library.



1.   SaveAsText

Syntax:
SaveAsText(ObjectType As AcObjectType, ObjectName As String, FileName As String)

Example Code:

Application.SaveAsText acForm, "frmMain", "G:\MyFiles\SavedObjects\frmMain.txt"

Application.SaveAsText acQuery, "qryUpdateLang", "G:\MyFiles\SavedObjects\qryUpdateLang.txt"

NOTE:
If preferred, you can use the constants associated with each object type:
1 = acQuery, 2 = acForm, 3 = acReport, 4 = acMacro, 5 = acModule. (0 = acTable is not supported)

The exported text file will include all object properties together with any code, embedded macros etc.      
SaveAsText can be used with all database object types EXCEPT tables

To export a table as a text file, you can use DoCmd.TransferText instead.

Syntax:
TransferText([TransferType As AcTextTransferType = acImportDelim], [SpecificationName], [TableName], [FileName], [HasFieldNames], [HTMLTableName], [CodePage])

Example Code:

DoCmd.TransferText acExportDelim, "DAPExportTableSpec", "tblSettings", "G:\MyFiles\SavedObjects\tblSettings.txt", True

Typical usage:

a)   Make a backup copy of one or more database objects for later importing into another database
b)   Export objects to a text file and view the object properties
c)   Export macro to a text file and read the contents e.g. to view macro XML as text
d)   Export query to a text file and read the query SQL



2.   LoadFromText

Syntax:
LoadFromText(ObjectType As AcObjectType, ObjectName As String, FileName As String)

Example Code:

Application.LoadFromText acReport, "rptLangScripts", "G:\MyFiles\SavedObjects\rptLangScripts.txt"

Application.LoadFromText acModule, "modDesignFeatures", "G:\MyFiles\SavedObjects\modDesignFeatures.txt"

Once again, LoadFromText can be used with all database object types EXCEPT tables
To import a table as a text file, you can use DoCmd.TransferText instead.

Example Code:

DoCmd.TransferText acImportDelim, , "tblCustomers", "G:\MyFiles\SavedObjects\tblCustomers.csv", True

Typical usage:

a)   Import one or more database objects into another database
b)   Import objects from a backup to replace a corrupted copy
c)   Import the contents of a macro as plain text from a text file
d)   Read the properties of one or more database objects for analysis



3.   Export All Tables to another database

There is one further very useful method available for SaveAsText that is not widely known about.

Syntax:
Application.SaveAsText 6, "", "FullPathToExternalDatabase.accdb"

This will export ALL tables (including linked) from the current database to another specified database.
The database will be automatically created if it doesn't exist
This code also copies all options, DBEngine properties and additional system tables not already present.



4.   Import All Tables from another database

The same syntax also works for LoadFromText

Syntax:
Application.LoadFromText 6, "", "FullPathToExternalDatabase.accdb"

This will import (or link) ALL tables from the specified database to the current database.
Additional system tables not already present are also imported
This code also copies all options and DBEngine properties



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 7 Aug 2024

Return to Code Samples Page

Return to Top