SQL Scalar Valued function in VBA for a Access Project (ADP)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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...
Click to expand...

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

rdsStr = "SELECT dbo.fGrantAccess('" & uName.Value & "', " & _
uPass.Value & "')"
 
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
 
Thanks Roy... it worked for me

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

rdsStr = "SELECT dbo.fGrantAccess('" & uName.Value & "', " & _
uPass.Value & "')"
Click to expand...
 
Back
Top