Populating a Range from a Recordset - MS Access VBA

[Shiznaw]

This is the fastest way I've found to populate a range from a DAO.Recordset without having to move thru the recordset via a For Next Loop.
Public Sub Trial()
Dim rng as Range
Dim wrksht as Worksheet
Dim rst as Recordset (DAO.Recordset)
Dim strSQL1 as String

strSQL1 = "SELECT

.* FROM

;" '<-MS Access SQL Syntax

Set rst = db.OpenRecordset(strSQL1, dbOpenDynaset)
rst.MoveLast
MsgBox "Number of records: " & rst.RecordCount, , Title:="Number of Records Returned" 'msgbox denoting how many records returned
rst.MoveFirst

Set rng = wrksht.cells(2,1)
rng.CopyFromRecordset rst, rst.RecordCount
End Sub

 

[dhookom]

Hi Shiznaw and welcome to Tek-Tips.

Is this a question or simply an observation? I don't see any code that sets the wrksht object.

Also, it's best to include TGML to format your code like this:

Public Sub Trial()
  Dim rng as Range
  Dim wrksht as Worksheet
  Dim rst as Recordset (DAO.Recordset)
  Dim strSQL1 as String

  strSQL1 = "SELECT [TABLE].* FROM [TABLE];" [COLOR=#4E9A06]'<-MS Access SQL Syntax[/color]

  Set rst = db.OpenRecordset(strSQL1, dbOpenDynaset)
  rst.MoveLast
  MsgBox "Number of records: " & rst.RecordCount, , Title:="Number of Records Returned" 'msgbox denoting how many records returned
  rst.MoveFirst

  Set rng = wrksht.cells(2,1)
  rng.CopyFromRecordset rst, rst.RecordCount
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016