pvs001d
asked on
How to exec SQL Server query w/o waiting
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.
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.
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.
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.
A good book for details is "Hitchiker's Guide to Visual Basic & SQL server" by W. R. Vaughn.
ASKER
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.
Use Doevents to return control to the O/S.
Devtha
Devtha
ASKER
Example?
The syntax is
Dim Q as QueryDef
(Make or get a querydef for the pass through query)
Q.Execute dbRunAsync
Dim Q as QueryDef
(Make or get a querydef for the pass through query)
Q.Execute dbRunAsync
ASKER
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=;D ATABASE=ca llsys"
myqry.SQL = "EXECUTE sp_notify"
myqry.ReturnsRecords = False
myqry.Execute dbRunAsync
At this point I get "Invalid argument" error (No 3001)
Comments?
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=;D
myqry.SQL = "EXECUTE sp_notify"
myqry.ReturnsRecords = False
myqry.Execute dbRunAsync
At this point I get "Invalid argument" error (No 3001)
Comments?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.