Access Based Media Manager (1 Viewer)

[BlueSpruce]

I am seeking an Access based media manager that allows me to index documents, images, and audio files so I can quickly locate, view, and attach selected files to email messages.

 

[DHookom]

What do you mean by “index”? Do you simply want an inventory of file names with specific extensions?

 

[arnelgp]

maybe what you need to start is to look for FileDialog or something.
see Form1 and Module1 on this demo.

 

[isladogs]

Why do this in Access?
The best file search tool is called Everything. It indexes all files on your computer so is lightning fast. Better still it’s free.

 

[BlueSpruce]

What do you mean by “index”? Do you simply want an inventory of file names with specific extensions?

By index, I mean to categorize the file so I can filter my searches.

 

[KitaYama]

I can filter my searches.

The app that @isladogs mentioned can do that.
It comes with a pre-installed filters and you can add your own filters.
It can be as a sidebar or combo box at top.

You can also use a lot of settings for each filter to show them as you wish. Or limit your search to a set of pre-defined folders.

You can also use vba to run a search using any filter you like.

pth = YourPathToExeFile
SearchThis = "A part of your file name/extension/Or anything you know about the file"
fltr = YourFilterName
Shell pth & " -filter " & fltr & " -s" & " " & clsConst.Quote & SearchThis & clsConst.Quote, 1

 

[BlueSpruce]

Why do this in Access?
The best file search tool is called Everything. It indexes all files on your computer so is lightning fast. Better still it’s free.

I agree that Everything is a good tool. Nevertheless, I want to know if there's an Access based generic file manager available that I can add more features to, like scan documents, cascading combo boxes for categorising files, assign custom filenames, create/view folder trees, etc.

I can always build it from scratch.

 

[CJ_London]

I can always build it from scratch.

Way to go

I created an app for cataloging photos- it populated the file properties with keywords and used a tree view type form to create and catalog the keywords and view the files in a web browser control

But don’t think it meets your requirements as it is not generic enough

 

[Levi_79]

I am interested in something similar. I have a huge collection of movies, music videos, series.
I want to be able to tag files and search for tags and compile a list or a playlist (for example want to list all "rock" and "music video").
As i see Everything only searches in file names.

 

[isladogs]

@Levi_79
You could use my free app to do that

Extended File Properties

This shows a simple method of obtaining all the extended file properties for any type of file or group of files in a folder and its subfolders. There are over 320 extended properties including the dimensions of pictures and the duration of video files.

isladogs.co.uk

 

[DHookom]

Inventory in a table would need to be refreshed regularly. However I wanted to create a snapshot of all my files (mostly images) to look for duplicates across multiple hard drives. I asked AI to write the code for me. I just added the ability to store the file type based on this thread.

Sub ScanFilesToAccess()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fso As Object
    Dim folderPath As String
    Dim folder As Object
    Dim file As Object

    ' Prompt user to select folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder to Scan"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1)
    End With

    ' Initialize FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(folderPath)

    ' Open Access table
    Set db = CurrentDb
    Set rs = db.OpenRecordset("FileInventory", dbOpenDynaset)

    ' Recursive scan
    Call ScanFolder(folder, rs)

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set fso = Nothing

    MsgBox "Scan complete!", vbInformation
End Sub

Sub ScanFolder(ByVal folder As Object, ByRef rs As DAO.Recordset)
    Dim subFolder As Object
    Dim file As Object
    'add FileType with SELECT CASE extension?
    Dim strFileType As String
    Dim strExtension As String
    ' Loop through files
    For Each file In folder.Files
        strExtension = Mid(file.Name, InStr(1, file.Name, ".") + 1)
        Select Case strExtension
            Case "jpeg", "jpg", "img", "mov", "png"
                strFileType = "Image"
            Case "ACCDB", "MDB", "ACCDE", "MDE"
                strFileType = "Access"
            ' === ADD MORE === or use a small lookup table
            Case Else
                strFileType = "Other"
        End Select
        rs.AddNew
        rs!FileName = file.Name
        rs!folderPath = file.Path
        rs!FileSize = file.Size
        rs!DateModified = file.DateLastModified
        rs!FileType = strFileType
        rs.Update
    Next file

    ' Loop through subfolders
    For Each subFolder In folder.SubFolders
        ScanFolder subFolder, rs
    Next subFolder
End Sub

These are the references (AI mentioned some but missed one)

AI also gave me the structure for the table (I added file type based on this thread)

 

[Imb]

I am seeking an Access based media manager that allows me to index documents, images, and audio files so I can quickly locate, view, and attach selected files to email messages.

Are you seeking something like this?

I have worked on an application with some 10,000 documents as Music sheets. Filenames and filepaths are stored in a Sheet_tbl.
There is a 1:m relation to a Feature_tbl. The Feature_tbl contains all kind of features regarding the Music sheet, like componist, performer, instrument(s), genre, easyness/level, tempo, ...
Features are grouped in a Category.
Now you can select from the Sheet_tbl with any combination of Features.
Features within the same Category are connected with an "OR", the different Categories are connected with an "AND", to extract the selected sheets.

 

[BlueSpruce]

Are you seeking something like this?

I have worked on an application with some 10,000 documents as Music sheets. Filenames and filepaths are stored in a Sheet_tbl.
There is a 1:m relation to a Feature_tbl. The Feature_tbl contains all kind of features regarding the Music sheet, like componist, performer, instrument(s), genre, easyness/level, tempo, ...
Features are grouped in a Category.
Now you can select from the Sheet_tbl with any combination of Features.
Features within the same Category are connected with an "OR", the different Categories are connected with an "AND", to extract the selected sheets.

I am seeking/building a Document Management System that allows healthcare patients to scan, store, view, and send medical records, using multiple selection criteria to filter desired documents. Below is my mockup ER.

 

[Imb]

I am seeking/building a Document Management System that allows healthcare patients to scan, store, view, and send medical records, using multiple selection criteria to filter desired documents. Below is my mockup ER.

Hi BlueSpruce,

At first sight of your diagram I thought that that all six related tables had the same structure, and thus ould be handled in the way of the Music sheets.
But then I saw it was only a mockup diagram, and all related tables have a different structure.

I agree, it is quite complex, especially with multi-selections form the same table.
Nevertheless, this kind of selection is standard available in all my applications.

To explain that, I have to go back in the evolution of my applications.
For the beginning on of my work with Access I have worked towards generalization. But only the forms were a hard nut to crack.
Then I realized that the binding of the form to the RecordSource was the killing factor in the generalization, and thus flexibilty. So I changed over to (User)control-binding. This means that the binding of a control is done on the moment that you focus on the control. With this binding the control knows where to get and store its value, but the control also knows how to act according to the user's wish, including the Before- and AfterUpdate functionality.

This makes it possible to use unbound forms, that are fully dynamical, completely independant of any data. In fact, the forms is "reduced" to a container with a number of anonymous controls, that are tuned with some generalized code, and displayed as such to the user. For all my applications I use only 2 forms: one continuous form, and one unbound form.

So, with this approach I can tune that one unbound form ready to make a dynamical SQL-string, for any table, with almost any condition depending on the usertype-definition for the fields. These usertype-definitions are more differentiated than the fieldtype definitions itself.

In my applications almost all underlying processes are generalized. This means that when starting with a new application, almost 90% of its future functionality is already there.

As an example the form to construct the WHERE-part of an SQL-string.
Focus is now on a "relation" (Vader), which happen to be the related records in the same table.

Further consequences of this approach is that I don't use Comboboxes, Subforms, Querydefs, Control classes, ... in trying to add more flexibility.

Imb.

 

[BlueSpruce]

At first sight of your diagram I thought that that all six related tables had the same structure, and thus ould be handled in the way of the Music sheets.

All 6 lookup tables do have the same structure. I can add a top level field, named "Category", for generically cataloging all types of files, e.g. Medical, Music, Business, etc. So it would then be a Library Management System.

 

[BlueSpruce]

@Levi_79
You could use my free app to do that

Extended File Properties

This shows a simple method of obtaining all the extended file properties for any type of file or group of files in a folder and its subfolders. There are over 320 extended properties including the dimensions of pictures and the duration of video files.

isladogs.co.uk

It's my understanding that user-defined properties can be added to files. Can your tool edit existing properties, or it just reads them?

 

[MarkK]

A link makes sense if a child entity is multi-dimensional, but if the child only has one dimension, just store that single data-point directly in the parent. You can put a combo on the field, and look up a finite list of data if you want, but when you link to a single field, the complexity of your link exceeds the complexity of the data you are linking to. This is a cost with no payoff.