vba 使用 Microsoft Access 创建文档数据库

Creating a Document Database using Microsoft Access

提问by themagicman2136

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:

我试图在数据库中创建一个表，该表存储与数据库“在其中”相关的所有文档。我真正想要做的是上传一个文件并拥有将文件复制到网络位置的 vba 代码，通过连接文档表表单中的两个字段来重命名文件（消除外部位置重复文件名的问题），然后将文件名和文件路径存储在表中的文件路径字段中。我对访问和 vba 很陌生，所以我很难让一切正常工作。我目前拥有的代码如下：

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

ms access 浏览文件并获取文件名和路径

VBA to copy a file from one directory to another

VBA将文件从一个目录复制到另一个目录

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.

我目前使用的是 Microsoft Access 2010，但由于数据库大小限制，我不希望使用文件附件字段类型。目前我按下一个按钮，一个文件浏览器出现来导航到正在上传的文件，路径和文件名被输入到字符串中。在这一点之后，我迷路了。如果需要任何其他信息，请告诉我。预先感谢您的帮助。

采纳答案by Renaud Bompuis

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 databasethat 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 ToolsVBA module that has a few useful functions to split a Path into its constituents.

示例数据库包含一个ToolsVBA 模块，该模块具有一些有用的函数来将路径拆分为其组成部分。

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 Accounttable) associated with a document, then click the upload button.

主窗体允许您设置要保存文件的网络路径。然后，您可以选择Account与文档关联的预定义帐号（在表中列出），然后单击上传按钮。

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

这将在Document表中创建一个新记录并打开一个表单，您可以在其中编辑文档标题并单击一个按钮将文件上传到服务器。

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 IDof the Documentrecord 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

用户选择的文件在其路径转换后被复制到服务器。我假设文件将保留其原始扩展名，文件名将被重命名为保存文件信息ID的Document记录（如5845.pdf），并且文件保存在服务器上的文件夹将是帐号，以便用户选择的源文件

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

would be saved as, for instance:

将被保存为，例如：

\docserver\files3-5554745.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.

Main 窗体还允许您更新现有记录、从服务器打开文件、打开文件所在的服务器文件夹，甚至将服务器文件以文件的原始名称复制回用户的计算机。

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

我会让你玩弄它。如果您有任何问题，请告诉我。