March 13, 2013 at 8:28 am
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.
March 13, 2013 at 8:46 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 8:56 am
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.
March 13, 2013 at 10:23 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply