Display ADO recordset

[BillyRogers]

I've inherited maintenance responsiblity for an Access db that uses dynamic
SQL and passthrough queries. The code is very problematic and susceptible
to problems if a user tries to stop a query while running.

I want to use ADO and stored procedures to speed up the queries and prevent
problems. Currently the code is called from a button on a form and displayed
like a saved query--in a spreadsheet like grid. I want the recordset
returned from the stored procedure to show up just like a regurlar saved
query in access when the user presses the button. Is this possible? I know
how to make the stored procedure and just don't know how to display it in a
grid like a saved query.

I'm hoping this is possible so the users won't notice any difference except
that it runs fasters and crashes less often.


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Rick Brandt]

I've inherited maintenance responsiblity for an Access db that uses
dynamic SQL and passthrough queries. The code is very problematic
and susceptible to problems if a user tries to stop a query while
running.

I want to use ADO and stored procedures to speed up the queries and
prevent problems. Currently the code is called from a button on a
form and displayed like a saved query--in a spreadsheet like grid.
I want the recordset returned from the stored procedure to show up
just like a regurlar saved query in access when the user presses the
button. Is this possible? I know how to make the stored procedure
and just don't know how to display it in a grid like a saved query.

I'm hoping this is possible so the users won't notice any difference
except that it runs fasters and crashes less often.

Just call the StoredProcedure in a Passthrough query. The results will be shown
in a grid "just like a saved query" because it will actually be a saved query.

 

[BillyRogers]

Great! thanks for the help.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Rick Brandt]

One thing I forgot to mention is that there are parameters that need
to be passed to the stored procedures based on account numbers
entered into text boxes on the form.

How do I pass the parameters to the stored procedure in the
passthrough query?

I just change the SQL each time...

CurrentDB.QueryDefs("QueryName").sql = "EXEC..."

 

[Rick Brandt]

That's the problem with the way it is now. It takes a long time to
run so people try to break out of the query and the code stops and
the passthrough gets saved with the account number stuck in the where
clause. It never gets to the part of the code that clears out the
where clause and the next time it is run it has two where clauses and
crashes.

I don't just add a WHERE clause to the existing SQL. I completely replace the
existing SQL with all new SQL that does what I want. That way what you describe
is not a problem.

Any program that is dependant on a previous process "cleaning up after itself"
as you are describing is doomed to failure.

 

[Rick Brandt]

I'll probably have to do it that way. I was just hoping I could use
stored procedures to speed things up and make it easier If I ever
have to transition the app to another environment such as a webpage.

You can use stored procedures. You just supply the entire syntax for
calling the SP rather than just supplying the parameters. You can easily
make a user defined funtion that will take just the parameters and do the
rest for you.