Creating a Document Database using Microsoft Access

I am attempting to create a table within a database which stores documents.

How do I upload the file to a network location, rename the file by concatenating two fields from the document table form (eliminating the issue of duplicate file names in the external location), and store the file name and file path in a file path field in the table?

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

ms access browse for file and get file name and path

VBA to copy a file from one directory to another

I am using Microsoft Access 2010, and I do not wish to use the file attachment field type because of database size constraints. 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.

One is an account number with hyphens and the other is the id field of the current document in the documents table which I have just set to an incremental number.

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.

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.

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.

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.