Pass-Through Action Query

[khurram7x]

Pass-Through always has issues i can't figure out. Now I'm trying to pass stored procedure in SQL Server by calling procedure from Access 2010 VBA as:
Call RunPassThroughACTION("EXEC acc_UpdateUserName")


i'm getting error '3146, ODBC --Call failed' when turning .ReturnRecords = False, and error '3065, Cannot execute a select Query' when .ReturnRecords = True. Procedure is an UPDATE procedure that fills the full name column by concatenating firstname and lastname.


What am i doing wrong here please??


Pasting the Pass-Through function below:


Thank you,
K

Function RunPassThroughACTION(strSQL As String)

Dim qdfPassThrough As QueryDef, MyDatabase As Database
Dim strConnect As String

If Not IsNull(CurrentDb.QueryDefs("qrySQLPassA").Sql) Then CurrentDb.QueryDefs.Delete "qrySQLPassA"

Set MyDatabase = CurrentDb()

Set qdfPassThrough = MyDatabase.CreateQueryDef("qrySQLPassA")

strConnect = "Driver=SQL Server;Server=" & TempVars("ServerName").Value & ";Database=PCMS_T1;Trusted_Connection=Yes;"

With qdfPassThrough
'Debug.Print .Name
.Connect = "ODBC;" & strConnect
.Sql = strSQL
.ReturnsRecords = False
.Execute
.Close
End With
End Function

 

[Ranman256]

You don't need the code. You can just make a query,set it to PASS THRU.

 

[khurram7x]

Thank you, solved. It was a permission issue. Your suggestion helped me get the proper error.