calling stored procedure on sql server DAO

[eighthman11]

I'm calling a stored procedure on a sql server from an access
application. I just need the stored procedure to run I do not need any
data returned from the stored procedure to my Access application but I
do pass parameters from my Access application. What I have works fine
and is attached below. But I have one question. Why do I need this
line of code to execute the stored procedure.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Here is the rest of my code:

Sub StoredProcedure()
Dim dbs As DAO.DataBase
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strConnectString As String
Dim Company As Long
Dim JobNumber As String
Dim Shift As Long
Dim DayWorked As String
Company = GetCompany()
JobNumber = "'" & GetJob() & "'"
Shift = GetShift()
DayWorked = "'" & GetDate() & "'"

Set dbs = CurrentDb

strConnectString = "ODBC" & _
";Database=Viewpoint" & _
";UID=ODBC" & _
";PWD=odbc" & _
";DSN=Viewpoint"
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnectString
qdf.ReturnsRecords = True

qdf.SQL = "dbo.uspTRFile " & Company & "," & JobNumber & "," & Shift &
"," & DayWorked & ""
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

rst.Close
Set rst = Nothing
Set qdf = Nothing
End Sub

[Tom van Stiphout]

On 17 Jan 2007 12:28:25 -0800, "eighthman11" <rd******@nooter.com>
wrote:

I think because calling stored procedures using DAO only barely works,
and this essentially is a workaround. More elegant would be if DAO had
an Execute method, like ADO does.

-Tom.

>I'm calling a stored procedure on a sql server from an access
application. I just need the stored procedure to run I do not need any
data returned from the stored procedure to my Access application but I
do pass parameters from my Access application. What I have works fine
and is attached below. But I have one question. Why do I need this
line of code to execute the stored procedure.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Here is the rest of my code:

Sub StoredProcedure()
Dim dbs As DAO.DataBase
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strConnectString As String
Dim Company As Long
Dim JobNumber As String
Dim Shift As Long
Dim DayWorked As String
Company = GetCompany()
JobNumber = "'" & GetJob() & "'"
Shift = GetShift()
DayWorked = "'" & GetDate() & "'"

Set dbs = CurrentDb

strConnectString = "ODBC" & _
";Database=Viewpoint" & _
";UID=ODBC" & _
";PWD=odbc" & _
";DSN=Viewpoint"
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnectString
qdf.ReturnsRecords = True

qdf.SQL = "dbo.uspTRFile " & Company & "," & JobNumber & "," & Shift &
"," & DayWorked & ""
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

rst.Close
Set rst = Nothing
Set qdf = Nothing
End Sub

[paii, Ron]

I have no experience with sql server, but there is an execute in the DAO
querydef object. Isn't this the same as ADO?

"Tom van Stiphout" <no*************@cox.netwrote in message
news:1k********************************@4ax.com...

On 17 Jan 2007 12:28:25 -0800, "eighthman11" <rd******@nooter.com>
wrote:

I think because calling stored procedures using DAO only barely works,
and this essentially is a workaround. More elegant would be if DAO had
an Execute method, like ADO does.

-Tom.

I'm calling a stored procedure on a sql server from an access
application. I just need the stored procedure to run I do not need any
data returned from the stored procedure to my Access application but I
do pass parameters from my Access application. What I have works fine
and is attached below. But I have one question. Why do I need this
line of code to execute the stored procedure.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Here is the rest of my code:

Sub StoredProcedure()
Dim dbs As DAO.DataBase
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strConnectString As String
Dim Company As Long
Dim JobNumber As String
Dim Shift As Long
Dim DayWorked As String
Company = GetCompany()
JobNumber = "'" & GetJob() & "'"
Shift = GetShift()
DayWorked = "'" & GetDate() & "'"

Set dbs = CurrentDb

strConnectString = "ODBC" & _
";Database=Viewpoint" & _
";UID=ODBC" & _
";PWD=odbc" & _
";DSN=Viewpoint"
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnectString
qdf.ReturnsRecords = True

qdf.SQL = "dbo.uspTRFile " & Company & "," & JobNumber & "," & Shift &
"," & DayWorked & ""
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

rst.Close
Set rst = Nothing
Set qdf = Nothing
End Sub

[Rick Brandt]

eighthman11 wrote:

I'm calling a stored procedure on a sql server from an access
application. I just need the stored procedure to run I do not need
any data returned from the stored procedure to my Access application
but I do pass parameters from my Access application. What I have
works fine and is attached below. But I have one question. Why do
I need this line of code to execute the stored procedure.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

The reason you have to do that is because you have specified...
qdf.ReturnsRecords = True

If you set that to No then you should be able to use Execute instead of
Open.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[eighthman11]

Thanks for the response

Rick Brandt wrote:

eighthman11 wrote:

I'm calling a stored procedure on a sql server from an access
application. I just need the stored procedure to run I do not need
any data returned from the stored procedure to my Access application
but I do pass parameters from my Access application. What I have
works fine and is attached below. But I have one question. Why do
I need this line of code to execute the stored procedure.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

The reason you have to do that is because you have specified...
qdf.ReturnsRecords = True

If you set that to No then you should be able to use Execute instead of
Open.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com