1

I am attempting to create a table within a database which store all of the documents related to the database "in it". What I really want to do is have a file uploaded and have vba code which copies the file to a network location, renames the file by concatenating two fields from the document table form (eliminating the issue of duplicate file names in the external location), and then stores the file name and file path in a file path field in the table. I am very new to access and vba so I am having difficulty getting everything to work. The code I currently have is below:

Option Compare Database

Private Sub Command15_Click()
Dim f As Object

Set f = Application.FileDialog(3)

f.AllowMultiSelect = False

If f.Show Then
    For i = 1 To f.SelectedItems.Count
        sFile = Filename(f.SelectedItems(i), sPath)
        MsgBox sPath & "---" & sFile
    Next
End If

End Sub

Public Function Filename(ByVal strPath As String, sPath) As String
    sPath = Left(strPath, InStrRev(strPath, "\"))
    Filename = Mid(strPath, InStrRev(strPath, "\") + 1)
End Function

I can not seem to get a handle on how to move, rename by concatenating the two fields from the form, or store the path in the path field of the table. I have been to the following locations to obtain what information I could

ms access browse for file and get file name and path

VBA to copy a file from one directory to another

I am currently using Microsoft Access 2010, and I do not wish to use the file attachment field type because of database size constraints. Currently I press a button and a file explorer appears to navigate to the file being uploaded, and the path and file name are entered into strings. After this point I am lost. If any other information is needed please let me know. Thanks in advance for the assistance.

Erik A
  • 31,639
  • 12
  • 42
  • 67
themagicman2136
  • 33
  • 1
  • 1
  • 4
  • 1
    You don't say what the 2 fields on your form are or what sort of data they contain. – Renaud Bompuis Jul 31 '14 at 07:41
  • The two fields will be text. One is an account number with hyphens and the other is the id field of the current document in the documents table which I just have set to be an incremental number. – themagicman2136 Jul 31 '14 at 10:55

1 Answers1

2

I believe your approach to managing the documents is right. In most cases, it doesn't make much sense to store documents in the database itself when the filesystem is a more suited to this job.

What you are doing is fairly straightforward but the main complexity will come from the correct management of the various paths and filenames and extracting the right information from them.

It can become tricky if you're not using some helper functions to to dissect and recompose the various bits of the paths.

I have created a sample database that has a few functions. Might not be exactly in line with what you need but you can easily play around with it to suit your particular case.

The sample database includes a Tools VBA module that has a few useful functions to split a Path into its constituents.

Basically, the database has 2 forms.

The main form allows you to set the network path where the files are to be saved. You can then select a pre-defined Account number (listed in the Account table) associated with a document, then click the upload button.

Main Form

This creates a new record in the Document table and opens a form where you can edit the document title and click a button to upload a file to the server.

Document Upload form

The file selected by the user is copied to the server after its path has been transformed. I took the assumption that the file would keep its original extension, the filename would be renamed to the ID of the Document record where the file information is saved (like 5845.pdf) and that the folder where the file is saved on the server would be the account number, so that a source file selected by the user 

C:\Users\user\Desktop\SuperSecretFile.pdf

would be saved as, for instance:

\\docserver\files\123-55547\5845.pdf

The Main form also allows you to update an existing record, open the file from the server, open the server's folder where the file is located or even copy the server file back to the user's computer with the original name of the file.

I'll let you play around with it. Let me know if you have any issues.

Renaud Bompuis
  • 16,596
  • 4
  • 56
  • 86
  • I do have one additional issue. I am integrating this with another database. My goal is to have a button on the form of my current database which opens the documents table. The account number in the Documents Form should pull from the account number field that is currently loaded on the main database form. I tried setting the default value to something along the lines of [Forms]![DatabaseMainForm]![Account #], but I can't seem to get it to work. The reason I need to do it this way is there are 2,000+ records. Everything else is perfect, I just can't figure out how to do this minor change – themagicman2136 Aug 04 '14 at 14:12
  • I have been able to implement this into my database. It works incredibly well. I was wondering if there is a way to remove a record if someone clicks upload file and then decides to not upload a file. Do you have any suggestions. Right now it will show up in the existing docs section with no file if that is done. – themagicman2136 Sep 09 '14 at 19:11
  • Actually I just added a criteria to the query for the list on the main form which eliminates any records that the destination path is null. Thanks again for all of your assistance. – themagicman2136 Sep 09 '14 at 19:33