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
Get the Path to the Database (.mdb / .accdb) File
Visual Basic .NET Tips
Visual Basic 6 Tips
Computer Tips Index

 


[ Top ] [ Home ]

 

Fix for Run-time error '3027' When Exporting

OS:

Windows 7 (64-bit)

Application:

Microsoft Access 2007

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.

VBA Error 3027

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

 

 

 


[ Top ] [ Home ]

 

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

OS:

Windows 7 (64-bit)

Application:

Microsoft Access 2007

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

 

 


[ Top ] [ Home ]

 

All items Copyright ©1996 - 2017 Chin. All Rights reserved Reproduction without explicit permission is prohibited. See: Terms of use