asked
Run a SQL Server Stored Procedure from MS Access2010
I have a SP that builds a temporary table I want to run this from MS Access however I cant work this out. My SP does not return any records to Access nor does it have any parameters from Access or anywhere else. It purely checks to see if the #Tempfile exists and DROPS it if it does exist then SELECTS * INTO #TempTable FROM MyView > IF OBJECT_ID('tempdb..#SalesData','U') > IS NOT NULL DROP TABLE #SalesData > > SELECT * INTO #SalesData FROM > vw_ActiveSalesData_V2 I could just run a pass-through query within Access but it then locks MS Access up for 20 seconds whilst the query is running Any ideas
The table temporary table is being built in advance of any reports that might need it. There are plenty of other reports that do not require this particular table, so Access should be able to run without this. Obviously there is the issue that there may be a time when a user skips through the various options/tabs in the database quick enough to try and run a report that uses the data from this temporary table, however for the sake of smoothness and user experience it would be better if Access didn't lock up for this period of time first. The reason for building this table is that it is very complex and before this some reports took 30 mins to run because of the nature of the view and aggregating values. I have reduced this to 20 seconds (in development) by using a temporary table. I real life it would take the user at least 15 seconds to get to the part in Access where they could run it. OK that's a 5 second danger zone, but if anyone has any other ideas fill me in.
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
Why not to use BCP out/IN or import/export or ssis as a solution to overcome this. Let's know if you need more details.
Hi VishalhSingh Sorry you lost me at BCP out/IN or import/export or ssis
You could have a code module run the SP in its own thread. Another option would be to create a SQL Agent job on the serverside, with a job step running the SP, and have your pass through query start the job. Though I'm still not so sure why you want to use a temp-table...
Hi Magnus My reason for using temporary tables is so that multiple users do not conflict with each other. I had been running a set of queries that ran and were supposed to pull together a data set based on the results of each other, but somehow it did not always work. and when it did it was extremely slow. By using a temporary table (#table) each user has their own instance of the table ready to use when they need it. From my limited understanding, as I am using Access as my front end, each instance of Access will see its own temporary table. So far my heaviest report has now gone from 30 minutes as a view with aggregated values to 30 seconds to build a temporary and then querying that takes a second or so. I am under no delusion that there are far better ways to do what I am doing, I have only been using SQL server for around a year (self taught) and have just managed to get my company to send me on a 4 day course. We do have IT developers but they are not helpful which I why I rely so heavily on Google and forums like this one.
18 People are following this question.
