Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to exec SQL Server query w/o waiting

Posted on 1998-06-04
8
758 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 100 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 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