How to Split Pipe-Delimited Access Data into Separate Records

In Microsoft Access, press the ALT + F11 keys on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a new module and declare your variables. Open your source table using dbOpenForwardOnly and your target table using dbOpenDynaset via the dbs.OpenRecordset method.

Use a 'Do While Not rstIn.EOF' loop to iterate through each row. Inside the loop, use the native 'Split(rstIn!Parameters, " | ")' function to separate the parameter string into a VBA array.

Loop through the newly created array. Split the parameter name and value using the equals sign as a delimiter, then use 'rstOut.AddNew' to populate the ParamName and ParamValue fields, followed by 'rstOut.Update' to save the record.

In the VBA Editor, create a Public Function (e.g., 'Splt') that accepts the source string, a delimiter string, and a position integer as arguments.

Within the function, use the native Split function and return the specific array element corresponding to the position integer minus one. Add error handling to return Null if the position exceeds the array size.

Create a simple table in Access containing a single column with sequential numbers (1, 2, 3, etc.).

Create a query that joins your source table with the numbers table. Pass the sequential value from the numbers table as the position argument to your custom 'Splt' function to dynamically generate multiple rows.