Cannot execute stored proc (1 Viewer)

[winshent]

I am having problems executing stored procedures on SQL Server 2005 Express.. This approach has always worked for me in the past..

On the server i have done the following:

1. Created user 'TimesUser' on the server.
2. Created the role 'DB_Executor' on the database using the following code.

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember 'db_executor', 'username'

The user account 'TimesUser' is therefore mapped it to the following roles:
1. DB_DataReader
2. DB_DataWriter
3. DB_Executor

From my access application, I call the generic following code to execute the proc. The code refers to globall for credentials, all are correct.

Public Sub ExecuteProc(ProcName As String)

  On Error GoTo PROC_ERR
 
  Dim cmd As New ADODB.Command
  Dim cnn As New ADODB.Connection
  
  Dim stConnect As String
  
  On Error Resume Next
  
  DoCmd.Hourglass True
  
  stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & SQLServer_Name & ";DATABASE=" & SQLServer_DatabaseName & ";UID=" & SQLServer_usr & ";PWD=" & SQLServer_pwd
  
  Debug.Print stConnect
  
  cnn.Open stConnect
  
  With cmd
    .ActiveConnection = cnn
    .CommandType = adCmdStoredProc
    .CommandText = ProcName
    .Execute
  End With
  
PROC_EXIT:
  On Error Resume Next
  DoCmd.Hourglass False
  cnn.Close
  Set cnn = Nothing
  Set cmd = Nothing
  Exit Sub
  
PROC_ERR:
  
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume PROC_EXIT
  
End Sub

Any ideas why it doesn't work?

 

[Galaxiom]

Any ideas why it doesn't work?

Perhaps, if you explained what you mean by "it doesn't work".

 

[winshent]

Perhaps, if you explained what you mean by "it doesn't work".

Sorry, I mean that the procedure will not execute when called by my VBA code above..

FYI, I have tested the procedure within SQL Management Studio and it works fine.

 

[Galaxiom]

Start by removing On Error Rsume Next after your Dims.

That line disables all the error handing in the procedure. I would suspect the connection step is failing.

 

[winshent]

Start by removing On Error Rsume Next after your Dims.

That line disables all the error handing in the procedure. I would suspect the connection step is failing.

Ooops, School boy error there.. Okay, now I have some meaningful information..

It appears to be trying to connect to another db on the server and not the one I am specifying in the connection string.

-2147467259

[Microsoft][SQL Native Client][SQL Server]
The server principal "User" is not able to access the database "AnotherDatabase" under 
the current security context.

The server is SQL 2005 SP4, the Access app is installed on a Windows 7 machine. I have changed the connection string to the following:

"Driver={SQL Native Client};Server=MyServer;Database=MyDatabase;UID=User;PWD=pwd;"

The Windows 7 machine has the following drivers installed:
SQL Native Client 2005.90.5000.00
SQL Server 6.01.7601.17514