Access ADODB call to SQL function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Access ADODB call to SQL function

Access ADODB call to SQL function

Rate Topic

Display Mode

Topic Options

Author

Message

a_ud

Posted Wednesday, March 13, 2013 8:28 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 67, Visits: 266

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

Sean Lange

Posted Wednesday, March 13, 2013 8:46 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 8,592, Visits: 8,233

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

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

Post #1430441

a_ud

Posted Wednesday, March 13, 2013 8:56 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 67, Visits: 266

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

Sean Lange

Posted Wednesday, March 13, 2013 10:23 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 8,592, Visits: 8,233

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()

Post #1430509

« Prev Topic | Next Topic »

Permissions