I converted a query from MSAccess to SQL Server into a stored procedure but having a hard time looking for a way to get the value of CurrentUser() function from MSAccess and pass it into the stored procedure. I tried passing the CurrentUser() as a parameter to my stored procedure by calling it from a query in MSAccess, but since my Access query is a pass-through query it can't understand the CurrentUser() function I'm trying to pass.
Thanks achorozy, I already got it the other day and it's pretty the same to what you have suggested. I guess that's why you're already a guru because it took me 3 days to figure this one out.
Here's the code...
Private Function AnnuityImport_23_PubHeaderAdd_PT() 'JLS
On Error GoTo Errors
Dim strsql As String
Dim qdef As QueryDef
strsql = "exec procAnnuityImport_23_PubHeaderAdd @strUserID='" & CurrentUser() & "'"
Set qdef = CurrentDb.QueryDefs("qryAnnuityImport_23_PubHeader Add_PT")
qdef.SQL = strsql
If Err.Number <> 3146 Then
MsgBox Err.Number & " " & Err.Description, vbCritical, "Error encountered in function AnnuityImport_23_PubHeaderAdd_PT."