Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Access ADODB call to SQL function Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 8:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
Help needed here!

I've got a system made out of: Access front-end + SQL Server back-end (typical).

I need to call from Access VBA a function in SQL Server, but I can't get it to run, some detail is missing. Say the SQL function is called XFunc(@id INT), so it has one parameter @id.

The ADODB code then would look sth like:

Dim cmd as NEW adodb.command
cmd.activeconnection = <the connexion you've set up>
cmd.CommandText = "XFunc"
cmd.Parameters = ??????????

cmd.Execute


I've tried this but VBA throws an error, either I'm missing the parameter @id when calling XFunc, or there is a specific ADODB command to call function (and maybe I'm using the stored procedures one).

What am I missing here? Are there any good sources of ADODB documentation you can recommend?

Thanks in advance, A.
Post #1430425
Posted Wednesday, March 13, 2013 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
a_ud (3/13/2013)
Help needed here!

I'm got a system made out of: Access front-end + SQL Server back-end (typical).

I need to call from Access VBA a function in SQL Server, but I can't get it to run, some detail is missing. Say the SQL function is called XFunc(@id INT), so it has one parameter @id.

The code then would look sth like:

Dim cmd as NEW adodb.command
cmd.activeconnection = <the connexion you've set up>
cmd.CommandText = "XFunc"
cmd.Parameters = ??????????

cmd.Execute


I've tried this but VBA throws an error, either I'm missing the parameter @id when calling XFunc, or there is a specific ADODB command to call function (and maybe I'm using the stored procedures one).

What am I missing here? Are there any good sources of ADODB documentation you can recommend?

Thanks in advance, A.


That isn't quite how sql functions work.

Something like this should work.
select dbo.XFunc(@Parm)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1430441
Posted Wednesday, March 13, 2013 8:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
Hi Sean,

Thanks, that helps, but could you complete it?

So let's say you've got a string var 'X' and want to return the function result there. Would this be the code for that?

dim X as String
X = Docmd.RunSQL "select " & dbo.XFunc(@Parm)

Thanks in advance, A.


Post #1430446
Posted Wednesday, March 13, 2013 10:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
I don't remember all of the syntax off the top of my head and I haven't used this in several years. I will do my best but google will likely fill in the holes and syntax issues. This is reasonably close IIRC.

Let's say you have a scalar function called MyFunction that receives a single parameter.

sql = "select dbo.MyFunction(?)"

dim myParam as new ADODB.Parameter
myParam.Value = [The value to pass in]

command.Parameters.Append myParam

dim rs as new ADODB.RecordSet
set rs = command.Execute()



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1430509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse