How to create a sub folder? (1 Viewer)

[deletedT]

In short:
In a Access FE with a MS SQL BE, I need to create a folder and several sub folders on a drive. I'm looking for the shortest possible way.


Details: A database that is used to control a production line, needs to save a file in a specified path. Later, these files will be used by mechanical machines (Lathe, etc) to manufacture parts & components.

1. The path will be decided by a sub when a user clicks a button in a form.
   This path would be something like :
   G:\shop1\Lath2\Nc\FDE\Part1\Section1\Tera\data.nc
2. This path would be destroyed(deleted) after the machines have done their job. So each time they are different and should be created.
3. At present I split the path and read each sub folder and create them one by one with FSO.CreateFolder
4. In some cases, because of the structure of the machines or their OS, Production situation, Person in charge or a lot more conditions, the path may be much longer.

Now my question:
Instead of creating each sub folder one by one, is there any other way to create all of the path in one go? for example something like this:

TargetPath="G:\shop1\Lath2\Nc\FDE\Part1\Section1\Tera\data.nc"
FSO.CreateFolder(TargetPath)

And all the necessary folders have been created.

I don't care if it's not a FSO or anything else. Whatever that gives me the result can be a solution.

thank you.

 

[theDBguy]

Hi. Not sure if there’s a built-in one but you can certainly create it yourself. I think I have seen one before. Try searching for CreateFolderStructure.

 

[deletedT]

Hi. Not sure if there’s a built-in one but you can certainly create it yourself. I think I have seen one before. Try searching for CreateFolderStructure.

I'll try to see if I can find anything.

Thank you.

 

[Gasman]

The mkdir command will do it.
Perhaps use Shell command.?

In short:
In a Access FE with a MS SQL BE, I need to create a folder and several sub folders on a drive. I'm looking for the shortest possible way.


Details: A database that is used to control a production line, needs to save a file in a specified path. Later, these files will be used by mechanical machines (Lathe, etc) to manufacture parts & components.

1. The path will be decided by a sub when a user clicks a button in a form.
   This path would be something like :
   G:\shop1\Lath2\Nc\FDE\Part1\Section1\Tera\data.nc
2. This path would be destroyed(deleted) after the machines have done their job. So each time they are different and should be created.
3. At present I split the path and read each sub folder and create them one by one with FSO.CreateFolder
4. In some cases, because of the structure of the machines or their OS, Production situation, Person in charge or a lot more conditions, the path may be much longer.

Now my question:
Instead of creating each sub folder one by one, is there any other way to create all of the path in one go? for example something like this:

TargetPath="G:\shop1\Lath2\Nc\FDE\Part1\Section1\Tera\data.nc"
FSO.CreateFolder(TargetPath)

And all the necessary folders have been created.

I don't care if it's not a FSO or anything else. Whatever that gives me the result can be a solution.

thank you.

 

[isladogs]

I got this from Daniel Pineault's site some time ago & have just tested it on your exact folder structure. It works
The URL is https://www.devhut.net/2011/09/15/vba-create-directory-structurecreate-multiple-directories/

Public Sub MyMkDir(sPath As String)
    Dim iStart          As Integer
    Dim aDirs           As Variant
    Dim sCurDir         As String
    Dim i               As Integer
 
    If sPath <> "" Then
        aDirs = Split(sPath, "\")
        If Left(sPath, 2) = "\\" Then
            iStart = 3
        Else
            iStart = 1
        End If
 
        sCurDir = Left(sPath, InStr(iStart, sPath, "\"))
 
        For i = iStart To UBound(aDirs)
            sCurDir = sCurDir & aDirs(i) & "\"
            If Dir(sCurDir, vbDirectory) = vbNullString Then
                MkDir sCurDir
            End If
        Next i
    End If
End Sub

 

[deletedT]

It seems that this one works:

Shell "cmd.exe /c mkdir " & TargetPath, vbHide

I tested it using a remote control from my phone to my office PC. And it did the magic.
And since the drive is a NAS, creating directories one by one took a long time. But mkdir, was a F1 car.
Of course, I need to check the time more carefully and do some more tests tomorrow as soon as I'm back to work.

Unfortunately I couldn't find anything about CreateFolderStructure.
But maybe it's because I hate small screen of smart phones and stopped searching.
Tomorrow I'll see if I can find anything on this too.

Guys, You are great.

 

[deletedT]

Colin,
Sorry. I marked it as solved before seeing your reply.
I'll check it tomorrow morning.

I appreciate your time for searching.

 

[isladogs]

No problem. I already had the code but thought it useful to confirm the link

EDIT:
Just tried the code you posted. That works well and is much more concise

 

[deletedT]

As a report of the result, I had to switch back to create folders one by one.
Using the shell command was fast. But it seems it was faster than what Access expects.

If I run the code one line at a time, everything is OK and the result is as expected. But when I run the code normally, I receive an error that the folder doesn't exists. Even though when I go to the specified path in windows explorer, the path exists. To my surprise, when I receive the folder doesn't exists, If I hit F5 to continue run, without doing anything, it works.

It shows Access needs a pause time to recognize the folder is actually on the disk.
If I put a 6 second wait after shell command, it works.

If any one is interested this is the test code I used:

Public Function CopyThisFile(Source As String, _
                         Target As String, _
                         FileName As String, _
                         Optional OverWrite As Boolean = True) As Boolean
    
    
    Dim FSO As New FileSystemObject
     
    Source = Source & FileName
    CopyThisFile = False   
    
    If Not FSO.FolderExists(Target) Then
        Shell "cmd.exe /c mkdir " & Target, vbHide
    End If
       
    ' Double check Target folder
    If Not FSO.FolderExists(Target) Then
        CopyThisFile = False
        Exit Function
    End If

    ' double check source folder
    If Not FSO.FileExists(Source) Then
        CopyThisFile = False
        Exit Function
    End If
    
    Target = Target & FileName
    ' Everything is OK. Copy
    FSO.CopyFile Source, Target, OverWrite
    CopyThisFile = True
    
End Function

 

[essaytee]

In one of my applications users upload (attach) files to relevant records. I only use what is provided by Access, Dir(), MkDir() and FileCopy() and have never come across a timing issue. I don't even use the vbScript FileSystemObject method.

To answer your original question, yes, I create the sub-directories, one by one and I'm not aware of an automatic create them all at the same time, other than rolling your own as shown a few posts up.

 

[isladogs]

Hi Tera

I hadn't previously tested copying a file after using your create subfolder code. Having now done so, I agree it doesn't work without a delay.

However, did you try using the code I provided in post #5?
That definitely allows you to copy a file into the newly created folder structure WITH NO DELAY needed. I have used it for that purpose for years!

Try that code together with something like:

Sub TestMakeSubfolderCopyFile()

    Dim Source As String, Target As String, Filename As String, FSO As Object

    'modify next 3 lines as appropriate
    Target = "g:\abc\def\ghi\xyz\" 
    Source = "C:\Test\"
    Filename = "passwordinfo.txt"
    
    MyMkDir Target
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.CopyFile Source & Filename, Target
    
    'if next line returns >0, file exists
    Debug.Print Len(Target & Filename)

    Set FSO = Nothing
    
End Sub

Obviously some error handing including testing for trailing '\' is needed ....but I know it works
Hope that helps

 

[deletedT]

However, did you try using the code I provided in post #5?

I was very busy with my job this morning. So I didn't test that code.
I'm now working on it.

I'll report back as soon as I can judge if it works for me.

Thanks for your help.

 

[deletedT]

In one of my applications users upload (attach) files to relevant records. I only use what is provided by Access, Dir(), MkDir() and FileCopy() and have never come across a timing issue. I don't even use the vbScript FileSystemObject method.

To answer your original question, yes, I create the sub-directories, one by one and I'm not aware of an automatic create them all at the same time, other than rolling your own as shown a few posts up.

As I mentioned above the target is a NAS drive. I don't know if you've been working with them or not, but apparently a network drive is much slower than a local one. If I have to create 8 nested folders and add them one by one and each folder takes 0.5 to 1 second, that's a total of 4 to 8 seconds. And when the personnel of a production line need to send files to the machines every half an hour or so, the loss of time is a problem. It makes them nervous watching the cursor goes hourglass. I received complains to check the code and if possible find a faster way.

Shell command proved to be very fast. maybe in a blink of an eye. and was able to make the directories in one go.
But Access couldn't recognize the folder are on drive and seems to ask for a time span. Maybe it's the difference between Access' mkDir() and shell's command. I don't know.

 

[isladogs]

Just one more thing.
Using the Sleep API I found that a delay of 150 ms (milliseconds) was sufficient for your original code to work.

'Sleep API declaration
Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

Sub MakeFolderCopyFile()

Dim TargetPath As String, SourcePath As String, FileName As String, FSO As Object
TargetPath = "G:\shop1\Lath2\Nc\FDE\Part1\Section1\Tera\data.nc\"
SourcePath = "C:\Test\"
FileName = "123.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

Shell "cmd.exe /c mkdir " & TargetPath, vbHide
Sleep 150

FSO.CopyFile SourcePath & FileName, TargetPath

Debug.Print Dir(TargetPath & FileName)

Set FSO = Nothing

End Sub

That delay was sufficient both for a local drive and a very old slow portable hard drive
Using a memory stick a delay of 250 ms worked.
I don't have a NAS drive to test against.

 

[deletedT]

Just one more thing.
Using the Sleep API I found that a delay of 150 ms (milliseconds) was sufficient for your original code to work.

Thanks for testing and let me know the result.
But I can't take the risk and use some code that can't be relied on. It may work with a 150ms now, but with the load of the network and maybe other circumstances, it may need a little more or less. At the peak load of network, if a user needs to send a file to a machine, and the machine can't find it in the specified location just because Access needs a delay, I'd be in trouble. The whole line may come down. The responsibility and the risk is too much for me.

At present I'm using the old loop and creating each folder one by one. In my situation,4 seconds delay is much better than never.
I'm working and testing your #5 post with two other colleagues and may use it depending the results.

I'll let you know. We are planning to test drive it Tomorrow (Sunday) when the line is down in real situation to see the result.

Thanks again.

 

[Gasman]

Another way might be to create folders in turn, but still use mkdir due to the speed.?

https://www.pcreview.co.uk/threads/mkdir-and-unc-path.1633276/

 

[isladogs]

Hi gasman
That's exactly what the code in post #5 does

 

[essaytee]

As I mentioned above the target is a NAS drive. I don't know if you've been working with them or not, but apparently a network drive is much slower than a local one.

Yes, this is why I chimed in, my app creates folders and sub-folders on a network drive and we all reckon our network is slow. Strange, I haven't had a timing problem copying files to folders and sub-folders created just prior by Access. I know there are no timing issues as I have a logging routine to keep track of this stuff, never a File Copying problem.

This is something for me to keep in mind, just in case this problem creeps in.

 

[deletedT]

......my app creates folders and sub-folders on a network drive and we all reckon our network is slow. Strange, I haven't had a timing problem copying files to folders and sub-folders created just prior by Access. I know there are no timing issues as I have a logging routine to keep track of this stuff, never a File Copying problem.

By network drive do you mean a shared folder or shared drive from another PC and server?
Or do you mean a NAS?

 

[deletedT]

Hi gasman
That's exactly what the code in post #5 does

And that's exactly why I deceided to give it a try. If Shell's mkDir is that fast, maybe Access' mkDir can be the answer to my problem.