?
Solved

How to exec SQL Server query w/o waiting

Posted on 1998-06-04
8
Medium Priority
?
811 Views
Last Modified: 2012-08-14
How can I run a query against SQL Server and return control to user without waiting for the query to finish.
What I want  to  call stored procedure that does some processing on the server and is not returning any results to the client. So ideally user should be able just click the button on the form ,that runs pass-through 'EXECUTE sp_process_data' query and forget about it.
0
Comment
Question by:pvs001d
8 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 1974833
My understandin is that Access runs on the client, and is therefore a remote system rather than a true Client/Server - but maybe someone else can enlare on this.
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1974834
Although the DAO/Jet engine that is access inherently runs in an asynchronous mode using ODBC, that is it issues a command without waiting, and polls for completeion, that asynchronous control is not available to you in your software.  You will either need to do it using the ODBC API, or an easier mode is to use a VBA module and RDO access method.  This means you also have to have VB5 Enterprise edition to get the RDO 2.0 control.

A good book for details is "Hitchiker's Guide to Visual Basic & SQL server" by W. R. Vaughn.
0
 

Author Comment

by:pvs001d
ID: 1974835
Are you sure that it can't be done in access only? I've read somewhere,that it is possible in Access'98 using ODBCDirect, but have not seen any explanation how to do it.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 6

Expert Comment

by:devtha
ID: 1974836
Use Doevents to return control to the O/S.
Devtha
0
 

Author Comment

by:pvs001d
ID: 1974837
Example?
0
 
LVL 4

Expert Comment

by:tomook
ID: 1974838
The syntax is
Dim Q as QueryDef

(Make or get a querydef for the pass through query)

Q.Execute dbRunAsync

0
 

Author Comment

by:pvs001d
ID: 1974839
Dim ws As Workspace
Dim dbs As Database
Dim myqry As QueryDef
Set ws = DBEngine.Workspaces(0)
        Set dbs = ws.Databases(0)
Set myqry = dbs.CreateQueryDef("")
            myqry.ODBCTimeout = 300
            myqry.Connect_
 =ODBC;DSN=DNET;UID=;PWD=;DATABASE=callsys"
            myqry.SQL = "EXECUTE sp_notify"
            myqry.ReturnsRecords = False
            myqry.Execute dbRunAsync
At this point I get "Invalid argument" error (No 3001)
Comments?

0
 
LVL 4

Accepted Solution

by:
tomook earned 300 total points
ID: 1974840
'dbRunAsync' is valid only in ODBC Direct workspaces. In Access, Workspace(0) is always Jet. I have a snippet of code which may solve your problem.

Dim ws As Workspace
Dim myconn As Connection
Dim myqry As QueryDef
   
Set ws = DBEngine.CreateWorkspace("A", "Admin", "", dbUseODBC)
Set myconn = ws.OpenConnection("TestConnection", dbRunAsync, False, "ODBC;DSN=DNET;UID=;PWD=;DATABASE=callsys")
Set myqry = myconn.CreateQueryDef("", "EXECUTE sp_notify")  
myconn.Execute "EXECUTE sp_notify", dbRunAsync
Set myconn=Nothing
Set ws=Nothing

This opens a new ODBC Direct workspace (you could have one already opened, but this is just an example) and runs the query text directly on it. Note the asynchronous option. I tried this on our SQL Server and it works.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question