Display query results in spreadsheet type format

[BSman]

I am trying to rewrite something that originally worked in Access, then I rewrote to work in Access with ODBC connnection to SQL, and now need to work in Access Project. I have a form that allows users to build queries. It builds the SQL code and then lets them open the queries (detail query and a grouping version of the query) (the ODBC to SQL version builds pass through queries) where the results are displayed in a spreadsheet type of format.

I cannot write forms to display the results because I do not know what columns will be included, particularly from the grouping version of the query.

These are for read only displays. The users cannot change the data.

I have been able to run using a stored procedure that the SQL code is passed to and executed and also I have been able to create a recordset with the cmd function. But, neither method will display the results. I know the results are in the recordset because I display a record count as in the code below.

How can I get either the cmd function or the stored procedure to open as a spreadsheet within the VBA code? Note that if I click on the stored procedure in the database window and manually enter some SQL code, I do get the spreadsheet type of display of the data.

Here's the code I've experimented with:

Dim cmd As ADODB.Command
Dim rst As Recordset
Dim strSQL As String
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
cmd.ActiveConnection = CurrentProject.Connection
' cmd.CommandText = "Matching Records"
' cmd.CommandType = adCmdTableDirect
strSQL = "SELECT * FROM " & strTableName & " WHERE " & strWhere _
& " ORDER BY " & strOrderBy & ";"
cmd.CommandType = adCmdStoredProc
Dim par As New ADODB.Parameter
Set par = cmd.CreateParameter("@strSQL", adChar, adParamInput, 1000, strSQL)
cmd.Parameters.Append par
cmd.CommandText = "RunQuery"
' cmd.Execute
' MsgBox "executed command"
' Set rst = cmd.Execute
rst.Open cmd.Execute
' rst.Open strSQL
rst.MoveLast
rst.MoveFirst
MsgBox rst.RecordCount
rst.Close
Set rst = Nothing
Set cmd = Nothing

 

[chrissie1]

can we have the stored procedure?
its name is "matching records"?
What does it do?
and why only one parameter?

in short more information

Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[cmmrfrds]

How did you do this in the original Access.

"I cannot write forms to display the results because I do not know what columns will be included, particularly from the grouping version of the query."

How is this different than what you did in the mdb?

 

[BSman]

In the original Access/SQL that used ODBC I wrote a "pass through query". Basically, what I did was create the SQL statement (which was stored in the string variable strSQL). I then created a pass through query with the contents of strSQL as its text (and of course defined the connection string for the pass through query). I always used the same name for the query, so I would be deleting the query and then re-creating it. Then I would open it with the command
DoCmd.OpenQuery "MatchingRecords"

Where "MatchingRecords" is the name of the query.

Here's the SQL code to create the stored procedure (that I called "RunQuery&quot:

CREATE PROCEDURE RunQuery
@strSQL AS varchar(1000)
AS
EXEC @strSQL
RETURN


If you open this stored procedure directly in Access Project, it will prompt you for strSQL. Just type in valid SQL code and you will get the spreadsheet type of display of the results.

By the way, in the code I listed in my question, the remarked lines (') were from various things I tried. I should have deleted them.

 

[BSman]

I've found out that there you can open a stored procedure in code with the following VBA code:

DoCmd.OpenStoredProcedure "procname"

(There are options you can set also.)

The problem is that I can't find any place that shows a way to set parameters. So if I put this line into my code, I get the prompt to enter the parameter. In the case of the stored procedure I set up called "RunQuery", I am prompted for @strSQL, which requires that I enter the SQL code manually.

The procedure is then opened in the spreadsheet type of format, just like it is if you double click on the stored procedure in the database window.

 

[chrissie1]

i dont think that what you are trying to do is very good programming

normally when you want to what you want to do you use

exec sp_executesql @strsql

look at books online for more info on sp_executesql

or

exec (@strsql)

should work also Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[BSman]

If you look at the stored procedure, you will see that it does use

exec @strSQL

But how do you pass the value of @strSQL to the procedure so that when you execute the stored procedure through Access Project it will open in the spreadsheet format? In the original code I sent I had tried the statement

cmd.Execute

(which you can see is remarked out) and it didn't open to a spreadsheet.

Remember also that strSQL contains the SQL statement that I have built based on user input on a form for selection criteria and sorting. In addition, the form actually creates two SQL queries. One is the basic select order by statement. The second is a grouping query based on the sort selections by the user and performing calculations on the appropriate fields for the table or view that the user selected to perform the ad hoc query on. It has worked very well with Acces/ODBC to SQL and opens as a spreadsheet display format for each query as a result of creating pass through SQL queries. If I can get that same spreadsheet type display with Project, then I can move forward.

What I am calling spreadsheet format is what you get if you just open a table from the database window.

 

[BSman]

I've figured out a work around solution that (almost) works. The basic trick is to put the SQL text into a table, then execute a stored procedure that gets the text from the table and then executes it. I'll list the table info and the stored procedure code below, BUT I'm having a problem.

The first time I execute the stored procedure it works exactly like I want. But, the next time I execute it (where the SQL text in the table has been changed) it executes the old SQL query or something like it. However, if I go to SQL Query Analyzer in between the changes to the SQL text and execute the stored procedure there, it works exactly as it should. It appears that Access Project is executing the "old" version of the stored procedure, like it's not "initializing" the stored procedure every time it executes it. If I close Access and re-open it, the first time works correctly.

The table: PassingParameters. One column: SQLText nchar(1000).

The stored procedure, created with the following code:

CREATE PROCEDURE RunSQL AS
Declare @sqlText varchar(1000)
SELECT @sqlText = (SELECT SQLText FROM PassingParameters)
EXEC (@sqlText)
RETURN

 

[BSman]

A further note about the problem in using the stored procedure for a spreadsheet type display where the SQL code changes:

I've looked at this further and it appears that whatever was the format (i.e. columns in the datasheet display) from the first time the stored procedure was executed are the columns that Access tries to display whenever you execute the stored procedure again, even when the SQL code has changed. Columns that are not returned by the new code display blanks.

So it appears that there should be a way to force Access to treat the calling of the stored procedure as the first time it was called.

Anybody got an idea how to do this?