What is the Adodb Recordset in Excel?
A recordset is like a tool used in computer programming (especially in languages like VBA) to handle and work with sets of data, often obtained from a database. It helps with tasks like looking at the data, making changes to it, and asking questions about it. In Excel, when you connect to other databases like Access or SQL Server, you might use a recordset to manage and manipulate the information effectively. This is typically done using technologies like ActiveX Data Objects (ADO).
How to Resolve Recordset Error with More Than 65536 Rows in Excel?
When working with ADODB to extract data from relational databases like SQL, Access, or Excel tables, you may encounter a limitation of only 65536 rows if you use table references like [Sheet$A1:D65537].
However, by ensuring that your table starts from cell A1, you can read the entire set of rows by referencing it as [Sheet1$]. This approach allows you to successfully retrieve and display the complete set of 1048765 records.
In this article, we provide a solution for overcoming this issue. Follow the step-by-step process outlined here to learn how to extract the entire table from Excel, and you can apply the same method to other relational databases.
Also See: How to Connect Excel to SQL Server Using VBA
Here are the Steps to Run the Below Query:
Step 1: Go to Developer > Visual Basic > Tools > References and select the latest “Microsoft ActiveX Data Objects 6.1” based on your Microsoft Office version.
Step 2: Create a table in “Sheet1” or the desired location.
Step 3: Navigate to Insert > Module.
Step 4: Copy and paste the macro provided below into the module you inserted.
Sub ADODB_RECORDSET_QUERY ()
Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strQuery As String
Dim ws_1 As Worksheet
Dim ws_2 As Worksheet
Set ws_1 = ThisWorkbook.Worksheets("Sheet1")
Set ws_2 = ThisWorkbook.Worksheets("Sheet2")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties = ""Excel 12.0 Macro;HDR=Yes"";"
'strQuery = "SELECT * FROM [Sheet1$A1:C65537]" 'Error
strQuery = "SELECT * FROM [Sheet1$]" 'Resolved
Set rs = con.Execute(strQuery)
ws_2.Range("A2").CopyFromRecordset rs
con.Close
Set rs = Nothing
Set con = Nothing
End Sub
Step 5: Click on the button to run the macro.
Step 6: Check “Sheet2” or the designated location where you retrieved the data.
Conclusion
Now, you will be able to retrieve the entire table from the relational database or Excel tables. This article has helped you find a solution. If you have any doubts or queries, feel free to comment in the box below. I will be happy to answer your questions.
