Add members from VBA code

[luckydata]

I have been trying to add code to my access97 front end that will add a new user to the Access2000 server, database and to a role. The following code executes correctly in query analyzer. It will not work when executed through a passthrough query, (access97 and DAO). The code is built by vba. I use the same passthrough query to execute SQL update and insert commands, so I am confident its connection properties are correct.

USE SSOLicense
EXEC sp_grantlogin 'DSTOMESSAGING\beames'
GO
EXEC sp_grantdbaccess 'DSTOMESSAGING\beames'
GO
EXEC sp_addrolemember 'OIC', 'DSTOMESSAGING\beames'
GO
Executing the passthrough query causes a runtime error '3146 ODBC--call failed'


My attempts to create a SP failed to, so either I am having a bad day or there is something I don't understand.

CREATE PROCEDURE spAddLogin @UserID varchar(10) AS

EXEC sp_grantlogin 'DSTOMESSAGING\' @UserID
GO
EXEC sp_grantdbaccess 'DSTOMESSAGING\' @UserID
GO
EXEC sp_addrolemember 'OIC', 'DSTOMESSAGING\' @UserID
GO

causes the error "Error 170: Line 3: Incorrect syntax near '@UserID'"

As I am a strugling learner I would appreciate any comments or help any one can give.

 

[alfredp77]

1. remove the GOs from your stored proc. sql will start another batch after GO. so after your first GO, everything else will not be part of CREATE PROCEDURE statement.

2. your storproc parameter is wrong, you must concatenate 'DSTOMESSAGING\' and @UserID, store it to a variable then pass it to the sp_grantlogin, sp_grantdbaccess and sp_addrolemember

your storproc declaration should be like this:

CREATE PROCEDURE spAddLogin @UserID varchar(10) AS

declare @newUserID varchar(30)
select @newUserID = 'DSTOMESSAGING\' + @UserID

EXEC sp_grantlogin @newUserID
EXEC sp_grantdbaccess @newUserID
EXEC sp_addrolemember 'OIC', @newUserID

GO