how to create a vba script that would parse out data from tables in Microsoft Access?
Posted on 2014-12-08
I have a table where I have a number field that contains a CONTROL ID. In this table, we have a custodian and location fields. So the custodian field contains names of people. It can be one or multiple. Location files contains the file paths for all of the data. So, I need to combine our custodian field with the location field. So for the example, the custodian field can look like this:
Keep in mind that the Location paths are not a set number of characters, they have vary. Custodian can contain one value or multiple values so I need my vba script to loop until there are no more names to pair up with the location. So the end result was Smith,John::\\Servername\PathforJohnSmith;Doe,Jane::\\Servername\PathforJaneDoe;Powell,Dan::\\Servername\PathforDanPowell in one field call All Custodians Locations.
To be able to accomplish this I created a series of queries. I used this expression first: Left([Location],InStr([Location],";")-1) to extract the first location into a separate field, then used the Right([Duplicate Locations],Len([Duplicate Locations])-InStr([Duplicate Locations],"|")) that gave me the remaining locations. This works on the custodian field with the names as well since both fields had delimiters.
I would have to repeat this process over and over again until all of the locations are extracted. This is the same with the custodian field as well. Then I would make up the fields with the location with concatenated.
I would like to automate this more within an Access database so it won't take me as long as it did for future work. Any suggestions would be happily received.