Access passthrough query fails

[Tiger955]

Hi!

I have a function with works perfectly to execute a stored procedure by a passthrough query from Access2010

'If QueryName is not provided or is an empty string ( = "" ) no query object will be created but the SQL statement or stored procedure will be executed (useful for DELETE, INSERT, UPDATE statements).
'If you provide the parameter QueryName a query will be created with the provided name (this is useful for a SELECT statement as you expect to retrieve the resulting data set).
'*****
Function SQL_PassThrough(ByVal SQL As String, Optional QueryName As String)
Dim qdf As querydef
'On Error Resume Next
Set qdf = CurrentDb.CreateQueryDef
With qdf
.Name = QueryName
.Connect = "ODBC;DSN=myDSN;UID=myUID;PWD=myPW;APP=Microsoft Office 2010;DATABASE=myDB;"
.SQL = SQL
.ReturnsRecords = (Len(QueryName) > 0)
If .ReturnsRecords = False Then
.Execute
Else
If Not IsNull(CurrentDb.QueryDefs(QueryName).Name) Then
CurrentDb.QueryDefs.Delete QueryName
End If
CurrentDb.QueryDefs.Append qdf
End If
.Close
End With
Set qdf = Nothing
End Function

I have a stored procedure in "myDB" on Server 2008R2 which the above connection string points to.

in a Access function I call the sp like this:
...
Dim strSQL As String
strSQL = "EXEC dbo.spMyStoredProcedure " & AnyParameter
Call SQL_PassThrough(strSQL)

This function works perfectly with ALL SPs which manipulate data in "myDB".

Now the problem:
I created a sp in "myDB" which looks like this

createPROCEDURE[dbo].[spAddDataToAnotherDB]
@PersIDint

AS
BEGIN
SETNOCOUNTON;

Insertinto[AnotherDB].[dbo].[ES](SiteID,ESStructureID)
SELECT25 asSite,45 asStructure
from[myDB].[dbo].[tblBesucher]WherePersID=@PersID

when I execute the sp from "myDB" direct on the Server it works fine
"EXEC dbo.spAddDataToAnotherDB 234;"

But when sending the same string from access it fails. Not on the server, but still in Access in function "SQL_PassThrough" at .Execute.

Edit: I get the Error 3146: ODBC--call failed
whatfore I cannot find on the web any suitable explanation for my situation.


I have no explanation exept that the connection string in the access function points only to "myDB" and not to "AnotherDB".

But the sp is stored in "myDB". Could this be the reason??

Or any other ideas what the problem is??

Thanks yr. help

Michael

 

[jdraw]

That may be it.
I don't know what the solution/answer is, but here is a 3 part article by Juan Soto that may help/

Good luck.

PS -- I don't use sql server.

 

[Tiger955]

EDIT 2: I found the error, but do not know how to solve it: The error message in detail says: 916:[Microsoft].[SQL SErver Native Client 11.0].[SQL SErver] The server principal "porter"(rem.: the login-name on this database) is not able to access the database "AnotherDB" under the current security context.

So how to solve this?
Any why is it possible to execute the sp on the server and there is no such error?

Thks
Michael

 

[Galaxiom]

Sounds like a permissions problem for the Access user. Do you have Execute permission for that user?

You probably have db_owner or similar permissions when you are logged on to the server.

 

[Tiger955]

After a break I discovered and solved the problem the other day!
"myDB", the DB in which the SP is stored I get access with SQL authentication, "AnotherDB" I get access with Windows authentication.
What makes obviously no problem when I run the SP from the server, results in an error when I run it from Access in a SP.
I just changed the access for "myDB" to Windows authentication and it workes well from Access now.
Most probably it would have to create a user in "anotherDB" who has the right to
access from "myDB".

But for my concept I can live with both ways of authentication.

Michael