How to split a blob of data into an array?

ExpandCollapse

Guest

Ok, you're going to tell me not to use an array, but I really want to,
unless it's going to be ugly. Given the following blob of data as a
string, how could you load it into an array? into an ADO Recordset?

ExpandCollapse

Guest

If you open up Visual Basic (using Tools, Macros, Visual Basic Editor in
Access) you can use the VBA help. There are two topics you should
read..."Declaring Arrays" and "Using Arrays". They will tell you everything
you need to know. I know, because I just learned it myself.

ExpandCollapse

Guest

Hi Danny

Is your "blob" of data a single string with lines separated by CR/LF?

You could use the Split function to create an array of strings, one per
line, and then further Split each line so you have an array of arrays of
elements. For example, assuming your data is in a string sBlob, you could
do this:

ExpandCollapse

Guest

Graham:

Thank you for that suggestion. I've implemented it and it works.
You're correct, it is a string with embedded VbCrLf characters.

What I was wondering was if there was a way to simply cram the
entire thing into an ADO recordset. I have a vague recollection
of doing something like that with Access 97 when ADO was first
being used, but couldn't find the code. (It probably doesn't exist)

What I'm doing is reading the ColumnOrder, ColumnHidden and
ColumnWidth values for all the controls of a datasheet and dumping
them into the Registry. Another function pulls out the blob and
processes it, reapplying the user's column settings.

I have to do this because we've gone to a new-client-file update
system that is pushing out a new copy of their MDB file daily. Users
started to complain that their datasheets weren't "remembering"
the previous day's settings. This code works pretty slick, though
there might be some caveat I haven't thought of.

If anyone has any advice for things to watch out for when mucking
with datasheet columns in code, I'd be glad to listen.
--

intColumns = UBound(strColumns) - 1
If intColumns <> 0 Then
' The first column (0) is for RecCount and shouldn't be touched.
' Start with the first table column, ordinal position = 1
For intColumn = 1 To intColumns
strValues = Split(strColumns(intColumn), ":")
Set ctl = frm.Controls(strValues(0))
ctl.ColumnOrder = CInt(strValues(1))
ctl.ColumnHidden = CBool(strValues(2))
ctl.ColumnWidth = CLng(strValues(3))
Next
End If
End If

ReDim strColumns(intCols)
For intCol = 0 To intCols - 1
For intCurr = 0 To intCols
strValues = Split(strTemp(intCurr), ":")
If CInt(strValues(1)) = intCol Then
strColumns(intCol) = strTemp(intCurr)
Exit For
End If
Next
Next

End Sub

Public Sub SaveUserColumnSetup(ByRef frm As Form)
On Error GoTo Err_Handler

ExpandCollapse

Guest

Hi Danny

I figured it was column settings, based on your sample data

I don't know of any way to dump the contents of an array into a recordset.

In any case, you would want these preferences to be saved on a per-user
basis, would you not? I think for user-specific settings such as these the
user registry hive (HKCU) is an appropriate place. Otherwise you need to
save the user's login name along with the settings in a table in your
back-end database.

There's no reason why you shouldn't do this, but I think I would still save
the actual data as a single string <preparing for flaming from normalisation
evangelists ;-)>

All you need is a simple table: UserName, FormName, and Settings. It's then
up to the form to interpret the format and meaning of the settings saved
there. If you had a multi-table, multi-field structure it would end up
being clumsier and far more restrictive.

ExpandCollapse

Guest

Graham:

First, the blob of text could include a user name and/or windows login name
so if it were possible to slam it into a recordset, that wouldn't be a problem.
(As you noted, since I'm using the registry, it's unnecessary, so that's why
it's omitted from my sample data.)

Second, I also thought about storing the blob in a table. I spoke with my
SQL Server DBA to see what he thought and while he didn't mind the amount
of data (100 users # 30 datasheets is not that many rows), he didn't like the
idea that it would get updated every time a user opened a form. He didn't
like the traffic and potential fragmentation it would create. So, it seemed
that the Registry idea would be better. It has the added benefit of porting
the user's settings automatically when they point to a different client dbs.
That doesn't happen for every user, but some power users work on several
databases. There would be no need for them to reset their prefs.

The problem was that Access kept crashing when I reset the ColumnOrder
property for the datasheet, until I reordered the array so that I set the
ColumnOrder of controls consecutively. I had to include a procedure to
do the reordering. (I'm never been great with handling arrays, but this
seems to work ... see below.)

This seems to work, so I'm not looking for a table solution anymore, but it
would have been cleaner to throw it into a sortable object. Dot Net has some
container objects like that, but not VBA.

ReDim strColumns(intCols)
For intCol = 0 To intCols - 1
For intCurr = 0 To intCols
strValues = Split(strTemp(intCurr), ":")
If CInt(strValues(1)) = intCol Then
strColumns(intCol) = strTemp(intCurr)
Exit For
End If
Next
Next

"Graham Mandeno" <Graham.Mandeno@nomail.please> wrote ...
> Hi Danny
>
> I figured it was column settings, based on your sample data
>
> I don't know of any way to dump the contents of an array into a recordset.
>
> In any case, you would want these preferences to be saved on a per-user basis, would you not? I think for
> user-specific settings such as these the user registry hive (HKCU) is an appropriate place. Otherwise you need to
> save the user's login name along with the settings in a table in your back-end database.
>
> There's no reason why you shouldn't do this, but I think I would still save the actual data as a single string
> <preparing for flaming from normalisation evangelists ;-)>
>
> All you need is a simple table: UserName, FormName, and Settings. It's then up to the form to interpret the format
> and meaning of the settings saved there. If you had a multi-table, multi-field structure it would end up being
> clumsier and far more restrictive.
>
> Just my 2c worth
> --
> Good Luck!
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand

Share This Page

It is the age of demonizing the Indian people by painting the worst stereotype about them . The BBC has already made a documentary hinting that the typical Indian male is a potential rapist . But why blame the BBC ?? There are plenty in the Indian media and film industry ready to co operate in...

About Us

Our community has been around for many years and pride ourselves on offering unbiased, critical discussion among people of all different backgrounds. We are working every day to make sure our community is one of the best.

Like us on Facebook

Support SPN

The management works very hard to make sure the community is running the best software, best designs, and all the other bells and whistles. We'd really appreciate your support!