Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2021

    Proof-of-Concept database- Can I achieve similar results with less macros and better relations?

    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:

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    think you need to provide a more detailed explanation of what you are trying to do from the 10,000 foot level

    'comparative translation analyses' implies language

    so where does 'l
    inking of figures' come into it?

    figures will represent key moments' - what is a moment? an event? a specific point in time?

    My best guess is you are involved with translating film/tv programs etc and you need to ensure verbal translations will fit within the length of a scene/shot. (it can take longer to say something in German rather than English for example)

    I had a look at your zip file but there virtually nothing there.

    I have a policy of not going to unknown sites so will not be visiting your dropbox.

  3. #3
    Join Date
    Sep 2021
    Luckily for me, this is a hobby project, not something work related.
    I wouldn't want to cut my teeth on something like this, which is already turning out pretty complex.
    A Pro is that I can spend as much time on this as I like. a Con is that my spare time is limited so it's slow going.

    Anyway, I'll try to explain what I'm intending to set-up and how it relates to the proof-of-concept database I shared.

    Base Idea:
    For the sake of language comparisons, context of an utterance is very important. I'm hoping to set up a system that allows me to manage and sort the information that lets this context be determined. And I want to allow various translations of an utterance, for various readings (contexts) of the text.

    Specific Goal:
    My own interest lies mostly in applying this to videogame translations. Here the context can often be cleared up in large parts from the visual frame, especially in newer games. Adding to that, taking screenshots is less error-prone than manually copying the script while playing. So I want the screenshots with the relevant textbox or subtitles to form the the basis from which I work. These can then be linked to transcripts that have the text of the lines seen in the shot, and possible translations of that line.

    I want the ability to link audio if that is available to me, so I can easily check if lines are delivered, for example, sarcastically or not.

    And I want to be able to tag the pictures, audio and translations, so I can easily filter and search through them. I imagine I would add tags like 'Present: Character A', or 'Speaking: Character B', to an image, or 'Tone: Sarcastic' to an audio file, or 'Theme: Sacrifice' to a specific translation. Then once that's done, I could use Queries or dedicated Forms to collect the relevant information to assess character arcs, thematic through-lines and whatnot. And finally, I could take that into account to write-up translations which I think strike a good compromise between all the relevant issues that the line touches on, and I could compare these to the official translations.

    So hopefully, that gives a good idea of what I'm trying to do.

    Proof-of-concept justification 1- Preliminary database structure:

    For the proof-of-concept phase I'm in, I working under the following assumption:

    I'm not 100% sure if I can guarantee that the pictures I'll be working with will always be stored in a well-ordered folder structure, or use naming conventions that make it easy to find them. I might also want to add more files later, and if I've attributed index numbers to the pictures I have, this means my indexes could be grossly out-of-order. So the first thing I want is the ability to order and interlink the pictures I have.

    So I'm looking at a set of X pictures, which relate to each other in some way. This gives me 0,5X^2-0,5X possible picture combinations. However, because I'm working in 2 languages for now, I'll only use about 1,5X actual relationships. (assuming two language tracts of 0,5X pictures length, with mostly one-to-one translations, so that times 3 relations per picture set) So I only want to store the actual used relations (linear with X) rather than the full set op options (quadratic with X)

    Between two pictures, ordered small to large by their indexes, there are 5 possible relationships:
    • one-to-one translation from language A to language B
    • many-to-one translation from language A to language B
    • one-to-many translation from language A to language B
    • next in sequence of Language A
    • previous in sequence of Language A

    I've added some files with figures that show my own working space with some relevant relations set up, which also show how my record structure grows once X becomes a bit larger (154 pics -> ~12000 possibilities -> ~225 relationships)
    Unused data and Data

    Proof-of-concept justification 2 - choice of first goalpost:

    In this case I'm looking at unstructured many-to-many relationships. There are 154 possible pictures as the first picture, then at most 153 possible pictures with a larger index number, (less than that, the higher my first picture's index is,) and 5 possible relationships per combination.
    This will be similar to how other things in my database will be linked, but those will not be self-referential, i.e. the options for the first key item of the many-to-many relationship won't impact the number of options for the second key item.
    If I have 200 audio files, each audio file links with a picture, and possibly multiple audio file per picture, but I can make the audio the primary key.
    Transcripts can just have a one-to-one relationship with my pictures.
    Each picture can have multiple translations, but not the other way around.
    Tags will be many-to-many as well, any picture, or audio file or translation can have many tags, and one tag can apply to many different objects. But at least there's none of, what I earlier referred to as self-reference, between the two.

    So this picture relationship set-up is the most complex thing I image would be in the database, and forms the spine of the entire thing.
    So I felt If I could get it up and running, Access would be a viable option to do what I'm hoping to.

    And I got it working.

    But it's very much my first Access project. So I'm hoping to figure out how to do it better, in a way that lets me build on what I have now.
    And I think I've been a bit over-reliant on macros to get Access to do what I want.

    Regarding shared files:

    I don't mind that you're not willing to download from an external source. It should be virus-free, but I'd not trust a random person on the internet either.
    If I wanted to do harm, I could do messy things with the macro's in the files I shared too, though. So if anyone is going to experiment, a modicum of trust will need to be given.

    I made the dropbox files available because I feared things wouldn't work properly on other systems, but adding my picture files, even after limiting my database size made things excessively big.
    The Excel file worked for getting rid of items to reduce my database of pictures, but on further testing didn't work so well anymore for setting up on a new location.
    I did some more tests, and fixed it, I think. I also added some figures to the new file on how I added some random pictures from a report to work with this database on a new location. This will hopefully make it clearer how to get things to work as intended, even if -in that case- the relationships don't make sense anymore.
    So with the file below, anyone should be able to set-up a sub-folder of random pictures that can be worked on in the access database.


    Final thoughts:

    I hope this made everything a bit clearer and that the files work better if anyone wants to have a look.
    Sorry it turned into such a long read though. I hope it's not a problem.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Ok, I get the what but not the why🙂 as for the how you need multiple tables.

    Still donít follow what you mean be language- do you mean English/German etc? Or good/bad/aggressive etc?

    Off the top of my head and not a full answer think you will need tables along the following lines.

    Stories -the name of the game, film, whatever

    Characters- names of the characters this might be linked to a story (1 to many) or multiple stories via a linking table (many to many) if the same character appears in multiple stories

    Scenes - (your pictures) linked to stories and containing links to an audio file and other related files such as scripts, ( might require an Additional table if it is a 1 to many) and another table for the many to many relationship with characters (I.e. those present in the scene) and perhaps links to previous or next scene(donít need both)

    Tones - list of tones such as aggressive, soft, sarcastic etc)

    TonesUsed - Table to link scene and character to tone

    Make sure each table has an autonumber PK and that this is used when linking other tables to it

  5. #5
    Join Date
    Sep 2021
    To start with the easiest thing to clarify, I'm talking about English/German etc. Not good/bad/aggressive.

    On Ajax's second question: I need multiple tables because the amount of fields will vary per entry.

    I can't just have 'one' translation field, because the same line may be translated in a number of ways, all of which may be correct...from their own point of view. And the number of lines that are viable may differ from one picture to the next.

    The same goes for audio files, tags, and even transcripts. (Regarding transcripts: Japanese has phonetic writing and ideographic writing systems operating side by side. Sometimes the same word may be written down in both -one over the other- to emphasize how the intended meaning of the word differs from the word that was spoken. When I encounter that, two fields for transcripts might come in handy, although I could also use apostrophes to sort it out.)

    But, to speak more generally, all of that is fairly standard Access fare, as I understand it. I can design a database so that, once it's filled in and I have a picture I'm starting out with, I can easily look-up and sort the related transcripts, translations, audiofiles and tags for that picture.

    I wouldn't call it trivial. But it is eminently do-able. The tables etc. Ajax suggested related to setting up a database structure like this.

    What is less clear to me, and therefore more of interest, is whether there's a good way to set-up a structure that lets me navigate my visual data.

    If I'm on the figure of a certain line in English, and want to go to next line in the story, I don't want to have to manually scroll through my entire unstructured dataset, again-and-again, to find it.

    Because it is visual, I can't have Access analyze this data for me. Not unless I couple Access to an AI, which would be even more hard to set-up and train.
    If my data is text, Access can easily alphabetize data, or -if it's numbers- sort them in ascending order. But visual data is just a whole other topic.

    So I want the user (i.e. myself) to set up the relevant relationships between the figures in their(my) data set, so it can be easily navigated.

    That is what the proof-of-concept database is doing. It has a form with two independent controls, each linked to my figure dataset, which shows the picture they're on. And the form shows the relationship I've set up between them as well, and lets it be edited.

    It's also already allowing for navigating the possible relationship field on a higher level. The form allows me to jump ahead a certain amount on both pictures at the same time.

    So navigation is possible!

    A next step would be to implement a navigation tool that uses the relationships I set-up in this one to navigate, instead of just record number. But I don't want to get ahead of myself.

    Because, as said, the way I implemented navigation, is likely not the most efficient way to do so, seeing as how I needed about two pages of macros, divided between parent and child forms to get it to work.

    Actually, because 'navigation' is fundamentally different from 'search & sort', I can probably just separate the database structures for both.

    Then I would just use the navigation options I've built to select the figure I'm interested in, and Access could make the related information available without running into errors regarding not-updateable recordsets.

    I suspect I could get that to work and have the "translations database" be completely insulated from the "navigation database", and any errors and weird relationships in it.

    But that still leaves the question of how best to set up a way to navigate data, where Access isn't able to do so itself.

  6. #6
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Somerset, UK
    I've almost completed an Access app called Translate & Speak. It uses Google Translate together with Text to Speech.
    There is a brief video of an earlier version of the app at Translate & Speak using Microsoft Access - YouTube

    You can also download a copy of that version at Translate & Speak | Access World Forums (
    Let me know if it looks useful.
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    sking for help isn't giving up. Its refusing to give up.

  7. #7
    Join Date
    Sep 2021
    Thanks for the suggestion, Isladogs.

    I had a look at the Access Tool you created, but sadly -although tangentially related- its achievements are not relevant to what I'm trying to accomplish here.
    Your database was integrating AI tools for translation and text-to-speech. I'm just looking to get a database that lets me streamline my own (human) translation process, by grouping relevant information.
    It was cool how much you got it to do, though. I could only follow along with the code in terms of general functionality. But the parts that leaned heavily on the external libraries were just opaque to me.

    Anyway, having reframed what I was attempting to do as 'navigation' yesterday, I pondered the matter some today and came to the conclusion that, in all likelihood, it's impossible to pull this off without having to program things in Vba that fall outside of the standard abilities of Access.

    I can think of this 'navigation' in terms of just having a number of named links to 'other figures' for each figure, to instantly navigate to these 'other figures' that are related to my current figure in some way.
    But then, when I try to sketch that out on paper, the result very much starts to resemble a graph database. Since Access is a relational database, it's...
    Well, it's probably not impossible to do what I'm thinking of, within constraints...
    But in the most general of terms, Access just wasn't built with what I'm trying to achieve here in mind.

    And if that's the case, I didn't so much as "do it wrong", but it was more like I "chose the wrong tool for the job." Sadly, since it's the only tool I have available (no administrative rights on the system I can use for this, so no installing other software unless I can make a really good case for it,) I'll probably continue trying to figure this out in Access. It's just going to be a matter of optimizing what I did so far, and expanding the options available to me, and not figuring out how to do the same without using Vba.

  8. #8
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Somerset, UK
    No problem.
    I'm still not totally clear what you are trying to do but had wondered whether you could make use of the translation part.
    Good luck with your project
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    sking for help isn't giving up. Its refusing to give up.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-12-2021, 12:54 PM
  2. Replies: 29
    Last Post: 08-21-2017, 06:45 AM
  3. Basic concept of database
    By Vrbic00 in forum Access
    Replies: 2
    Last Post: 07-26-2016, 06:33 PM
  4. The concept of updating a database
    By hect1c in forum Import/Export Data
    Replies: 3
    Last Post: 11-16-2014, 08:45 PM
  5. Replies: 3
    Last Post: 04-12-2010, 02:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums