Access Running a Stored Procedure 3

[dmlatham]

Good Day,

I have a stored procedure (SQL Server 8) that runs for about 2-3 minutes. If I try to run the stored procedure using Access, I get a timeout error. Is there any way around this?

All help is appreciated,

Deborah

 

[jimmythegeek]

How are you running it from Access? Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[RobertT687]

If you are executing it as a Pass-Thru Query, set the ODBC timeout on the query to something higher than the 60 second default or set it to zero (0) for no time limit.

 

[scking]

Stored Procedures in Access which were implemented as an extension and use ADO to run are something of a cluge job. I wrote a subroutine to build a stored procedure using ADO and discovered it saved as a standard query. Also, I was then able to run standard queries using ADO as stored procedures. However, are you sure your copy of Access has the extensions? I don't believe they were incorporated until Access 2000. Maybe your symptoms are because the version you are using won't support stored procedures. This works for me. I've only provided the part you need for ADO stored procedures.

Steve King

Public Function CreateReportFromQuerySP(DbPath As String, _
Proc As String, _
Optional Param1 As String) As Boolean

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim prmParameter As ADODB.Parameter
Dim ws As Worksheet
Dim lngSize As Long
Dim iCols As Integer
On Error Resume Next

' Open the connection.
Set cnnConn = New ADODB.Connection

' If the DbPath was provided verify it
' otherwise open from the default database.
If Len(DbPath) > 0 Then
' Check to verify that the database exists at the location stated
' in the input parameter
If Len(Dir(DbPath)) > 0 Then
With cnnConn
'.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DbPath & ";" _
& "Jet OLEDB:System Database=C:\Dmats\system2000.mdw;" _
& "Jet OLEDBatabase Password=guest"
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DbPath
.CursorLocation = adUseServer
End With
Else
MsgBox "Invalid database path (" & DbPath & &quot"
Exit Function
End If
Else
If Len(Dir(DefaultDbPath)) > 0 Then
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DefaultDbPath & ";UID=guest;PWD="
End With
Else
MsgBox "Invalid database path (" & DefaultDbPath & &quot"
Exit Function
End If
End If

On Error GoTo HandleErr

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn

If Len(Param1) > 0 Then
Set prmParameter = cmdCommand.CreateParameter("MyParam", adVariant, _
adParamInput, Len(Param1))
cmdCommand.Parameters.Append prmParameter
prmParameter.Value = Param1
End If

With cmdCommand
.CommandText = Proc
.CommandType = adCmdStoredProc
.Execute
End With

If cnnConn.Errors.Count > 0 Then
Err.Raise 10621, "cnnConn", "Error Returns following execute."
End If Growth follows a healthy professional curiosity

 

[jimmythegeek]

scking,

I don't think it is a version thing, because I run code similar to yours all the time from Access 97. As long as you have the proper reference, the code should be identical in the versions. Works great for me. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[scking]

Jim

In Access 2000/2002 there is an option. The Tables/Queries tab allows you to set the 'SQL Server Compatible Syntax (ANSI 92)' for the database. Since mine is not checked AND I am also accessing Access "stored procedures" from Excel using ADO, I'm not sure what you get with the option checked. I used Access 97 for years without having to use any of the features of ANSI 92.

Steve King Growth follows a healthy professional curiosity

 

[dmlatham]

Thanks for all of the responses.

I am using ADO code to call the stored procedure. Here is a sample of my code:

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strConnectString As String
'------------------------------------------------------------
' make a connection
strConnectString = "Provider=sqloledb;" & _
"Data Source=Server1;" & _
"Initial Catalog=Catalogt;" & _
"User Id=User1;Password=;"
'------------------------------------------------------------
'creates and opens a Command object
Set cmd = New ADODB.Command
cmd.ActiveConnection = strConnectString
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_Test"
'Use a Recordset object to hold the results of executing the stored procedure
cmd.Execute ( at this point, I recieve a timeout expired error)


The stored procedure creates tables for our reports staff to access. So they just have to be able to run it when need be. The stored procedure takes around 2-3 minutes to run.

Any advice?

 

[jimmythegeek]

I have had trouble before by not have the ".1" in the Provider parameter of the connection object. Try the following and see if it makes a difference.

=========================================
Dim strConnectString As String
Dim cmd As ADODB.Command

strConnectString = "Provider=SQLOLEDB.1;Persist Security Info=false;User ID=User1;Password=;Initial Catalog=Catalogt;Data Source=Server1"

Set cmd = New ADODB.Command
with cmd
.ActiveConnection = strConnectString
.CommandType = adCmdStoredProc
.CommandText = "stp_Test"
.Execute
End With
========================================= Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[cmmrfrds]

Dim cn As new ADODB.Connection

cmd.CommandTimeOut = 500 '- default is 30 seconds
cn.CommandTimeOut = 500 '- default is 15 seconds

Either the command or the connection could be timing you out. Try expanding the connection time out first. A 0 will give infinite time but is probably not a good idea.

 

[dmlatham]

Thanks for the info. I will try this out Monday morning (6/19/02 est) and post back the results.

D

 

[dmlatham]

Thanks cmmrfrds!!!!!!!!!!!!!!!!!!!!!!!!

It was the command timeout. I had already tried the connection timeout and it wasn't working for me.

Again, thanks for all of your help and thanks to everyone for thier assistance.

Deborah