View a recordset as a data sheet

Question

I have a database project written by someone else that uses a large number of SQL strings that are constructed on the basis of a series of elaborate IF/THEN or CASE/ELSE decision trees.

In order to understand what is going on I capture the SQL and turn it into a tmporary querydef and then use doCMD.Openquery to see the data sheet.

My code looks like this

Dim dbs as Database

Dim qdf as Querydef

Dim StrSQL as String

stSQLr = "SELECT someData FROM  someTable"

dbs.createQueryDef("tmpInfo", strSQL)

DoCmd.OpenQuery "tmpInfo"

Response = msgbox("Close tmpInfo sheet?", vbYesNo)

If Response = 6 then

   DoCmd.Cose aCQuery, "tmpInfo"

   dbs.QueryDefs. Delete "tmpInfo"

   qdf.Close

   dbs.Close

End If

And this works fine for the first four or five sets of strSQLs.

Then I come to one strSQL  that returns one record from one field.

In this case the code runs and does not throw an error, but the data sheet does not appear.

However if I exit the Debug mode and go back to the current form, the tmpInfo query exists and if I execute it manually from the object explorer, then it displays as expected.

I also created a form with a listbox on it, with the datasource set to the queryDef "tmpInfo" 

On Load, the form sets the number of columns in the lstbox equal to the field count for the querydef "tmpInfo".

This form also works and also exhibits the same behavior: when called for that particular "tmpInfo" query it does not throw an error, but nothing happens.

However, if I exit the Debug mode and display the form manually, then it works as expected.

What is preventing DoCmd.OpenQuery "tmpInfo" or DoCmd.Openform "tmpInfo" from working from code when they will work manually?

Answer 1

I see the advantage of storing them under a different name so they can be seen concurrently, but that isn't necessary for this exercise (Might be useful if this gets more complicated).

My solution for the visibility problem was to open another instance of the database on another machine, Then I step through until the querydef is created and execut it on the other machine.  That way I can undertand how he manipulated the data to get to the final result.

In some cases he had results stored in a temporary table, so all I need is

doCommand.openTable, "tableName"

But when I issue this or the open query command the screen flashes, but no table.

Yet If I go to the other machine and open the table, no problem.

Answer 2

So My question is what could allow it to display in some cases and prefent it in another?

 

I've no idea I'm afraid.  However, you are not correct in stating:

Your line : Set qdf = CurrentDb.CreateQueryDef("Temp" & n) is similar to what I am doing. mine ;looks like Set qdf = dbs.CreateQueryDef("Temp" , tmpSQL)

In my case because the variable n is declared as Static each time the function is called a new querydef object is established under a different name, allowing the resulting datasheets to be displayed concurrently.

I'd suggest you try calling my function to see if the problem persists.  It may well do so, but on the other hand you might be lucky.

Answer 3

You are correct, I had errors in what I showed you

Your line :

 Set qdf = CurrentDb.CreateQueryDef("Temp" & n)

is similar to what I am doing. mine ;looks like

Set qdf = dbs.CreateQueryDef("Temp" , tmpSQL)

followed by

DoCmd OpenQuery "Temp".

And I can also delete the temporary query when done.

In my case the SQL string is is provided in the code I inherited, but some of it is pretty dense.

The previous author used recordsets extensively, which I cannot see, so all I need is to create a temporary query out of his string and then run the query so I can examine the results.

I have this set up as a subroutine

ShowQDF (tmpSQL)

I capture his SQL string as  tmpSQL and then add the line that calls my subroutine.

It works five times in a row and the temporary query is deleted each time and then a new one is made each time the previous author displays a new SQL string.

 But then the code stops working. that is it runs and does not throw an error, but it does not display the data sheet. However, if I stop the code running (before the temporary query is deleted)  and go out and run the query manually, the data sheet appears as expected.

So My question is what could allow it to display in some cases and prefent it in another?

Answer 4

I can only assume that the code you posted is not the complete code as it is declaring object variables but not assigning any objects to them.  There is also a spelling error in DoCmd.Cose aCQuery, "tmpInfo".

You might find the following function helpful.  You can pass an SQL statement as a string expression into the function to open the query in datasheet view.  Calling the function repeatedly, passing in a different SQL statement each time, will open multiple datasheets:

Public Function OpenTempQuery(strSQL As String)

Dim qdf As DAO.QueryDef

Static n As Integer

n = n + 1

' delete temporary querydef object if exists

On Error Resume Next

CurrentDb.QueryDefs.Delete "Temp" & n

Select Case Err.Number

Case 0

' no error

Case 3265

' temporary querydef does not exist

' ignore error

Case Else

' unknown error

MsgBox Err.Number, vbExclamation, "Error"

End Select

' create temporary querydef object

Set qdf = CurrentDb.CreateQueryDef("Temp" & n)

CurrentDb.QueryDefs("Temp" & n).SQL = strSQL

' open query and then delete temporary querydef object

DoCmd.OpenQuery "Temp" & n

' delete temporary querydef object

CurrentDb.QueryDefs.Delete "Temp" & n

End Function