Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post
    I am trying to split a string in a field into records. The code below is running without errors but not populating the table. I think my problem lies in the .CommandText = "qryLists" . The query is: SELECT tblListSNs.newSNs FROM tblListSNs;. I would like add each arData(i) to newSNs field.

    Code:
    Private Sub modStrSplit()
        Dim arData() As String
        Dim objConn As ADODB.Connection
        Dim objRec As ADODB.Recordset
        Dim objCmd As ADODB.Command
        Dim strTableNM As String
     
        'Setup error handling
        On Error GoTo modStrSplit_Err      
        Set objConn = CurrentProject.Connection
        Set objRec = New ADODB.Recordset
        Set objCmd = New ADODB.Command
        strTableNM = "Split the SNs"
        newSNTbl = "tblListSNs"
     
        objRec.Open Source:=strTableNM, ActiveConnection:=objConn, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, options:=adCmdTableDirect
     
     
        'Iterate through the recordset one row at a time
        Do Until objRec.EOF
            'Step 1: Parse the Projects field into a array for futher
            'processing
     
            strData = objRec("SNs")
     
            arData = Split(strData, ",")
     
            'Step 2: Loop through the array item by item, inserting
            'each item into a separate row in tblListSNs
            For i = LBound(arData) To UBound(arData)
                 With objCmd
                    .ActiveConnection = objConn
                    .CommandType = adCmdStoredProc
                    .CommandText = "qryLists"
                    .Execute , Array(objRec(0), arData(i)), adExecuteNoRecords
                 End With
     
     
            objRec.MoveNext
        Loop
     
        objRec.Close
        MsgBox "All done!"
    modStrSplit_Exit:
        Set objCmd = Nothing
        Set objRec = Nothing
        Exit Sub
     
    modStrSplit_Err:
        'Alert the user that an error occurred
        MsgBox Err.Number & vbCrLf & Err.Description
        Resume modStrSplit_Exit
     
    End Sub

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I don't work much with ADO, as I learned DAO first, and find it more straightforward for most situations, so I can't comment with any certaintly on that part of the processing. However, if I understand what you are trying to do, you are taking a field from a table, and parsing it with the Split function (BTW, it doesn't deal with certain situations, such as two consecutive delimiters), and writing the results out as separate rows in a table. If that's right, then are you adding rows in the same table? I only see one table being opened. Also, in most instances a For statment should have a closing Next statement, and I don't see one in your code. I would suggest single stepping through the code to see exactly what is happening.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jul 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply. I've been messing with this code so much, I must have deleted the 'next' along the way. I am a beginner at ADO but have used DAO in the past. Maybe I should switch back! What I don't get is this code: With objCmd .ActiveConnection = objConn .CommandType = adCmdStoredProc .CommandText = "qryLists" .Execute , Array(objRec(0), arData(i)), adExecuteNoRecords End With
    The 'qryLists' is actually the new table where the data should be stored? I don't know how it adds data to the table? (pulled it from another source)
    About switching to DAO. Can I have one Access db running DAO and one running ADO?
    How would you write this in DAO?
    Thanks.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I guess you must have two UserIDs for the Lounge?

    You can use DAO and ADO in the same database, in fact you can use both in the same procedure. Using DAO, I would open the source recordset in a snapshot mode, and the destination recordset in append-only mode, and then do a Do While Loop with the Not EOF condition, do the split for the source field, loop through the array doing an Add New for each element in the array to the destination recordset, then do a Move Next in the source recordset. Using DAO, the table needs to be a local or linked table in the database, while ADO doesn't have to use a local table - it can connect directly to a SQL Server, text file, or other source without having the table defined in the TableDef collection.

    I wonder if in this situation, you might be able to use the TransferText method to parse the comma delimited field by exporting it to a text file, and reimporting it. That would let you deal with the possibility of consecutive delimiters and how they are to be handled, and would avoid creating a complex procedure. I often do something similar where I take the data to Excel and use the Text to Columns approach where I want to split a field (people names are a common need) into multiple parts. In that case you end up with multiple fields in a row rather than multiple rows with a single field. That might not fit your needs however.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post
    Thanks for the info, Wendell. I posted the original message for my daughter because she cannot reach this forum from her office. Then she replied to you this morning from home. I imagine she is at work by now and if she doesn't get it worked out I'll probably receive another email with a response to be posted for her. I'll email your latest message to her since she may have left for work by the time it was posted.

    We both appreciate your help.

    Bill

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Code:
    With objCmd
            .ActiveConnection = objConn
            .CommandType = adCmdStoredProc
            .CommandText = "qryLists"
            .Execute , Array(objRec(0), arData(i)), adExecuteNoRecords
    End With
    Difficult to say exactly what this is doing without knowing the parameters
    and also what the Query qrylist actually does.

    It is more common to use ADO when linking to an external database such as SQL SERVER in order to run the query
    on the server, but in your case if all the data is in Access, I think Wendell's solution suggestions are better.

    But to explain the essence of the Execute Method of the Comand Object

    The General Syntax is

    .Execute Optional Records Effected, Optional Parameters for query to use,Optional options

    So in your case

    .ActiveConnection = objConn 'Sets the Connection to use (which is the current database)
    .CommandType = adCmdStoredProc ' Tells ADO it is a saved Query
    .CommandText = "qryLists" 'Tells it which query to Run

    .Execute , Array(objRec(0), arData(i)), adExecuteNoRecords

    This is passing the element(s) Array(objRec(0), arData(i)) as the parameter(s) to send to the query to process

    The last option tells access that this query returns no records (ie an Action Query)

    I assume the query qryList is an append query that writes data into a table based open
    the incoming parameters

    I would guess that IF no data is being written, then either qryList is not the correct query to run,
    or the parameters passed to it generate NO records to Append.
    Andrew

  7. #7
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post
    A reply from Linda:

    Thanks for explaining this code! That was very helpful.

    The “qryLists” query is: SELECT tblListSNs.newSNs FROM tblListSNs;

    Yes, I think that is my problem. I’m not sure how to make it an append query because I am appending arData(i) which contains the split data?

    The qryLists table contains 2 fields – ID(autonumber) and newSNs.

    Could you tell me how to write the correct INSERT INTO statement?

    Thanks so much!

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    It would be a lot easier if you could post a sanitized copy of the database, with the
    required conversion requirement to show what needs to be done.

    I am not sure that using the existing code is the way to go.
    Andrew

  9. #9
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post
    Linda (flamingoparty) says this:

    Got it working in dao!!!!!!!!!!!!!!

    Thanks for everyones help!!!!

    Here is the code:

    Code:
    Public Sub TestSplit()
    Dim db As DAO.Database, rstRead As DAO.Recordset, rstWrite As DAO.Recordset
    Dim strResult() As String, i As Long
    
    Set db = CurrentDb
    Set rstRead = db.OpenRecordset("SN with Multiples", dbOpenSnapshot)
    Set rstWrite = db.OpenRecordset("tblListSNs", dbOpenDynaset)
    
    If Not (rstRead.BOF And rstRead.EOF) Then
        'If both are true, no records were in the table
        rstRead.MoveFirst
        Do Until rstRead.EOF
            strResult() = Split(rstRead!SNs, ",")
            For i = LBound(strResult) To UBound(strResult)
                With rstWrite
                    .AddNew
                    !newSNs = strResult(i)
                    .Update
                End With
            Next i
            rstRead.MoveNext
        Loop
    End If
    
    rstRead.Close
    rstWrite.Close
    Set rstRead = Nothing
    Set rstWrite = Nothing
    Set db = Nothing
    
    End Sub

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Well done - that looks pretty close to what I would have constructed. Glad we could help.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •