1

I've been unable to find a good example of how to execute a SELECT SQL from vba and display the results as a datasheet. I have a query with 31 fields. I'm attempting to build a form which will allow the user to select the fields they want to see in the results, rather than building a bunch of stored queries that might never be used. I'm starting small... I have a form built with 3 option buttons which I am using to build the fields I want to display. The SQL looks correct, and I have been able to get the SQL to run in the past, but have never had it display the results as a datasheet. I would GREATLY appreciate any help you can provide.

Private Sub btn_RunQuery_Click()

Dim int_build_fields As Integer
Dim str_fields As String
Dim int_length As Integer
Dim rs_query As Recordset
Dim str_SQL As String
Dim str_List As String

str_fields = ""

If opt_Jobname Then
    str_fields = str_fields & ", Jobname"
End If

If opt_CycleDate Then
    str_fields = str_fields & ", CycleDate"
End If

If opt_EndTime Then
    str_fields = str_fields & ", EndTime"
End If

'Remove comma and space from first field
    int_length = Len(str_fields)
    str_fields = Right(str_fields, (int_length - 2))

str_SQL = " SELECT " & str_fields & " FROM UnionWithJobnamesAndGeneralStats"

MsgBox "str_SQL = " & str_SQL

Set rs_query = CurrentDb.OpenRecordset(str_SQL)

'Not sure how to execute & display the SQL here...

rs_query.Close
Set rs_query = Nothing

MsgBox "Done"

End Sub
E_Allen
  • 11
  • 2
  • 2
    Possible duplicate of [How to view a recordset in an access table by means of vba?](https://stackoverflow.com/questions/5182087/how-to-view-a-recordset-in-an-access-table-by-means-of-vba) – Andre Aug 14 '17 at 13:43

1 Answers1

-1

You database connection "CurrentDb" is not defined and set-up?

Don't forget to close you connection ;)

check Accessing SQL Database in Excel-VBA for more info.

Teun Gelder
  • 9
  • 5
  • 2
    Eh... it's not Excel VBA, it's Access VBA, so currentDb is always set, and you don't need to create a connection to the database you're accessing, nor do you need to close it. – Erik A Aug 14 '17 at 15:24