0

All I want to do is use VBA to get the results of some query into an Excel workbook instead. The main issue is that the openRecordSet method appears to not be working. Every time when I try debugging it I see that the recordset (rcset) is Nothing. When I just run the query so it is viewed in the Access viewer, it seems to work just fine (see last line of code). I get no errors when I run the code, so I'm having a very hard time understanding why my recordset would return Nothing. I've searched around the internet quite a bit, but haven't found anyone in this particular situation. Thanks in advance for any help! 

 Dim db As DAO.Database
  Dim qdef As DAO.QueryDef
  Dim rcset As DAO.Recordset
   Dim i As Integer


'Identify the database and query
Set db = CurrentDb
On Error Resume Next
  With db
    .QueryDefs.Delete ("RealQuery")
    Set qdef = .CreateQueryDef("RealQuery", strSQLRQ)
    .Close
End With

 'The problem child line
 Set rcset = qdef.OpenRecordset()

'Clear previous contents
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
  .Visible = True
  .Workbooks.Add
  .Sheets("Sheet1").Select
  'Copy the recordset to Excel
  .ActiveSheet.Range("A2").CopyFromRecordset rcset
'Add column heading names to spreadsheet
For i = 1 To rcset.Fields.Count
    xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With
qdef.Close
Set qdef = Nothing
Set db = Nothing
DoCmd.OpenQuery "RealQuery", acViewNormal
andruemily
  • 1
  • 1
  • 8
  • what is strsqlrq? also try this set rcset = db.OpenRecordset("Select * from RealQuery") – Krish Aug 05 '15 at 18:15
  • strsqlrq is just the SQL query in a string. It's a line above this block of code, and it is definitely working correctly. Tried the "Select * from RealQuery" but rcset is still Nothing. – andruemily Aug 05 '15 at 18:21
  • 1
    1) try not closing the database 2) remove your On Error Resume Next and see what errors you get. 3) confirm qdef is a reference to a Querydef when you call it. – Don Jewett Aug 05 '15 at 19:19
  • YES. That worked, and with a few more modifications it is working now. Thank you so much – andruemily Aug 05 '15 at 20:26

1 Answers1

1
Option Explicit
    Public Const strQry = "insert sql select command here"
    Public Const conStr = "insertconnectionstringhere";
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

Sub sql2excell()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open (conStr)
rs.Open strQry, cn

If Not rs.EOF Then
ActiveSheet.Range("A1").CopyFromRecordset rs
End If

rs.Close
cn.Close
End Sub

Simplest SQL to excel vba i can get

Przypadkiem
  • 11
  • 1