Others

How to Split a Comma-Delimited String into Child Records in Access

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to split comma-delimited string data into separate child records within a Microsoft Access database using VBA or an append query.

Product: Microsoft Access
Device & OS: not provided
Scenario: Importing and normalizing data from Google Forms that contains comma-separated values into a relational database structure.
Observed behavior: The user has existing VBA code triggered by a command button and is unsure whether to maintain it as an event procedure or convert it into a callable function for an append query.

Before you start

Ensure you have a clear understanding of your Access table structure and the exact format of the incoming comma-delimited data from Google Forms before running any VBA update or append scripts.

Solution 1Recommended

Utilize an Event Procedure via Command Button

Keep the existing VBA code as an event procedure if it already successfully splits the strings and matches your current skill level.

While creating a callable function for an append query is an option, sticking to a command-button event procedure is often simpler and highly effective, especially when dealing with external data sources like Google Forms.

Open Form Design View

Open your Microsoft Access database, navigate to the form containing your data processing command button, and switch to Design View.

Access the VBA Editor

Right-click the command button, select 'Build Event', and open the VBA Editor to view your current script.

Implement the Split Logic

Ensure your VBA code uses the `Split()` function to parse the comma-delimited string and utilizes a loop (such as `For Each` or `For i = 0 To UBound(Array)`) to run an SQL INSERT statement for each child record.

Test the Procedure

Save the VBA module, switch back to Form View, and click the button to verify that the parent data correctly splits into relational child records.

Multi-value Fields limitation: Avoid using native Access multi-value fields for this task, as they are not well-suited for importing raw comma-separated data directly from Google Forms.

Solution 2

Convert VBA Code to a Callable Function

Refactor your VBA script into a public function if you need to process the string splits automatically within an Access Append Query.

Free Microsoft Office alternative

Looking for an Easier Way to Manage Office Data?

While Microsoft Access requires complex VBA to split strings, WPS Spreadsheet provides powerful built-in text-to-columns features to easily separate comma-delimited data from Google Forms. WPS Office is a lightweight, highly compatible alternative to Microsoft Office.

1. Download and Install WPS Office: Get the free WPS Office suite from the official website and open WPS Spreadsheet.
2. Import Your Data: Paste your comma-delimited data from Google Forms into a new spreadsheet.
3. Use Text to Columns: Navigate to the Data tab, click 'Text to Columns', select 'Delimited', and choose 'Comma' to instantly separate your strings into individual cells without any VBA coding.

High compatibility with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Built-in 'Text to Columns' feature to effortlessly split comma-delimited strings without coding.Lightweight design that runs smoothly on Windows, Mac, and Linux.Free to use with a familiar interface, ensuring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use multi-value fields for Google Forms data in Access?

Multi-value fields in Access have a proprietary structure that makes it difficult to map raw comma-separated text strings imported directly from external sources like Google Forms. Using VBA to split the string into a standard relational child table is much more robust.

Can I use an Append Query without VBA to split strings?

No, Microsoft Access queries do not have a built-in SQL function to split a single comma-delimited string into multiple rows. You must use VBA to iterate through the split array and insert the resulting records individually.

What is the VBA Split() function?

The Split() function in VBA takes a text string and a delimiter (such as a comma) and breaks the string into a zero-based, one-dimensional array of substrings. You can then loop through this array to create individual child records for your database.

Office Quick Solutions