none
Cannot bind form to ADODB recordset RRS feed

  • Question

  • Hi,

    I need to get values out of Excel and dump them into Access unpivoted.  Access doesn't provide an unpivot statement, like T-SQL does, so I have instead created two recordsets.  The first gets the flat file data from Excel.  I then use nested For Next loops to loop through each column I need to unpivot, and then each row, and add the unpivoted data to the second recordset.

    When I msgbox out the values from the second recordset I can see that I am getting the results I need.  So far all good.

    What I cannot seem to do is bind the second recordset to my form.  The following is my code:

    Private Sub btnPrevew_Click()
    'Import Dimensions
    Dim rss As New ADODB.Recordset
    Dim cnn2 As New ADODB.Connection
    Dim cmd2 As New ADODB.Command
    Dim rsr As New ADODB.Recordset
    
    'Check if txtFilename has been selected
    If txtFileName = "" Or IsNull(txtFileName) Then Exit Sub
    
    'Import data
    ''Set up the Excel connection
        With cnn2
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=C:\Test\" & txtFileName & ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
            .Open
        End With
    
    ''Open the recordset
        Set cmd2.ActiveConnection = cnn2
        cmd2.CommandType = adCmdText
        cmd2.CommandText = "SELECT * FROM [Sheet1$]"
        rss.CursorLocation = adUseClient
        rss.CursorType = adOpenDynamic
        rss.LockType = adLockOptimistic
        rss.Open cmd2
        rss.MoveFirst
        
        'Set recordset for Preview form
                    With rsr.Fields
                          .Append "Products", adVarWChar, 100
                          .Append "Application", adVarWChar, 100
                          .Append "FMth", adVarWChar, 10
                          .Append "Tons", adDecimal, 2
                    End With
                    
                    rsr.Open
                    
        ''INSERT unpivoted rows into rsr
        For ic = 1 To rss.Fields.Count - 3
            'Import all rows
            For ir = 1 To rss.RecordCount - 1
                rsr.AddNew
                rsr.Fields!Products = Nz(rss.Fields(0), "")
                rsr.Fields!Application = Nz(rss.Fields(1), "")
                rsr.Fields!FMth = Nz(rss.Fields(ic + 2).Name, "")
                rsr.Fields!Tons = Nz(rss.Fields(ic + 2).Value, 0)
                rsr.Update
                rss.MoveNext
            Next
            rss.MoveFirst
        Next
            
        Set Form_sfrmForecastImportPreview.Recordset = rsr
        Form_sfrmForecastImportPreview.txtProduct.ControlSource = rsr.Fields(0).Name
        Form_sfrmForecastImportPreview.txtApplication.ControlSource = rsr.Fields(1).Name
        Form_sfrmForecastImportPreview.txtMonth.ControlSource = rsr.Fields(2).Name
        Form_sfrmForecastImportPreview.txtTons.ControlSource = rsr.Fields(3).Name
        
    ''Tidy up recordset
    Clean:
        rss.Close
        Set rss = Nothing
        cnn2.Close
        rsr.Close
        Set rsr = Nothing
        
    End Sub

    This is the result I get:

    Please can someone tell me what I have done wrong here.  This method of binding an ADO recordset to a form works in another DB I have created, so I am baffled.  I have also tried:
    Set Form_sfrmForecastImportPreview.Recordset = rsr.clone

    I have spent the entire day puzzling over this.

    Please help!!

    Thanks,

    Charles


    Thursday, August 13, 2015 2:45 PM

Answers

  • Found it:

                    With rsr
                        .CursorType = adOpenKeyset
                        .CursorLocation = adUseClient
                        .LockType = adLockPessimistic
                    End With

    • Marked as answer by Charles__Toray Thursday, August 13, 2015 2:54 PM
    Thursday, August 13, 2015 2:54 PM