How to Access Parse Data

Use VBA in Access to parse data.

Knowing how to parse and access parsed data can save you time when developing a database application. Often database administrators need to parse data in a database field and add it to other tables or use it in other ways. You can accomplish this task by using Microsoft Office Access. Access is a relational database management system included in the Microsoft Office suite. In Access you can use Visual Basic for Applications (VBA) to parse and access parsed data.

Things You'll Need

• Microsoft Office Access

Instructions

1. Start Microsoft Office Access and select "Blank Database" then click "Create." Click "Database Tools" then select "Visual Basic".

   Type the following to create a new procedure:

   Private Sub parseFieldData()

   Press "Enter."

2. Type the following to declare the variables you will use while parsing the data:

   Dim sSQL As String

   Dim db As Database

   Dim rst As Recordset

   Dim cellIntArray(4) As String

   Dim x As Integer

   Dim y As Integer

   Dim fldStr As String

   Dim pos As Integer

   Set db = CurrentDb

3. Type the following to create a new table to hold the data you will parse:

   sSQL = "CREATE TABLE tblParseData (fieldData TEXT(100));"

   DoCmd.SetWarnings False

   DoCmd.RunSQL (sSQL)

   sSQL = "INSERT INTO tblParseData VALUES('This sentence will be parsed.');"

   DoCmd.RunSQL (sSQL)

4. Type the following to query the data you added to the table in the previous step:

   Set rst = db.OpenRecordset("Select tblParseData.* from tblParseData;")

   rst.MoveFirst

   fldStr = rst.Fields(0).Value

5. Type the following to add the parsed data to a String array:

   x = 0

   pos = InStr(1, fldStr, " ")

   Do While fldStr <> ""

   cellIntArray(x) = Left(fldStr, pos)

   fldStr = Right(fldStr, Len(fldStr) - pos)

   pos = InStr(1, fldStr, " ")

   x = x + 1

   If pos = 0 Then

   cellIntArray(x) = fldStr

   fldStr = ""

   End If

   Loop

6. Type the following to access the parsed data from the array:

   For y = 0 To x

   Debug.Print cellIntArray(y)

   Next y

7. Type the following to close your Recordset and database:

   rst.Close

   db.Close

References

• Microsoft: Create Table
• Microsoft: Database.OpenRecordset Method
• Microsoft: DoCmd.RunSQL Method

Resources

• Microsoft: Access 2010 Development