Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to split a blob of data into an array?

Discussion in 'Information Technology' started by Danny J. Lesandrini, Jul 28, 2006.

  1. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    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?

    SSN:2:False:345
    EmployeeNumber:4:False:1185
    AbsenceType:3:False:1140
    Assigned to:12:False:1200
    ProgramID:5:False:1650
    EmployeeName:1:False:2565
    GroupName:7:False:2190
    CloseDate:9:False:1335
    EmployerName:6:False:2100
    OpenDate:8:False:-1
    AbsenceID:11:False:-1
    RecCount:0:True:-1

    --
    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast
     
  2. Loading...

    Similar Threads Forum Date
    India Team Anna splits over politics Breaking News Sep 22, 2012
    India Split in Team Anna. Two core members quit committee. Breaking News Oct 18, 2011
    Canada Community Split on Resolution Banning Non-baptized Sikhs from Running Guru Nanak Temple Breaking News Jul 14, 2011
    India High Priests May Discuss Split Gurpurabs Next Week Breaking News Jan 7, 2011
    India Split in Badal Family, Amarinder Returns to Centrestage Breaking News Dec 30, 2010

  3. Linc

    Linc
    Expand Collapse
    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.



    "Danny J. Lesandrini" wrote:

    > 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?
    >
    > SSN:2:False:345
    > EmployeeNumber:4:False:1185
    > AbsenceType:3:False:1140
    > Assigned to:12:False:1200
    > ProgramID:5:False:1650
    > EmployeeName:1:False:2565
    > GroupName:7:False:2190
    > CloseDate:9:False:1335
    > EmployerName:6:False:2100
    > OpenDate:8:False:-1
    > AbsenceID:11:False:-1
    > RecCount:0:True:-1
    >
    > --
    > Danny J. Lesandrini
    > dlesandrini@hotmail.com
    > http://amazecreations.com/datafast
    >
    >
    >
    >
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    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:

    Dim a1 As Variant, a2 As Variant, i As Integer
    a1 = Split(sBlob, vbCrLf)
    ReDim a2(UBound(a1))
    For i = 0 To UBound(a1)
    a2(i) = Split(a1(i), ":")
    Next

    Now, a2(4)(3) would give 1650.
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand


    "Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in message
    news:eJLSDiJlGHA.1664@TK2MSFTNGP03.phx.gbl...
    > 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?
    >
    > SSN:2:False:345
    > EmployeeNumber:4:False:1185
    > AbsenceType:3:False:1140
    > Assigned to:12:False:1200
    > ProgramID:5:False:1650
    > EmployeeName:1:False:2565
    > GroupName:7:False:2190
    > CloseDate:9:False:1335
    > EmployerName:6:False:2100
    > OpenDate:8:False:-1
    > AbsenceID:11:False:-1
    > RecCount:0:True:-1
    >
    > --
    > Danny J. Lesandrini
    > dlesandrini@hotmail.com
    > http://amazecreations.com/datafast
    >
    >
    >
     
  5. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    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.
    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    On Error GoTo Err_Handler

    Dim ctl As Control
    Dim strBlob As String
    Dim strColumns() As String
    Dim strColOrdered() As String
    Dim intColumns As Integer
    Dim intColumn As Integer
    Dim strValues() As String
    'Dim intValue As Integer

    On Error Resume Next

    strBlob = GetSetting(gcstr_WebErrCode, "Column_Settings", frm.Name, "")
    If strBlob <> "" Then
    strColumns = Split(strBlob, vbCrLf)
    ' Have to resort the list according to ordinal number
    Call GetOrderedColumns(strBlob, strColumns)

    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

    Exit_Here:
    Exit Sub
    Err_Handler:
    'LogErrorToTable Err.Number, Err.Description, "basUserColumnSetup", "LoadUserColumnSetup", Erl
    Resume Next
    End Sub

    Private Sub GetOrderedColumns(ByVal strData As String, ByRef strColumns() As String)
    On Error Resume Next

    Dim strTemp() As String
    Dim intCols As Integer
    Dim intCol As Integer
    Dim intCurr As Integer
    Dim strValues() As String

    strTemp = Split(strData, vbCrLf)
    intCols = UBound(strTemp) - 1

    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

    Dim ctl As Control
    Dim strBlob As String

    For Each ctl In frm.Controls
    If ctl.ControlType <> acLabel Then
    strBlob = strBlob & ctl.Name & ":" & ctl.ColumnOrder & ":" & ctl.ColumnHidden & ":" & ctl.ColumnWidth &
    vbCrLf
    End If
    Next

    SaveSetting gcstr_WebErrCode, "Column_Settings", frm.Name, strBlob

    Exit_Here:
    Exit Sub
    Err_Handler:
    'LogErrorToTable Err.Number, Err.Description, "basUserColumnSetup", "SaveUserColumnSetup", Erl
    Resume Next
    End Sub
     
  6. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    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.

    Just my 2c worth :)
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in message
    news:uERz$1TlGHA.1208@TK2MSFTNGP02.phx.gbl...
    > 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.
    > --
    >
    > Danny J. Lesandrini
    > dlesandrini@hotmail.com
    > http://amazecreations.com/datafast
    >
    >
    > On Error GoTo Err_Handler
    >
    > Dim ctl As Control
    > Dim strBlob As String
    > Dim strColumns() As String
    > Dim strColOrdered() As String
    > Dim intColumns As Integer
    > Dim intColumn As Integer
    > Dim strValues() As String
    > 'Dim intValue As Integer
    >
    > On Error Resume Next
    >
    > strBlob = GetSetting(gcstr_WebErrCode, "Column_Settings", frm.Name, "")
    > If strBlob <> "" Then
    > strColumns = Split(strBlob, vbCrLf)
    > ' Have to resort the list according to ordinal number
    > Call GetOrderedColumns(strBlob, strColumns)
    >
    > 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
    >
    > Exit_Here:
    > Exit Sub
    > Err_Handler:
    > 'LogErrorToTable Err.Number, Err.Description, "basUserColumnSetup",
    > "LoadUserColumnSetup", Erl
    > Resume Next
    > End Sub
    >
    > Private Sub GetOrderedColumns(ByVal strData As String, ByRef strColumns()
    > As String)
    > On Error Resume Next
    >
    > Dim strTemp() As String
    > Dim intCols As Integer
    > Dim intCol As Integer
    > Dim intCurr As Integer
    > Dim strValues() As String
    >
    > strTemp = Split(strData, vbCrLf)
    > intCols = UBound(strTemp) - 1
    >
    > 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
    >
    > Dim ctl As Control
    > Dim strBlob As String
    >
    > For Each ctl In frm.Controls
    > If ctl.ControlType <> acLabel Then
    > strBlob = strBlob & ctl.Name & ":" & ctl.ColumnOrder & ":" &
    > ctl.ColumnHidden & ":" & ctl.ColumnWidth & vbCrLf
    > End If
    > Next
    >
    > SaveSetting gcstr_WebErrCode, "Column_Settings", frm.Name, strBlob
    >
    > Exit_Here:
    > Exit Sub
    > Err_Handler:
    > 'LogErrorToTable Err.Number, Err.Description, "basUserColumnSetup",
    > "SaveUserColumnSetup", Erl
    > Resume Next
    > End Sub
    >
    >
     
  7. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    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.

    Private Sub GetOrderedColumns(ByVal strData As String, ByRef strColumns() As String)
    On Error Resume Next

    Dim strTemp() As String
    Dim intCols As Integer
    Dim intCol As Integer
    Dim intCurr As Integer
    Dim strValues() As String

    strTemp = Split(strData, vbCrLf)
    intCols = UBound(strTemp) - 1

    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



    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "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