vbams-accessadpaccess-data-project

Dynamically create Datasheet from SQL query with VBA in Ms Office Access

I have Access database project connected with Ms SQL Server. Data (tables) is stored in the Ms SQL server and Forms and Reports are stored in the Access .ADP file. It is not possible to create Queries, Tables, Views using Design view but Tables and Views can be created using SQL queries and stored on the server. I don't have Ms SQL Server Management Studio and I can't install it in my computer in my office.

So, what I want is to get a dynamically generated Datasheet of a SELECT SQL query to see results temporarily for data analysis. I have placed a textbox and a button in a form and want to display a datasheet containing the result of the SQL query written in the textbox when the button is clicked.

I tried this but it is not working for me and doesn't seems what I want:
MS Access VBA - display dynamically built SQL results in datasheet subform

I also tried by assigning query to Recordsource property of a form. It is showing blank datasheet, but the navigation pane below the datasheet is showing the actual number of records retrieved. So, it is working but not showing the data.

I tried (from http://www.pcreview.co.uk/forums/create-query-dynamically-vba-t3146896.html):

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "select * from analysts"
Set qd = db.CreateQueryDef("NewQueryName", strSQL)
DoCmd.OpenQuery "db.NewQueryName"

It is showing run-time error 91, Object variable or With block variable not set on the line Set qd = db....

And also (from the same page):

Dim strSql As String
strSql = "select * from analysts"
CurrentDb.QueryDefs("qryExport").SQL = strSql
DoCmd.OpenQuery "qryExport"

Returning same error on the line CurrentDb.QueryDefs.....

Any idea or workaround?

Solution

  • This doesn't seems efficient, but it is working and a bit satisfactory:

    DoCmd.RunSQL "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS " & _
     "WHERE TABLE_NAME = 'tv') DROP VIEW tv"
DoCmd.RunSQL "create view tv as " & txtQry
DoCmd.OpenView "tv"

    Here I am creating a temporary VIEW (tv) in a button's click event. Before creating the view, I am checking that if a view with the same name exist or not; and if it exist then delete it so that a new view can be created with the same name with different query.