MS Access: Mastering the CopyFile Method to Folder with Renaming from Record Set

Are you tired of encountering the “Property Not Supported” error when trying to copy files to a folder and rename them based on a record set in MS Access? Fear not, dear reader, for we’re about to embark on a journey to conquer this frustrating issue once and for all!

The Problem: “Property Not Supported” Error

When attempting to use the CopyFile method in MS Access to copy files to a folder and rename them based on a record set, you may encounter the dreaded “Property Not Supported” error. This error occurs because the CopyFile method doesn’t support the renaming of files while copying them. But don’t worry, we have a solution!

The Solution: Using the FileSystemObject and a Loop

The solution lies in using the FileSystemObject (FSO) and a loop to iterate through the record set, copying and renaming the files one by one. Sounds simple, right? Let’s dive into the details!

Step 1: Create a New Module in MS Access

Open your MS Access database and navigate to the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic in the ribbon. Create a new module by clicking Insert > Module or by pressing Alt + F11 again.

 INSERT THE FOLLOWING CODE IN THE NEW MODULE:


Option Explicit

Sub CopyFilesAndRename()
' Declare variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fso As Object
Dim file As Object
Dim folder As Object
Dim newPath As String
Dim newFileName As String

' Set database and recordset objects
Set db = CurrentDb()
Set rs = db.OpenRecordset("your_table_name", dbOpenTable)

' Create a new FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

' Loop through the record set
While Not rs.EOF
' Set the source file path
file = "C:\SourceFolder\your_file.txt"

' Set the destination folder path
folder = "C:\DestinationFolder\"

' Create the new file path and name based on the record set
newPath = folder & rs!Field1 & "_" & rs!Field2 & ".txt"

' Copy the file and rename it
fso.CopyFile file, newPath

' Move to the next record
rs.MoveNext
Wend

' Clean up
Set file = Nothing
Set folder = Nothing
Set fso = Nothing
Set rs = Nothing
Set db = Nothing
End Sub


Step 2: Configure the Code

In the code above, replace “your_table_name” with the name of your table, “your_file.txt” with the name of your source file, and “Field1” and “Field2” with the names of the fields you want to use to rename the file. Update the file paths and names to match your specific requirements.

Step 3: Run the Code

Save the module by clicking File > Save or by pressing Ctrl + S. Then, click Run > Run Sub/User Form or press F5 to execute the code. The files will be copied and renamed based on the record set.

Troubleshooting Tips and Variations

In this section, we’ll cover some common issues and variations you might encounter when using the CopyFile method with renaming from a record set.

Issue 1: Error 70 – Permission Denied

If you encounter a “Permission Denied” error, ensure that the destination folder has the necessary permissions to write files. You can also try running the MS Access application as an administrator to resolve the issue.

Issue 2: Error 53 – File Not Found

If you encounter a “File Not Found” error, verify that the source file exists and the file path is correct. Also, ensure that the file is not currently in use by another process.

Variation 1: Renaming Files with a Counter

If you need to rename files with a counter, such as “File1.txt”, “File2.txt”, and so on, you can modify the code to include a counter variable. Here’s an example:

Dim counter As Integer
counter = 1

While Not rs.EOF
    ' ...
    newPath = folder & rs!Field1 & "_" & rs!Field2 & "_" & counter & ".txt"
    fso.CopyFile file, newPath
    counter = counter + 1
    rs.MoveNext
Wend

Variation 2: Renaming Files with a Timestamp

If you need to rename files with a timestamp, such as “File_20230220_120000.txt”, you can modify the code to include a timestamp variable. Here’s an example:

Dim timestamp As String
timestamp = Format(Now, "yyyy-mm-dd_hh-mm-ss")

While Not rs.EOF
    ' ...
    newPath = folder & rs!Field1 & "_" & rs!Field2 & "_" & timestamp & ".txt"
    fso.CopyFile file, newPath
    rs.MoveNext
Wend

Conclusion

In this article, we’ve covered the “Property Not Supported” error that occurs when trying to use the CopyFile method in MS Access to copy files to a folder and rename them based on a record set. We’ve also provided a solution using the FileSystemObject and a loop, along with troubleshooting tips and variations to handle common issues and requirements.

By following the instructions and examples provided, you should be able to master the CopyFile method and rename files with ease. Happy coding!

Keyword	Frequency
MS Access	5
CopyFile	4
Rename	3
Record Set	2
Property Not Supported	1

This article is optimized for the keyword “MS Access – Copyfile to folder, rename from record set – property not supported” and is part of a comprehensive guide to mastering MS Access development.

• Related Article: MS Access: Mastering the FileDialog Object
• Related Article: MS Access: Using the FileSystemObject to Read and Write Files

Here are 5 Questions and Answers about “ms access – Copyfile to folder, rename from record set – property not supported” in a creative voice and tone:

Frequently Asked Question

“Get the lowdown on MS Access and copy files to folders like a pro!”