468,248 Members | 1,513 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,248 developers. It's quick & easy.

calling stored procedure on sql server DAO

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

Jan 17 '07 #1
4 16566
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
Jan 18 '07 #2
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

Jan 18 '07 #3
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
Jan 18 '07 #4
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
Jan 19 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Bob Murdoch | last post: by
1 post views Thread by Purple-Man | last post: by
2 posts views Thread by Woody Splawn | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.