|
Home > Archive > MS Access project with SQL Server > April 2006 > Stored procedure
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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
| |
| Brendan Reynolds 2006-04-02, 8:29 pm |
|
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;Persis
t Security Info=False;" & _
"Initial Catalog=Northwind;Da
ta 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
--
Brendan Reynolds
Access MVP
"Mark" <dmsoftware@tin.it> wrote in message
news:442fadcc$0$3692
9$4fafbaef@reader3.news.tin.it...
>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
>
>
>
>
| |
|
| 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 acOutputStoredProced
ure, ProcedureName, , , True
Isn't It?
Marco Dell'Oca
"Brendan Reynolds" < brenreyn@discussions
.microsoft.com> wrote in message
news:u4tv5ApVGHA.4792@TK2MSFTNGP14.phx.gbl...
>
> 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;Persis
t Security Info=False;" & _
> "Initial Catalog=Northwind;Da
ta 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
>
> --
> Brendan Reynolds
> Access MVP
>
> "Mark" <dmsoftware@tin.it> wrote in message
> news:442fadcc$0$3692
9$4fafbaef@reader3.news.tin.it...
&[color=darkred]
name[color=darkred]
>
>
| |
| Brendan Reynolds 2006-04-03, 11:32 am |
|
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;Persis
t Security Info=False;" & _
"Initial Catalog=Northwind;Da
ta 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
--
Brendan Reynolds
Access MVP
"Mark" <dmsoftware@tin.it> wrote in message
news:4431187c$0$2972
2$4fafbaef@reader2.news.tin.it...
> 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 acOutputStoredProced
ure, ProcedureName, , , True
>
>
>
> Isn't It?
>
>
>
> Marco Dell'Oca
>
>
>
>
> "Brendan Reynolds" < brenreyn@discussions
.microsoft.com> wrote in message
> news:u4tv5ApVGHA.4792@TK2MSFTNGP14.phx.gbl...
> &
> name
>
>
| |
| giorgio rancati 2006-04-03, 11:32 am |
| 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 acCmdViewStoredProce
dures
Application. RefreshDatabaseWindo
w
DoCmd.OpenStoredProcedure ProcedureName, acViewNormal, acReadOnly
----
bye
--
Giorgio Rancati
[Office Access MVP]
"Mark" <dmsoftware@tin.it> ha scritto nel messaggio
news:442fadcc$0$3692
9$4fafbaef@reader3.news.tin.it...
> 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
>
>
>
>
| |
|
| Many many thanks Brendan,
I think I will choose the Giorgio Rancati Idea.
Thanks again
Marco Dell'Oca
"Brendan Reynolds" < brenreyn@discussions
.microsoft.com> wrote in message
news:OGl7pgzVGHA.5808@TK2MSFTNGP12.phx.gbl...
>
> 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;Persis
t Security Info=False;" & _
> "Initial Catalog=Northwind;Da
ta 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
>
> --
> Brendan Reynolds
> Access MVP
>
> "Mark" <dmsoftware@tin.it> wrote in message
> news:4431187c$0$2972
2$4fafbaef@reader2.news.tin.it...
creating[color=darkr
ed]
from[color=darkred]
name[color=darkred]
adp[color=darkred]
>
>
| |
|
| 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" < giorgio_No_Spalmer_r
ancati@tiscali.it> wrote in message
news:e6%23dzpzVGHA.3736@TK2MSFTNGP15.phx.gbl...
> 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 acCmdViewStoredProce
dures
> Application. RefreshDatabaseWindo
w
>
> DoCmd.OpenStoredProcedure ProcedureName, acViewNormal, acReadOnly
> ----
>
> bye
> --
> Giorgio Rancati
> [Office Access MVP]
>
> "Mark" <dmsoftware@tin.it> ha scritto nel messaggio
> news:442fadcc$0$3692
9$4fafbaef@reader3.news.tin.it...
"[color=darkred]
> &
name[color=darkred]
> and
>
>
|
|
|
|
|