By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,512 Members | 3,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,512 IT Pros & Developers. It's quick & easy.

executing stored procedure in vb Access97

P: n/a
Hi All,

I've a serious problem with executing stored procedures (SQL2000) from
an Access db (version 97). After executing a stored procedure ,
msaccess hangs. The whole call to the procedure is running fine, but
immediatly after this msaccess hangs, when the focus goes back to the
calling form ?
Has anyone any idea what i can test or look at.

Thanks a lot !!!!!!

CODE*************
Function DownloadData() 'This is the function executed when clicking
on a btn on form
Dim RetVal

RetVal = Execute_Procedure("sp_DownloadData", "", False)
MsgBox "All data has been downloaded "
'UNTIL EVERYTHING IS WORKING FINE BUT WHEN GOING OUT THIS FUNTION
THE ACCESS APPLICATION START USING 100% CPU AND HANGS

End Function


Public Function Execute_Procedure(iProcedure As String, iArgs As
String, iIndicator As Boolean) As Boolean
Dim t1, t2
t1 = Timer

DoCmd.Echo False, "Executing procedure " & iProcedure & " " &
iArgs
Dim oCn As ADODB.Connection
Set oCn = New ADODB.Connection

oCn.ConnectionString = gCn
oCn.Open
oCn.CommandTimeout = 0

Dim oCmd As ADODB.Command
Set oCmd = New ADODB.Command

oCmd.CommandTimeout = 0
oCmd.CommandType = adCmdStoredProc

oCmd.CommandText = iProcedure & iArgs

oCmd.ActiveConnection = oCn

oCmd.Execute , , adExecuteNoRecords

oCn.Close

t2 = Timer

DoCmd.Echo False, iProcedure & " executed in " & CInt((t2 - t1)) &
" seconds"

Set oCmd = Nothing
Set oCn = Nothing

Execute_Procedure = True

End Function
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Well, unless your code turns the echo back on, the echo is still off, and your
application will appear to hang. Turning the echo off is a somewhat sensitive
operation for this reason.

On 4 Aug 2004 23:58:11 -0700, rv**@skynet.be (rvdw) wrote:
Hi All,

I've a serious problem with executing stored procedures (SQL2000) from
an Access db (version 97). After executing a stored procedure ,
msaccess hangs. The whole call to the procedure is running fine, but
immediatly after this msaccess hangs, when the focus goes back to the
calling form ?
Has anyone any idea what i can test or look at.

Thanks a lot !!!!!!

CODE*************
Function DownloadData() 'This is the function executed when clicking
on a btn on form
Dim RetVal

RetVal = Execute_Procedure("sp_DownloadData", "", False)
MsgBox "All data has been downloaded "
'UNTIL EVERYTHING IS WORKING FINE BUT WHEN GOING OUT THIS FUNTION
THE ACCESS APPLICATION START USING 100% CPU AND HANGS

End Function


Public Function Execute_Procedure(iProcedure As String, iArgs As
String, iIndicator As Boolean) As Boolean
Dim t1, t2
t1 = Timer

DoCmd.Echo False, "Executing procedure " & iProcedure & " " &
iArgs
Dim oCn As ADODB.Connection
Set oCn = New ADODB.Connection

oCn.ConnectionString = gCn
oCn.Open
oCn.CommandTimeout = 0

Dim oCmd As ADODB.Command
Set oCmd = New ADODB.Command

oCmd.CommandTimeout = 0
oCmd.CommandType = adCmdStoredProc

oCmd.CommandText = iProcedure & iArgs

oCmd.ActiveConnection = oCn

oCmd.Execute , , adExecuteNoRecords

oCn.Close

t2 = Timer

DoCmd.Echo False, iProcedure & " executed in " & CInt((t2 - t1)) &
" seconds"

Set oCmd = Nothing
Set oCn = Nothing

Execute_Procedure = True

End Function


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.