ConsciousVibes.com

Home | Links ( Motorcycles, Computers )

VBA (Visual Basic for Applications) Tips

All of the information, instructions, and recommendations on this Web site are offered on a strictly "as is" basis. Remember "Murphy's Law." Please take the proper precautions before attempting any of the tips or modifications listed here.

Contents

Fix for Run-time error '3027' When Exporting

Symptom:

When you try to export to a text file using VBA, you receive the following error message:

Run-time error '3027':
Cannot update. Database or object is read-only.

Cause:

The file that you are exporting to, does not have a valid file name extension that Microsoft Access expects.

Resolution:

Specify a valid file extension. The following file name extensions are valid:

- txt
- csv
- tab
- asc

Example Working Code:

DoCmd.TransferText acExportMerge, , "qryListToExport", "List to Merge With.txt", True

Reference

• ACC2000: Importing or Linking a Text File Fails for a File That Does Not Have a Valid File Name Extension
  

Get the Path to the Database (.mdb / .accdb) File

If the database is not a split (front-end/back-end) database this function returns the path to the database. If used in a split database architecture this returns the path to the front-end database.

Public Function GetDBPath() As String

    GetDBPath = CurrentProject.Path & "\"

End Function

If the database is a split (front-end/back-end) design, this function returns the path to the Back-End.

Public Function GetDBPathBE() As String

    Dim strFullPath As String

    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("tblLinked").Connect, 11)

    GetDBPathBE = Left(strFullPath, InStrRev(strFullPath, "\"))

End Function

Usage (Code Snippet)

Dim dbPath As String
Dim strDocName As String

dbPath = GetDBPath()
strDocName = "qryMailMergeSource"   ' Table or Query you're exporting from

DoCmd.TransferText acExportMerge, , strDocName, dbPath & "Mail Merge Data.txt", True
   ' e.g. C:\Data\Mailing List\Mail Merge Data.txt

Reference

• Get the Path to the Database (.mdb) File