Need to extract attachments to a folder and link them back to DB

[Modify_inc]

Hello all,



Using Access 2010, I have reached the 2GB limit. It's not the data, it's the PDF files I scanned in for each customer that has stretched the db to its limits. The db is also currently split.

I didn't realize Office had a 2GB limit; I assumed if you had a 64 bit computer and a NTFS partition, there would be no 2GB limit.

Using a template database in Access 2010, I need to extract the attachments to a folder and thus link them back to the database using hyperlinks. I can handle this task, but I'm not confident how to extract the attachments from each record (around 900) to a folder and have it do it automatically for all records. I can do it record by record, but that is not feasible with this many records.

Any suggestions on how to extract all the attachments for each record, and have them saved in folders by their name or other ID, so I can link them back to the db via a hyperlink?

Thanks
Mike

[June7]

Review

http://blogs.office.com/b/microsoft-...cess-2007.aspx

http://allenbrowne.com/casu-09.html

http://allenbrowne.com/func-GoHyperlink.html

Can use VBA code to construct the complete hyperlink and save to hyperlink field. Or don't use hyperlink field and just use Allen's code to build the hyperlink 'on the fly'.

[Modify_inc]

I came across this code that will work acceptably for what I need, but I'm not sure how to apply it to my form.

Private Sub cmdExplore_Click()
On Error GoTo Err_cmdExplore_Click

Dim stAppName As String

stAppName = "C:\Windows\explorer.exe d:\clients" & Me.txtContactName & "\"
Call Shell(stAppName, 1)

Exit_cmdExplore_Click:
Exit Sub

Err_cmdExplore_Click:
MsgBox Err.Description
Resume Exit_cmdExplore_Click

End Sub

I created a module in VBA using the above code and it worked when I tested it, although I had to remove the following: & Me.txtContactName & "\"
This part of the code is important, because it should open the folder that the txtContactName refers too, at least that's what I was implied to believe.

With that said, I still do not understand how to run the module when I click on the control button.

I thought I could copy the code into the 'On Click' field under 'Zoom' for the control button, but that fails with an error.


Thanks
Mike

[June7]

Why would you remove that reference to textbox on form?

Open the button Properties dialog. In the Click event select [Event Procedure]. Click the ellipses (...). This will take youi to the procedure in VBA editor.

[Modify_inc]

Why would you remove that reference to textbox on form?

Open the button Properties dialog. In the Click event select [Event Procedure]. Click the ellipses (...). This will take youi to the procedure in VBA editor.

I'm not sure what you're referring too, when you ask why I would remove the reference to the textbox on the form.

Following your advice, I was able to finally get explorer to open, although it opens My Documents folder on my system drive rather than the D:\Clients folder I specified in the code.

This is the code in VB after following your directions:

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)

Exit_cmdExplore_Click:
Exit Sub

Err_cmdExplore_Click:
MsgBox Err.Description
Resume Exit_cmdExplore_Click

End Sub

Any ideas why that is so?

Thank you

[June7]

Do you have a field or textbox on form called txtContactName? Show an example of exact path structure of a file you want to open.

[Modify_inc]

Do you have a field or textbox on form called txtContactName? Show an example of exact path structure of a file you want to open.

I do have a textbox named txtContactName. Not sure if it's important, but I have the Control Source set to display the full name in the textbox, txtContactName. This is the code for it that I use:

=IIf(IsNull([ID]),"Full Name",[ContactName])

The path(s) are to open folders only, not files:

D:\Clients\Contact Name (Contact Name is identical to the name found in the txtContactName field)\

Example:
D:\Clients\Mike A. Tompson\

[June7]

Looks like your code is missing a backslash, unless that character is stored with value in field:

stAppName = "C:\Windows\explorer.exe D:\Clients\" & Me.txtContactName & "\"

The textbox will display the literal text "Full Name" if the ID field is null.

Might want conditional statement to handle situation where folder is not found:

If Dir("D:\Clients\" & Me.txtContactName & "\") = "" Then
MsgBox "Folder not found."
Else
'code to open folder
End If

[Modify_inc]

Looks like your code is missing a backslash, unless that character is stored with value in field:

stAppName = "C:\Windows\explorer.exe D:\Clients\" & Me.txtContactName & "\"

The textbox will display the literal text "Full Name" if the ID field is null.

Might want conditional statement to handle situation where folder is not found:

If Dir("D:\Clients\" & Me.txtContactName & "\") = "" Then
MsgBox "Folder not found."
Else
'code to open folder
End If

It's working now!

It was the backslash you found and a extra space after the initial in the name that was causing it to fail. I realized that just now when I tried testing it with another client with only the first and last name. I had created the test folders myself and didn't notice there were two spaces after the middle initial period.

My next objective is to create the folders automatically for each contact, based on the txtContactName. I am thinking the above code can be altered to achieve this maybe? Your thoughts...

Thanks again for you help and the extra code, much appreciated!

Mike

[June7]

Google: VBA create folder
http://www.ozgrid.com/forum/showthread.php?t=32259

[Modify_inc]

Google: VBA create folder
http://www.ozgrid.com/forum/showthread.php?t=32259

Thanks for the link!

I'll be honest, most of that is above my experience.

What would you recommend I try to master first to accomplish this task, VBA or macros? 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.

Thanks again
Mike

[June7]

I don't use macros, with one exception - an AutoExec macro. I learned VBA first because that's what we needed in project and I had a tutor that said to stay away from macros. Now I find macros more confusing and harder to debug and only deal with them when helping posters in forum.

AFAIK, a macro could not accomplish the requirement you described.

Unless building a web database, I recommend VBA.

What do you not understand about the MkDir example?

[Modify_inc]

I don't use macros, with one exception - an AutoExec macro. I learned VBA first because that's what we needed in project and I had a tutor that said to stay away from macros. Now I find macros more confusing and harder to debug and only deal with them when helping posters in forum.

AFAIK, a macro could not accomplish the requirement you described.

Unless building a web database, I recommend VBA.

What do you not understand about the MkDir example?

Thanks for sharing that with me, it has helped me to decide which route to pursue.

Regarding the MkDir example. The OP mentioned using macros to create a folder, which doesn't seem feasible as you said for this task. Also the folder he wants to create is a static folder and not folders for each client as I am needing. I assume I could add the code

 & Me.txtContactName & "\"

somehow to possibly resolve this, although I really do not know.

Looking at the code posted by Jack, is honestly, beyond my knowledge of VB. There is quite a bit of code, and I am uncertain of what I would need to alter to use it in my DB.

Also there is mention of using UDF to check whether the folder already exists. I understand UDF when referring to Universal Disk Format, but I do not understand it in their context.

Thanks again!
Mike

[June7]

Sorry, that link is in an Excel forum.

Macros in Excel are really VBA code. Macros in Access are different animal.

So the example provided still applicable to Access/VBA. Yes, use variable instead of literal value. Reference to form control is a variable, same as you have used in the other code.

UDF means user defined function - a VBA procedure. Don't really need UDF. I already showed you code to check if directory (folder) exists.

You need an understanding of basic programming concepts as well as VBA vocabulary and syntax.