Create folder if folder does not exist for client

[Modify_inc]

Hey everyone!

I'm pretty new to using Access 2010, and have created a workable database for tracking my client records. Only problem is I didn't realize that using the attachment field for my PDFs for each client would result in me reaching the 2GB limit of Access. Minor over-site, I know. I'm trying to transfer the files from each record (client) to a folder on my server, relative to the client's name.

Currently I'm using this code listed below to open the folder of the client I'm viewing in the Form and thus allowing me to view the PDFs associated with that client.
I have over 900 clients that I need to create these folders for, and currently I just created three client folders manually for testing with the below code. This works, but my objective is to create the folders automatically from the Form, only if the client folder does not already exist, and if possible, transfer the attached files to the folder when it is created for the client. If the latter complicates things, I can manage to transfer the files to each folder manually over time. I just need to free some space in my DB so I can continue to use it.

Here is the code I'm using at the moment, (not mine, found on the net, although altered for my DB):

Private Sub Command1043_Click()

On Error GoTo Err_cmdExplore_Click

Dim stAppName As String

stAppName = "C:\Windows\explorer.exe D:\Clients\" & Me.txtContactName & "\"
Call Shell(stAppName, 1)
If Dir("D:\Clients\" & Me.txtContactName & "\") = "" Then
MsgBox "Folder not found."
Else
Exit_cmdExplore_Click:
Exit Sub

Err_cmdExplore_Click:
MsgBox Err.Description
Resume Exit_cmdExplore_Click

End If

End Sub

I assume this task to be somewhat in depth or complicated to some extent. If someone knowledgeable in this area can provide me a starting point to solving this on my on, I would be grateful either way.

I truly want to learn how to code or at minimum learn how to do some of these tasks without requiring so much assistance from others. I understand macros are suppose to be easier than VBA and that it uses a subset of VBA code. But when I open the macro editor in Access 2010, I'm lost.

Sincerely
Mike

 

[ChrisO]

You do not have to be concerned if the folder already exists for the client.

What you do need to be concerned with is if Me.txtContactName is unique and if it complies with folder naming restrictions.

Please verify each concern and we can proceed.

Chris.

 

[CJ_London]

To make a folder you use

If Dir("D:\Clients\" & Me.txtContactName & "\") = "" Then
MkDir("D:\Clients\" & Me.txtContactName)

However as Chriso says - ensure you avoid duplicate txtContactName and meet naming conventions

I have a similar requirement and I would actually call the folder (in your parlance)
txtContactName & "_" & txtContactID to avoid the duplicates - this also means you can find the folder if the txtContactName changes by looking up against ID

I actually go one further - I have one folder called DocStore and I rename all files as CustomerID_DocType_DocName and users 'browse' by using Access functionality

To copy files you use FileCopy

FileCopy OldPath & OldFileName, NewPath & NewFileName

and to delete from old location use

Kill OldPath & OldFileName

 

[ChrisO]

There is no need to check for the existence of a Folder before attempting to create it.

Chris.

 

[CJ_London]

Sorry - don't agree, if the path already exists you will get an error - 75 Path/File Access error

an alternative is

On Error Resume Next
MkDir....

 

[ChrisO]

It depends on how we do it:-

http://allapi.mentalis.org/apilist/MakeSureDirectoryPathExists.shtml

And:-

Overloading the MkDir Statement

Chris.

 

[CJ_London]

Hmm - not come across that API before, but have seen similar functions

thanks for the heads up

 

[ChrisO]

>>Hmm - not come across that API before, but have seen similar functions<<

It is ambiguous; seen similar functions to the API call or the one I wrote based on it?

Post a link please.

Chris.

 

[CJ_London]

I meant acheive the same objective

Here is a link to something similar (which doesn't use API's)

 

[ChrisO]

Post #5
>>Sorry - don't agree, if the path already exists you will get an error - 75 Path/File Access error<<

Post #7
>>Hmm - not come across that API before, but have seen similar functions<<

Post #9
>>Here is a link to something similar (which doesn't use API's)<<

The lack of attention to detail in a post only makes more work for other people.

What link in post #9?

Chris.

 

[CJ_London]

It was late

http://www.freevbcode.com/ShowCode.asp?ID=257

Looking forward to your detailed response to help the poster

You do not have to be concerned if the folder already exists for the client.

 

[Modify_inc]

You do not have to be concerned if the folder already exists for the client.

What you do need to be concerned with is if Me.txtContactName is unique and if it complies with folder naming restrictions.

Please verify each concern and we can proceed.

Chris.

It's not near as unique as the account number I have for each client, but I didn't think it wise to create folders using numbers. I like the fact that if I needed to search for a client manually I could find it much quicker by name than trying to reference the account number to a name. I assume though I could append the account number to the folder name maybe?

The names should comply with the naming restrictions, there just first middle and last name with a period after the middle initial following with two spaces then last name.
ex: Mike A. Tompson
Some only have first and last name with one space, like so: Mike Tompson

Account numbers are mostly 8 digits sometimes with a hypen at the end followed by a number
ex: 12345678-2 or 12345678

Some clients are duplicate, meaning same name, but different account numbers. They are the same person, just with a different account number. I would be fine with putting the PDFs in one folder for duplicate names or in separate folders for each duplicate name, whatever is deem the easiest to achieve. I have no idea, as most of this is beyond my experience.

Hope that helps, and I want to thank you again for offering to assist me with this task.

Mike

 

[ChrisO]

First let’s look at creating the directories…

Sub Test is only a representation of the data we will need to get from your Table(s); it is only for test at the moment.
You may already have a Query which produces that data.

I used the C drive because I don’t have a D drive.

Option Explicit
Option Compare Text


Public Declare Function apiCreatePath Lib "Imagehlp.dll" _
                 Alias "MakeSureDirectoryPathExists" (ByVal strPath As String) As Long
'


Sub Test()

    MakeFolder "Mike Tompson"
    MakeFolder "Mike Tompson", "12345678-2"
    MakeFolder "Mike Tompson", "12345678"
    MakeFolder "Mike A. Tompson"
    MakeFolder "Mike A. Tompson", "1"
    MakeFolder "Mike A. Tompson", "2"

End Sub


Public Sub MakeFolder(ByVal vntName As Variant, _
             Optional ByVal vntAcc As Variant = Null)

    Dim strPath As String
    
    Const conRoot As String = "C:\Clients\"
    
    If Len(vntName) Then
        strPath = conRoot & Trim(vntName) & "\"
        
        If Len(vntAcc) Then
            strPath = strPath & Trim(vntAcc) & "\"
        End If
        
        apiCreatePath strPath
    End If

End Sub

Once we have the directories setup the way you prefer we can look at extracting the files.
I have no experience with attachment fields so we may need to get further help with that side of things.

Chris.

 

[Modify_inc]

First let’s look at creating the directories…

Sub Test is only a representation of the data we will need to get from your Table(s); it is only for test at the moment.
You may already have a Query which produces that data.

I used the C drive because I don’t have a D drive.

Option Explicit
Option Compare Text


Public Declare Function apiCreatePath Lib "Imagehlp.dll" _
                 Alias "MakeSureDirectoryPathExists" (ByVal strPath As String) As Long
'


Sub Test()

    MakeFolder "Mike Tompson"
    MakeFolder "Mike Tompson", "12345678-2"
    MakeFolder "Mike Tompson", "12345678"
    MakeFolder "Mike A. Tompson"
    MakeFolder "Mike A. Tompson", "1"
    MakeFolder "Mike A. Tompson", "2"

End Sub


Public Sub MakeFolder(ByVal vntName As Variant, _
             Optional ByVal vntAcc As Variant = Null)

    Dim strPath As String
    
    Const conRoot As String = "C:\Clients\"
    
    If Len(vntName) Then
        strPath = conRoot & Trim(vntName) & "\"
        
        If Len(vntAcc) Then
            strPath = strPath & Trim(vntAcc) & "\"
        End If
        
        apiCreatePath strPath
    End If

End Sub

Once we have the directories setup the way you prefer we can look at extracting the files.
I have no experience with attachment fields so we may need to get further help with that side of things.

Chris.

Wow, much more than I was expecting! I can't wait to test it.

On a positive note, I forgot that I had scanned in and saved most of the client PDFs along time ago, and had created a folder for each client. The only problem is the naming convention I used was at most, casual. I quickly entered names and so many of the client folder names that I had created, do not match entirely with my naming convention I'm currently using from the txtContactName field.

I have no idea from a coding standpoint, if what I mentioned makes things easier, but I thought I would share it with you.

The attachments at this time are not that much of a concern, though it would be nice to save all of them to their respected client folder. Access only allows you to copy one attachment at a time from each record. And to top that, it ask every time if you want to rewrite over the existing PDF file that is already in the client folder. It has no selection for yes to all, and this is time consuming when you have 50+ files for some of the record. This goes for delete also, so after I get them copied to the folder, I have to delete each individual file from the record.

Anyhow I will try to test the code this evening when I get some free time and give you some feedback.

Thank you again!

Mike

 

[Modify_inc]

I just had a moment to try it and I'm not sure if I did something wrong, but after making slight modifications, such as changing C drive to D drive, I get the message box:
Enter a Parameter Value ID

Not sure what that means, but it's not the first time I've seen it when trying to use VBA code. I assume there's something it doesn't like about the code maybe? It pops up no matter if I click the button that activates the procedure or not. If I close out of the Main form and open it back up, it will also immediately display the above message.

Anyhow, I'm sure you familiar with it; I'm just trying to provide as much detail as possible to assist you.

Mike

 

[ChrisO]

The code supplied is designed for testing only at this stage and I didn’t want any complications.

Copy and paste the code into a new standard module. Remove the code from anywhere else you put it.

To run the code, place the cursor in Sub Test and press F5.

Chris.

 

[Modify_inc]

The code supplied is designed for testing only at this stage and I didn’t want any complications.

Copy and paste the code into a new standard module. Remove the code from anywhere else you put it.

To run the code, place the cursor in Sub Test and press F5.

Chris.

Sorry rookie mistake. I removed it, and even removed the button that referenced the code, but the form still produces the message box, Enter a Parameter Value when I open the Main form. I noticed the code is still in the VBA Editor and have no idea how to completely remove it for good as it seems to always come back when I reopen the VBA Editor. I can quickly restore from a backup if need be.

I went ahead as you said and created a new module and pasted the code you provided with the cursor in the Sub Test, and hit F5.

Compile error:
Only comments may appear after End Sub, End Function, or End Property

UPDATE: Actually I think that was a direct result of the other code. I finally was able to remove it completely. Apparently you just clear the code from the window and save. I redid the steps, and no error message or anything is displayed when I hit F5. It is almost like it is not doing anything, is this the correct behavior you're looking for? I have a MakeFolder and Test in the Procedure list. Hope that helps!

UPDATE 2: It did create two folders with the name, Mike Tompson and Mike A. Tompson, but the account number was not appended to the folder name. The account numbers became the sub-folder names inside the parent folder, Mike Tompson. Mike A. Tompson had two sub-folders named 1 and 2.

 

[ChrisO]

Okay, I can’t fix your database so do a restore.

Then download and convert the attachment and we can work without the other complications.

Chris.

 

[Modify_inc]

Okay, I can’t fix your database so do a restore.

Then download and convert the attachment and we can work without the other complications.

Chris.

I had edited my last reply to provide you with updates but I don't think you got them. I was able to resolve the message box without restoring, and the test code did create folders.

 

[ChrisO]

Okay, we have progress; that is what it should have done.

Now make very sure of the directory structure you will require.

I did it that way so that, in future, only one unique PDF document will go into one account folder.
The unique account folder(s) should be under the unique client name. Also consider joint account names because the account number may be more important than the clients in that account.
At the moment, one unique client with (possibly) many unique account numbers each of which (possibly) contains many unique PDF files.

In other words, we can now create directories but we must first look at the table structure. The directories should be exactly the same as the table structure unless the full path to the unique document is stored in each record.

The reason for all that is that if we can run a Query to find the Name of a unique PDF document for a client in a Table then the same Query can be used to locate the same unique File on disk.

So, to me, the initial question has been answered.
The next question is what directory structure should be created.
The answer to that depends on Table structure.
The answer to that is a separate question altogether and should be asked in a different forum.

Chris.