Range("A1").CopyFromRecordset does not seem to work properly

Question

When I bring data from .db file to excel sheet , there is a problem with some records that return twice to the sheet and deleting the next record. The issue appears in record 256 and adding 256 to it: 512,768 ..etc . When I change the parameters of rst.Open strSQL, conn, 1, 1 to rst.Open strSQL, conn It returns only the missing records.

Private Sub CommandButton1_Click()
query_Data "SELECT * FROM Test_Table;"
End Sub
Private Sub query_Data(strSQL As Variant)
Cells.ClearContents
Dim conn As Object
Dim rst As Object
    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
        conn.Open "DRIVER=SQLite3 ODBC Driver;Database=E:\A disk\Lit\Data.db;"
        rst.Open strSQL, conn, 1, 1
                [Range("A1").CopyFromRecordset][1] rst
                rst.Close
        Set rst = Nothing: Set conn = Nothing
        Range("a1").Select
End Sub

I don't see anything in your code that would cause behavior like that. Furthermore the difference between cursor types `adOpenKeyset` (`1`) and the default of `adOpenForwardOnly` shouldn't cause `.CopyFromRecordset` to behave that way either. Are you certain that your column A is cleared before dropping the records in `Range("A1")`? Furthermore, are you certain that your data in `test_table` looks they way you expect it to? — JNevill, Jan 13 '22 at 15:10
This problem is occurring in all my tables and in every 255 record , and when I change The cursor types parameter below 1,it returns only the missing records. — Peter Tom, Jan 13 '22 at 15:20
When I use the GetRows method, and for loop I get right data but this is way slower than the copyFromRecord approach. — Peter Tom, Jan 13 '22 at 15:30
Very strange. I found one post from 2003 having the same issue in Oracle with no resolution. I've not run into this issue before :( — JNevill, Jan 13 '22 at 16:35
I am also having issues with CopyFromRecordset with SQLite ODBC driver. Only returns one column when the recordset has 2. *update* This is solved here: https://stackoverflow.com/questions/49335585/how-to-retrieve-data-from-a-sqlite-database-in-vba-in-excel — langbourne, Jun 05 '23 at 10:13

score 0 · Answer 1 · answered Jan 14 '22 at 12:55

I figured out a workaround to solve this issue by retrieving each 254 records per iteration like this:

Private Sub CommandButton1_Click()
      query_Data "SELECT * FROM Test_Table;"
End Sub
Private Sub query_Data(strSQL As Variant)
    Cells.ClearContents
    Dim conn As Object
    Dim rst As Object
    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
        conn.Open "DRIVER=SQLite3 ODBC Driver;Database=E:\A disk\Lit\Data.db;"
        rst.Open strSQL, conn, 1, 1
                 rstCount= rst.RecordCount
                        For i = 1 To rstCount Step 254
                        Call Range("A" & i + 1).CopyFromRecordset(rst, 254)
                        Next
                rst.Close
        Set rst = Nothing: Set conn = Nothing
        Range("a1").Select
End Sub

Range("A1").CopyFromRecordset does not seem to work properly

1 Answers1