Hi,
Would this also apply to my situation:

I need to parse a long text field (imported from a very old database format)
which will contain 0 or more 13-character blocks of text. These blocks
represent status update entries; each begins with "!!" but does not have any
delimiters at the end of the block.

I have no experience with the Split() function in Access VBA, and the last
time I worked with arrays was a good 20 years ago.

I had written an event procedure parsing the blocks in a single record and
appending them to a second table using a foreign key to relate the two
tables. I used the Mid() function in my loop to process the string field
character by character.

Now I am faced with the task of processing the entire table at once. So
far, it looks as though the way to go is to use VBA and recordset operations
(with which I have very little experience, being more SQL oriented), to
unpack the status blocks into a temporary table containing the ID and the
string, just to keep this step simple. After creating this temp table, I
would call an append query using Mid() to unpack the status block into the
appropriate fields of the status table.

Thanks in advance!


"John Nurick" wrote:
[..]

JMHO here, but it would seem to be much easier to simply use ADO methodology
to do the entire thing. You have complete control over each step of the
process, and unlike a SQL query, you can single-step through the code to make
sure each element is being parsed correctly.

"mary r" wrote:
[..]

The Split function would be exactly the thing to use in this case. It is
not that difficult and the fact that there is no delimiter on the end makes
it even easier. If it did, the resulting array would have a null element at
the end.

As to working with arrays in Access. It really isn't hard. You do have to
be aware of the Option Base statement. Option Base determines whether array
elements start with 0 or 1. The default is 0, so it will always be 0 unless
you have Option Base 1 specificed at the top of your module. So, the array
index will be 0 to the number of elements -1. It is just like many of the
property collections in Access.

There is also the Ubound function that will tell you what the highest
element index is. If you are using Option Base 0 and there are 6 elements,
the Ubound function will return 5.

So if you need to create a record for each element, here is a general
outline to get you started:

Dim varMyArray As Variant
Dim lngX as Long
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SomeTable")
varMyArray = Split(BlockOfStrings, "!!")

With rst
For lngX = 0 to UBound(varMyArray)
.AddNew
!BlockField = varMyArray(lngx)
.Update
Next lngX
.Close
End With
Set rst = Nothing

Like I say, this is just an outline, I am sure there will be more to do, but
hopefully, this will get you started.

Good Luck!

"mary r" <maryr> wrote in message
news:6045
[..]

Dennis, thanks for your reply. I have very little experience and no training
in ADO, being a dinosaur who still remembers flow charts and linked lists :-)

"Dennis" wrote:
[..]

Thanks very much, Klatuu. I think I can move forward with this now. Have a
great day!


"Klatuu" wrote:
[..]