querying SQL server - a problem (1 Viewer)

Hi All,
Hope you can help me with this. I know very little about access, but have been asked to retrieve some data from the SQL Server through access. I have a stored procedure on the SQL server side that accepts a username as a parameter, and returns a list of roles associated with that user.
This works fine from SQL server.
I have set up an ODBC connection, and can query SQL server tables from access.
If I try to run the stored procedure WITHOUT the parameter, I get this error

EXEC rc.dbo.GetUserSecurityLevels


ODBC--call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'GetSecurityLevels' expects parameter '@UserName', which was not supplied. (#201)

This indicates that access it at least seeing my stored procedure. But I don't know how to specify the parameter. I've tried all types of syntax:

EXEC rc.dbo.GetUserSecurityLevels('fred')
EXEC rc.dbo.GetUserSecurityLevels("fred")
EXEC rc.dbo.GetUserSecurityLevels(fred)


Would someone be able to point me in the right direction here?
Thanks,
J

Do away with the parenthesis around your parameter.

WOW, fast, correct, many thanks.
I can't believe I didn't try that!

Now, If I may take the liberty of asking a further question. Can I get Access to prompt me for the user name, rather than hard coding it?
Thanks,
J

Not in a pass-through query. You'll have to do it in VBA.

Code:Copy to clipboard

[/COLOR]
DIM QDF as Querdef
...
strReply=InputBox("Enter User NAme")
SET QDF=CurrentDb.QueryDefs("YourPassThruQuery")
QDF.SQL="[COLOR=#1f497d]EXEC rc.dbo.GetUserSecurityLevels '" & strReply & "'"[/COLOR]
Docmd.OpenQuery ("YourPassThruQuery")
...