CopyFromRecordset Limit? Alternative?

[davidb88]

Hi Forum -

I have a bit of an emergency with a workbook that I have built. I have buttons in a workbook that when pressed, they are supposed to connect to an Access database via an ADO connection and paste those records into a set worksheet. The copy and paste of recordset was done through the copyfromrecordset function. It worked really well until this morning there were more than ~200 records added in. Now I get an error on the copyfromrecordset function when I try to run the code. Is there a limit to the number of rows it can copy and paste? If so, is there an alternative?

Thank you in advance for your help! Hopefully I can resolve this soon!

 

[Norie]

I'm pretty sure if there was a limit it wouldn't be as low as a few hundred records.

What error(s) are you encountering?

Are they from Excel, Access or ADO?

Can you post the code you are using?

 

[davidb88]

Hi Norie -

Thanks for your fast response. I am getting an error that says "Method 'copyfromrecordset' of object 'Range' failed. The records are coming from an Access database and being pasted into an Excel worksheet.

This is the code. It was working until this morning. I'm not sure if there is a row limit, but maybe there is a character limit? Since people have added in more records in the database it seems to have triggered something that is causing this error, but I am not positive.

Sub chkall()
 
Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset
Dim strSQL As String
Dim i As Long
Dim objFld As ADODB.Field
 
'open the connections to access
    Set objConn = New ADODB.Connection
    objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source= \\LLSPRO Database - Do Not Use\LLS_PRO_Database_v1_be.accdb;Jet OLEDB:Database Password=pass23"
    Set objRs = New ADODB.Recordset
   
    strSQL = "SELECT tblclstrack.* FROM tblclstrack"
    objRs.Open strSQL, objConn, adLockReadOnly
 
'clear current sheet
    Worksheets("CLS_LLS_PRO_Email_Tracker").Activate
    Range("A1").CurrentRegion.ClearContents
 
'set the field headings
    i = 0
    With Range("A1")
        For Each objFld In objRs.Fields
        .Offset(0, i).Value = objFld.Name
        i = i + 1
        Next objFld
        End With
           
'paste tblclstrack
    Worksheets("CLS_LLS_PRO_Email_Tracker").Range("A2").CopyFromRecordset objRs
   
'close the connections
    objRs.Close
    Set objRs = Nothing
    objConn.Close
    Set objConn = Nothing
 
 
End Sub

 

[Norie]

Does this work?

Dim rngDst As Range

    ' other code 
    
    Set rngDst = Worksheets("CLS_LLS_PRO_Email_Tracker").Range("A2")

    With objRs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While Not .EOF
                For i = 0 To .Fields.Count - 1
                    rngDst.Offset(, i) = .Fields(i).Value
                Next i
                objRs.MoveNext
                Set rngDst = rngDst.Offset(1)
            Wend
        End If
    End With

    ' rest of code

 

[davidb88]

Hi Norie - thanks for your help. It actually turned out not to be a problem with the code, but rather in one of the fields in the database, there was an entry that exceeded the character limit which was causing the error. Thank you for your help and suggestions!