sql statement return single value

[JohnE]

Hello. I am having a situation in which I need to make another Sub similar to
the one that is below. But, the one that I need to do is to only return a
single value (ex: a boolean). I do know that the result should come after
the .Execute line and before the .Close but I am not grasping what is needed
and ask for assistance from the group.

Sub RunSQLServerStoredProcedure(sql As String)
Dim server_name As String
Dim connect_str As String
Dim DB As Database
Dim new_qd As QueryDef
Dim database_name As String

server_name = DLookup("SQLServer", "_LinkedDataSets", "UseIt = True")
database_name = DLookup("SQLDatabase", "_LinkedDataSets", "UseIt = True")

connect_str = SqlServerODBCConnectionString(server_name, database_name)

Set DB = CurrentDb

Set new_qd = DB.CreateQueryDef("") 'Setting name to "" creates a
temporary query def!!!!

new_qd.ReturnsRecords = False 'this MUST come before setting the SQL
string

new_qd.Connect = connect_str

new_qd.sql = "Exec " & sql

new_qd.Execute

new_qd.Close

End Sub

If anyone can get me started would be great. Links are also good.
Thanks..... John

 

[Dorian]

Look up Functions in Access Help, see below:

Function RunSQLServerStoredProcedure(sql As String) As Boolean
Dim server_name As String
Dim connect_str As String
Dim DB As Database
Dim new_qd As QueryDef
Dim database_name As String

server_name = DLookup("SQLServer", "_LinkedDataSets", "UseIt = True")
database_name = DLookup("SQLDatabase", "_LinkedDataSets", "UseIt = True")

connect_str = SqlServerODBCConnectionString(server_name, database_name)

Set DB = CurrentDb

Set new_qd = DB.CreateQueryDef("") 'Setting name to "" creates a
temporary query def!!!!

new_qd.ReturnsRecords = False 'this MUST come before setting the SQL
string

new_qd.Connect = connect_str

new_qd.sql = "Exec " & sql

new_qd.Execute

new_qd.Close

RunSQLServerStoredProcedure = True (or False)

End Function

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".