How do i use a SQL Server Scalar-valued function in MS Access ADP?

I have a function "fGrantAccess" in the SQL Server Database and i would like
to use it in an Access Project. The function returns a GUID from a table
depending on two parameters: user id and password.

I thought the function would operate in a similar fashion to a view or
table-valued function; however, i was mistaken.

This line's error is "Invalid Object Name"..

rdsStr = "SELECT fGrantAccess FROM dbo.fGrantAccess WHERE (" & _
"(uiLoginId='" & uName.Value & "') AND " & _
"(uiLoginPw='" & uPass.Value & "'));"

rdsADO.Open rdsStr, Application.CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

ps. i have also tried setting the sql string to

rdsStr = "SELECT fGrantAccess FROM {fn {fGrantAccess('" & uName.Value &
"','" & uPass.Value & "')}};"

Thank you for your help...

"rogge" <(E-Mail Removed)> wrote in message
<72EACE30-140F-40D1-8B2A-(E-Mail Removed)>:
> How do i use a SQL Server Scalar-valued function in MS Access ADP?
>
> I have a function "fGrantAccess" in the SQL Server Database and i
> would like to use it in an Access Project. The function returns a
> GUID from a table depending on two parameters: user id and password.
>
> I thought the function would operate in a similar fashion to a view
> or table-valued function; however, i was mistaken.
>
> This line's error is "Invalid Object Name"..
>
> rdsStr = "SELECT fGrantAccess FROM dbo.fGrantAccess WHERE (" & _
> "(uiLoginId='" & uName.Value & "') AND " & _
> "(uiLoginPw='" & uPass.Value & "'));"
>
> rdsADO.Open rdsStr, Application.CurrentProject.Connection,
> adOpenForwardOnly, adLockReadOnly
>
> ps. i have also tried setting the sql string to
>
> rdsStr = "SELECT fGrantAccess FROM {fn {fGrantAccess('" & uName.Value
> & "','" & uPass.Value & "')}};"
>
> Thank you for your help...

Assuming username is the first parameter, and password the second, try
something like this:

rdsStr = "SELECT dbo.fGrantAccess('" & uName.Value & "', " & _
uPass.Value & "')"

--
Roy-Vidar

Try this:

rdsStr = "SELECT dbo.fGrantAccess('" & uName & "','" & uPass & "')"

"rogge" <(E-Mail Removed)> wrote in message
news:72EACE30-140F-40D1-8B2A-(E-Mail Removed)...
> How do i use a SQL Server Scalar-valued function in MS Access ADP?
>
> I have a function "fGrantAccess" in the SQL Server Database and i would
like
> to use it in an Access Project. The function returns a GUID from a table
> depending on two parameters: user id and password.
>
> I thought the function would operate in a similar fashion to a view or
> table-valued function; however, i was mistaken.
>
> This line's error is "Invalid Object Name"..
>
> rdsStr = "SELECT fGrantAccess FROM dbo.fGrantAccess WHERE (" & _
> "(uiLoginId='" & uName.Value & "') AND " & _
> "(uiLoginPw='" & uPass.Value & "'));"
>
> rdsADO.Open rdsStr, Application.CurrentProject.Connection,
> adOpenForwardOnly, adLockReadOnly
>
> ps. i have also tried setting the sql string to
>
> rdsStr = "SELECT fGrantAccess FROM {fn {fGrantAccess('" & uName.Value &
> "','" & uPass.Value & "')}};"
>
> Thank you for your help...

Baz, that worked like a charm... i need to adjust the rest of my code; but,
hopefully, i will be able to figure that out... have a good week

"Baz" wrote:

> Try this:
>
> rdsStr = "SELECT dbo.fGrantAccess('" & uName & "','" & uPass & "')"
>
> "rogge" <(E-Mail Removed)> wrote in message
> news:72EACE30-140F-40D1-8B2A-(E-Mail Removed)...
> > How do i use a SQL Server Scalar-valued function in MS Access ADP?
> >
> > I have a function "fGrantAccess" in the SQL Server Database and i would
> like
> > to use it in an Access Project. The function returns a GUID from a table
> > depending on two parameters: user id and password.
> >
> > I thought the function would operate in a similar fashion to a view or
> > table-valued function; however, i was mistaken.
> >
> > This line's error is "Invalid Object Name"..
> >
> > rdsStr = "SELECT fGrantAccess FROM dbo.fGrantAccess WHERE (" & _
> > "(uiLoginId='" & uName.Value & "') AND " & _
> > "(uiLoginPw='" & uPass.Value & "'));"
> >
> > rdsADO.Open rdsStr, Application.CurrentProject.Connection,
> > adOpenForwardOnly, adLockReadOnly
> >
> > ps. i have also tried setting the sql string to
> >
> > rdsStr = "SELECT fGrantAccess FROM {fn {fGrantAccess('" & uName.Value &
> > "','" & uPass.Value & "')}};"
> >
> > Thank you for your help...
>
>
>

Thanks Roy... it worked for me

"RoyVidar" wrote:

> "rogge" <(E-Mail Removed)> wrote in message
> <72EACE30-140F-40D1-8B2A-(E-Mail Removed)>:
> > How do i use a SQL Server Scalar-valued function in MS Access ADP?
> >
> > I have a function "fGrantAccess" in the SQL Server Database and i
> > would like to use it in an Access Project. The function returns a
> > GUID from a table depending on two parameters: user id and password.
> >
> > I thought the function would operate in a similar fashion to a view
> > or table-valued function; however, i was mistaken.
> >
> > This line's error is "Invalid Object Name"..
> >
> > rdsStr = "SELECT fGrantAccess FROM dbo.fGrantAccess WHERE (" & _
> > "(uiLoginId='" & uName.Value & "') AND " & _
> > "(uiLoginPw='" & uPass.Value & "'));"
> >
> > rdsADO.Open rdsStr, Application.CurrentProject.Connection,
> > adOpenForwardOnly, adLockReadOnly
> >
> > ps. i have also tried setting the sql string to
> >
> > rdsStr = "SELECT fGrantAccess FROM {fn {fGrantAccess('" & uName.Value
> > & "','" & uPass.Value & "')}};"
> >
> > Thank you for your help...
>
> Assuming username is the first parameter, and password the second, try
> something like this:
>
> rdsStr = "SELECT dbo.fGrantAccess('" & uName.Value & "', " & _
> uPass.Value & "')"
>
> --
> Roy-Vidar
>
>
>