Re: MsAccess to SqlServer: sp_setapprole problem

The issue you are experiencing is caused by the limited capabilities of
ODBC.  For basic operations ODBC is an easy way to connect an Access mdb
to a sql server backend.  But once the operations get a little more
sophisticated - as in your case - the limitations of ODBC become
apparent.  The workaround is to switch to ADO - this is why ADO was
developed -  to overcome the limitations of ODBC.

you can execute sp_setapprole through an ADODB.Command object very
easily (just make a reference in Tools/References to Microsoft ActiveX
DataObjects Library 2.5 or higher)

Sub xyz()

Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSrvr;Database=yourSvrDB;Trusted_Connection=Yes
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_setapprole"
cmd.Execute
cmd.ActiveConnection.Close

End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com
***