Stored procedure

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have this problem and I hope that someone can help me.



When I create a stored procedure in access Xp with this command:



Set cnn = CurrentProject.Connection

criteria = "create procedure " & ProcedureName & " as select * from " &
Txt35 & " where " & Txt38

cnn.Execute (criteria)
cnn.Close



and I execute the procedure



DoCmd.OpenStoredProcedure ProcedureName, acViewNormal, acReadOnly



I get an error. Access tells me that there is no procedure with that name
"ProcedureName" .



If I have a look in SQL Server I will find the procedure with that name and
it will work fine.



I think that this happens because the procedure name is not in the adp
database.



How can I solve this problem?



Thanks in advance



Marco Dell'Oca
 
It seems to be a caching thing. Resetting the connection between creating
and accessing the stored procedure seems to fix it. For example ...

Public Sub CreateSproc()

Const strcConnection As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=(local)"

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = strcConnection
cnn.Open
cnn.Execute "CREATE PROCEDURE TestSproc AS SELECT * FROM Employees"
cnn.Close

Set cnn = New ADODB.Connection
cnn.ConnectionString = strcConnection
cnn.Open

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "EXEC TestSproc"
.Open
Debug.Print rst.Fields(0)
.Close
End With

cnn.Close

End Sub
 
Thanks Brendan for the help,



I know that I can read the recordset resetting the connection.

What I need is to open the stored procedure on the client screen with a
command

like this:



DoCmd.OpenStoredProcedure ProcedureName, acViewNormal, acReadOnly



The strange is that I can not open the stored procedure recordset bat I can
export it with the command:



DoCmd.OutputTo acOutputStoredProcedure, ProcedureName, , , True



Isn't It?



Marco Dell'Oca
 
I'm afraid I don't have an answer to that one. Could you use a form in
datasheet view to display the result rather than using OpenStoredProcedure?
If so, the following worked for me (in the form's class module) ...

Option Compare Database
Option Explicit

Dim m_cnn As ADODB.Connection
Dim m_rst As ADODB.Recordset

Private Sub Form_Close()

If Not m_rst Is Nothing Then
If m_rst.State <> adStateClosed Then
m_rst.Close
End If
End If
If Not m_cnn Is Nothing Then
If m_cnn.State <> adStateClosed Then
m_cnn.Close
End If
End If

End Sub

Private Sub Form_Open(Cancel As Integer)

Const strcConnection As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=(local)"

Set m_cnn = New ADODB.Connection
m_cnn.ConnectionString = strcConnection
m_cnn.Open
On Error Resume Next
m_cnn.Execute "DROP PROCEDURE TestSproc "
On Error GoTo 0
m_cnn.Close

Set m_cnn = New ADODB.Connection
m_cnn.ConnectionString = strcConnection
m_cnn.Open
m_cnn.Execute "CREATE PROCEDURE TestSproc AS SELECT * FROM Employees"
m_cnn.Close

Set m_cnn = New ADODB.Connection
m_cnn.ConnectionString = strcConnection
m_cnn.Open
Set m_rst = New ADODB.Recordset
With m_rst
Set .ActiveConnection = m_cnn
.CursorLocation = adUseClient
.Source = "EXEC TestSproc"
.Open
End With

Set Me.Recordset = m_rst

End Sub
 
Hi Mark,
You must refresh the storedprocedures window
----

Set cnn = CurrentProject.Connection
criteria = "create procedure " & ProcedureName & " as select * from " &
Txt35 & " where " & Txt38

cnn.Execute (criteria)
cnn.Close

DoCmd.RunCommand acCmdViewStoredProcedures
Application.RefreshDatabaseWindow

DoCmd.OpenStoredProcedure ProcedureName, acViewNormal, acReadOnly
 
Many many thanks Brendan,

I think I will choose the Giorgio Rancati Idea.

Thanks again



Marco Dell'Oca
 
Hi Giorgio

In a few days you have solved me two problems.



Perhaps one day I will do some finks for you (I hope)



Many thanks



Marco Dell'Oca


giorgio rancati said:
Hi Mark,
You must refresh the storedprocedures window
----

Set cnn = CurrentProject.Connection
criteria = "create procedure " & ProcedureName & " as select * from " &
Txt35 & " where " & Txt38

cnn.Execute (criteria)
cnn.Close

DoCmd.RunCommand acCmdViewStoredProcedures
Application.RefreshDatabaseWindow

DoCmd.OpenStoredProcedure ProcedureName, acViewNormal, acReadOnly
----

bye
--
Giorgio Rancati
[Office Access MVP]

Mark said:
I have this problem and I hope that someone can help me.



When I create a stored procedure in access Xp with this command:



Set cnn = CurrentProject.Connection

criteria = "create procedure " & ProcedureName & " as select * from
Click to expand...
"
&
Txt35 & " where " & Txt38

cnn.Execute (criteria)
cnn.Close



and I execute the procedure



DoCmd.OpenStoredProcedure ProcedureName, acViewNormal, acReadOnly



I get an error. Access tells me that there is no procedure with that name
"ProcedureName" .



If I have a look in SQL Server I will find the procedure with that name and
it will work fine.



I think that this happens because the procedure name is not in the adp
database.



How can I solve this problem?



Thanks in advance



Marco Dell'Oca
Click to expand...
Click to expand...
 
Back
Top