Hello everyone,
I'm a novice at Access and am intending to build a database framework that lets me more easily sort through information I've gathered for comparative translation analyses. (Specifically pictures, audio files, transcripts, descriptions and translations.)
This will require quite a number of 'many-to-many' relationships, which will all have to be sorted through in different ways. This results in an exceedingly large range of possible combinations, which would result in very large tables if I were to save every option. I have some thoughts on how I'll set things up to work efficiently, but at the moment, the exact structure of the database I'm going for isn't all that relevant yet.
I'm still at the proof-of-concept stage, so to see if this could work to begin with, I set out to create a single many-to-many relationship database. Specifically the most critical and complex one I think will be in the project. It pertains to the linking of figures, currently using some I'm working with for a recent project, using a Form that lets me manage these basic relationships fairly easily. These figures will represent key moments, which will have audio, transcripts, translations, tags etc. linked to them at a later point.
It took some work, but I succeeded. The result can be found in the attachment to this post, although I couldn't add the pictures it links to without going over the max upload size.
The zip file luckily also has an Excel file with a macro that loops over subfolders and gathers pictures (*.png) in it. The resulting Excel table is intended to be linked to in the Access database. So with just the attached files, you can add a subfolder with some pictures where you've stored the files, repopulate the excel table, (It has a button for that, should be mostly self-explanatory,) re-establish the link to that table in Access and the whole thing should run as intended. (Make sure you have macro's on!)
The same file, but including the specific figures I've been working with, can be found in the dropbox link below, although there you'll still need to update the Excel table and re-link it in Access, because it uses absolute paths. Either that or store everything specifically at "C:\Thuiswerken\POC"
So that should hopefully allow anyone interested to see what I've got so far. Problem is, I resolved problems I encountered along the way as I do so in Excel. When I want to do something I think should be simple, but can't find a way the program allows me to do it easily, I write a macro. I don't think I went overboard with it, and the result appears fairly robust and efficient, and it does what I want it to. But I did not subject my database to rigorous testing. And I suspect building on this as a basis will have me running into trouble later down the line.
Some problems I ran into along the way:
- Imported Excel tables can't have a primary key, and therefore can't form the basis of a one-to-many relationship, or be properly connected by a junction table to result in a many-to-many relationship. I worked around this by setting up a query that collects all possible relevant combinations (qryFigureTuples) which I linked to my junction table.
- Using join-type 2 in my Form dataquery allows me to create a record set that has all options, but Access isn't clever enough to work back from that to the underlying data or to connect that base data automatically. I had to equip my base SelectFigure Form with code that pushes information up to parent Forms to allow it to properly navigate when inserted in a parent form. The parent form, frmFigureLinks, had the join-type 2 query, and needed code to read and write into the underlying Table, tblFigureLinks, because this query was 'unupdateable', locking my ability to modify my information.
I suspect I can use vba in Access to populate internal tables with data of my fileset and their locations in my file structure -including proper primary keys- and be rid of quite some trouble. But I fear the join-type 2 relationship would result in trouble either way.
Since I have a working example of what I want to do, I was hoping someone with more experience could look it over and tell me whether something similar could also be done without macro's. (Not for getting the control buttons to act, of course, you can't do without assigning those something, but without macros for reading from- & writing to my tables and navigating my possibility range.) Any advice on how something like this could best be set-up properly would also be much appreciated.
Oh, and though I think I cleaned up my files fairly well, there may be some leftover Dutch words in places. Sorry about that, but the important parts should all be understandable using just English. If I missed anything, or something is unclear, please specify what it is, and I'll update my file, or at the very least attempt to clear up the matter.
Links to the files I referred to:
POC.zip
https://www.dropbox.com/s/gcjzkstiei...small.zip?dl=0