Download Sample Access File:
Store and Search documents
(Version 2010 +)
MS Access has a built-in function to attach an external file or image to database; however, it will increase a file size quickly when more files are attached. It might be okay to use this function for a small database. Those attached files also cannot be searched by user.
The efficient way is to store only the file name, file description, and file location in database. Then we can use a function to search for those files/documents and open those files. The actual files/documents should be stored outside of MS Access file. So this sample MS Access file is created for.
This download file contains two MS Access Files:
- Store and Search Documents
- Store and Search Documents V2
1.Store and Search Documents
Forms and Highlight functions in this file include:
#1 Add New Document Form:
We use this form to add file/document to database. It will open with a blank form. This form will copy a file to a specified location (example: C:\) and will add file name with its new location to database. In this database, I set the document location in the GlobalConst Module as Public Const MainFolder As String = “C:\TestDatabase”. So you can simply change to your new document location on this GlobalConst module.
The Document Type is needed before making a copy file to new location and adding file name to database. If you click the “Add Doc” button without selecting the Doc Type then you will get an error message shown below:
After selecting the Doc Type and clicking the “Add Doc” button, the explorer window will open. You can browse to your document, select it and click Open button to add file name and its file path to database and copy to new to location.
You can also add more description of your document on the Doc Description field. This field will be also included in the Search function.
From the picture above, the boy.JPG file was select for Photo doc type. The boy.JPG photo file is copied to a Photo folder (Doc Type) in the specified location (C:\TestDatabase). The new file is saved in this folder with a new name: 14_boy.JPG. A number 14 comes from DocID field and placed in front the file name. So each file in a folder will not be duplicated.
You can add the URL address to this database without copying file/document to database. This feature allows us to store a document description in database and link to document online. When you click at the link it will open the website.
You can open the document folder to see all documents in the selected folder on this form by clicking on “Open Folder” button after the Doc Type is selected.
#2 Search Document Form:
This form is a core feature of this database. It includes a search by multiple keyword function, a search by Doc Type, open a document and delete a document. In this sample file, it is programmed to search in three different fields: FileName, Doc Description, and Doc Link.
The information is needed in the Enter Keyword box or the Doc Type dropdown box before searching. If you click the Search icon without entering the keyword or selecting the Doc Type, you will get an error message shown below.
Example of Searching 3 keywords:
You can search more than one keyword by entering a space between words. The search function will use a loop searching for each keyword on all three fields (FileName, DocDescription, and DocLink). There are 4 results from searching for 3 keywords of “logo family boy.”
Example of Searching keywords and Doc Type together:
Per example above, now I select “Letter” for the Doc Type. There is only one record contains the keyword “family” in the “Letter” Doc Type.
Example of Searching Doc Type only:
You can also search only on the Doc Type without entering any keyword. After selecting the Doc Type, the result will display on the list below.
Open Link:
You can click a link of the number in front of the File Name to open a Document Form to view more information about the file/document as shown below.
Open or Delete document:
This search form also provides the “Open Doc” or “Delete” button to view or delete the document of each record.
Admin Page:
This database comes with an User table and UserType table. The user login is a computer login name. For example, I have a computer login name as “Tewan” in my computer, so I enter “Tewan” under the UserLogin field. The Admin Page/Tab will display only for the user who has the UserType as Admin. Under the Admin tab, you can add or edit the users and document types.
Warning before deleting document:
After you click the Delete button, you will get a pop up warning asking for a confirmation before deleting. The default button is set to “No” on the pop-up message to prevent the accident deleting.
Regular User:
The Admin Page/tab will be invisible when the users log in with the user type as User. So the regular user will not be allowed to add or edit the user and the document type.
The user who logs in with the “User” user type will not be allowed to delete any document. The Access
Denied message will pop up after clicking the “Delete” button on the Search Form and on the Document Form shown below.
2.Store and Search Documents V2
This MS Access File V2 has all features same as version 1 above except adding a Search Type dropdown box as a part of search function. There are two types of the Search Type: “Contains” and “Exact keyword”. These search types will make a difference in the search result when there are two keywords or more. The “Contains” Search Type will display when this Search Form is loaded.
Example of one keyword with “Contains” search type:
There is no different result from searching one keyword with the “Contains” search type. For example, we enter a “letter” keyword for searching with the “Contains” search type. There will be three records as a result.
Example of two keywords with “Contains” search type:
The search function will be looking for “letter” and “to” keywords separately. So there are five records when searching for the “letter to” keywords using the “Contains” search type as shown below.
Example of one keyword with “Exact keyword” search type:
There is no different result from searching one keyword with the “Exact keyword” and “Contains.” There will be three records as a result of searching the “letter” keyword with the “Exact keyword” search type.
Example of two keywords with “Exact keyword” search type:
There are two records as a result of searching for the “letter to” using the “Exact keyword” search type as shown below. The search function will be looking for the “letter to” keywords as one word.
Download file contains:
4 Tables, 8 Forms, and 2 Modules
