How to save Access query result into an object in VBA?

I'm not able to write the whole result of an Access query to an object.

Here is a sample code.

Sub test()

Dim cn As Object
Dim strConnection As String
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.mdb;Jet OLEDB:Database Password=pass;"

cn.Open strConnection

'Write some test data
cn.Execute ("CREATE TABLE TestTable (ID int, Column_1 varchar(255), Column_2 varchar(255));")
cn.Execute ("INSERT INTO TestTable (ID, Column_1, Column_2) VALUES (1, ""Foo"", ""Bar"")")
cn.Execute ("INSERT INTO TestTable (ID, Column_1, Column_2) VALUES (2, ""Bar"", ""Foo"")")
cn.Execute ("INSERT INTO TestTable (ID, Column_1, Column_2) VALUES (3, ""FooBar"", ""BarFoo"")")

'Get the data
Set rs = cn.Execute("SELECT * FROM TestTable")

cn.Close

End Sub

It seems I'm getting only the first row when checking rs on the watchlist, having Item 1 = 1, Item 2 = Foo, Item 3 = Bar under the Fields.

On Python with PyODBC I'm used to use the fetchall method, but I don't find similar for ADODB.

On Access the query returns all the 3 rows.

Is the object type wrong? I tried also "Set rs = New ADODB.Recordset" but it did help either. So how can I write all 3 rows to the object?

The rough equivalent to FetchAll is GetRows:

Dim cn As Object
Dim strConnection As String
Dim rowdata As Variant

Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.mdb;Jet OLEDB:Database Password=pass;"

cn.Open strConnection

'Write some test data
cn.Execute ("CREATE TABLE TestTable (ID int, Column_1 varchar(255), Column_2 varchar(255));")
cn.Execute ("INSERT INTO TestTable (ID, Column_1, Column_2) VALUES (1, ""Foo"", ""Bar"")")
cn.Execute ("INSERT INTO TestTable (ID, Column_1, Column_2) VALUES (2, ""Bar"", ""Foo"")")
cn.Execute ("INSERT INTO TestTable (ID, Column_1, Column_2) VALUES (3, ""FooBar"", ""BarFoo"")")

'Get the data
rowdata = cn.Execute("SELECT * FROM TestTable").GetRows()

cn.Close

Usually, working with the recordset is preferred, as recordsets only load data when needed (e.g. when you use rs.MoveNext, the next record gets loaded, instead of immediately loading it), and recordsets are linked to the data source, so can be used to insert new rows into your table. GetRows has very few use cases in VBA.

First, I don't recommend GetRows unless you want to go from a really nice fantastic recordset object with nice fields, and movenext etc to that of a multi-dimensional array which is hard to work with.

Next up:

When you use rs.execute, you get a forward only recordset, and you can ONLY check recordcount for existing records (it will always be -1). So, you can ONLY check for records by using .recordCount - not get an actual count of records.

Because of above, you want to specify the "cursor" type in the open command.

So, use:

Dim strSQL     As String

strSQL = "select * from tblHotels where City like 'Edmon%' "

rstD.CursorLocation = adUseClient
rstD.Open strSQL, rstDCon
Debug.Print rstD.RecordCount

There are several other ways to set the cursor (recordset) type, you could also just likey go:
rstD.CursorLocation = adUseClient, but the Execute command will override the setting in most cases. Since you need the extra line of code, then just use the .Open method of the recordset to do this.

So:

'Get the data
Set rs = cn.Execute("SELECT * FROM TestTable")

Becomes

'Get the data
rs.Open "SELECT * FROM TestTable", cn, adUseClient
debug.print rs.RecordCount

It not clear if you are using late binding, and thus you have to replace the constant adUseClient with 3

eg:

'Get the data
rs.Open "SELECT * FROM TestTable", cn, 3
debug.print rs.RecordCount