odbc error with passthru

[abenitez77]

I am trying to run a passthru query and get a runtime error 3146:
ODBC-- Call Failed


This is my code:

conn = "ODBC;DRIVER={SQL Server Native Client 10.0};SERVER=UKHOST04;DATABASE=COOPTRADEDISCOUNTS;TRUSTED_CONNECTION=Yes;"

sql = "Update TblGroupID Set AuditorID = 'abenit01', UpdatedDate = GetDate() From ( Select AuditorID, Min(GroupID) as MinOfGroupID From TblGroupID Group By AuditorID Having AuditorID IS NULL OR AuditorID = '') as TblGP Where TblGroupID.GroupID = TblGP.GroupID"

Call SQL_PassThrough(conn,sql)

Function SQL_PassThrough(ByVal ConnectionString As String, _
ByVal sql As String, _
Optional ByVal QueryName As String)

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
dbs.QueryTimeout = 1800

Set qdf = dbs.CreateQueryDef
With qdf
.name = QueryName
.Connect = ConnectionString
.sql = sql
.ODBCTimeout = 1800
.ReturnsRecords = (Len(QueryName) > 0)
If .ReturnsRecords = False Then
.Execute
Else 'A query of QueryName must be manually created first time or this will error out.

For Each qdf In dbs.QueryDefs
If qdf.name = QueryName Then
dbs.QueryDefs.Delete QueryName
Exit For
End If
Next

'dbs.QueryDefs.Append qdf.name

Set qdf = dbs.CreateQueryDef(QueryName, sql)



' If dbs.QueryDefs(QueryName).name = QueryName Then
' dbs.QueryDefs.Delete QueryName
' dbs.QueryDefs.Append qdf
'Else
' dbs.QueryDefs.Append qdf
'End If
End If
.Close
End With
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

End Function

 

[dhookom]

The first thing I would do is manually create a pass-through query and then paste the SQL.

Update TblGroupID Set AuditorID = 'abenit01', UpdatedDate = GetDate() 
From ( Select AuditorID, Min(GroupID) as MinOfGroupID
      From TblGroupID
      Group By AuditorID Having AuditorID IS NULL OR AuditorID = '') as TblGP 
Where TblGroupID.GroupID = TblGP.GroupID

Then test to see if the issue is the SQL or something else.

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

Yes, I tried that and it ran fine in SQL. I did a watch for the sql string and then pasted it in SQL Server and it ran fine.

 

[dhookom]

I typically keep a saved pass-through query and then simply change its SQL property as needed and execute it. I don't waste time coding the creation and deletion of query objects.

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

apparently, vba does not like to update tables when it is used with subqueries. It treats the table as read only. I had to get rid of the subquery.

 

[dhookom]

I thought you stated "it ran fine in SQL". Running the SQL as a pass-through should work the same.

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

Yes, subqueries are fine in SQL Server but in ms access vba, ODBC does not treat subqueries the same. If you try to use it in a passthru it treats the table as read only and won't let you update the table. It is an issue with how ODBC uses subqueries.

 

[dhookom]

I have never had any issues executing a query in a pass-through query that works in SQL Server Management Studio. It's an update query so there is no "table" to update. The query doesn't return records.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

The Access database Engine and linked tables may behave differently than SQL server but a Pass through query is literally sending SQL commands to the server. So they should simply work and behave the same way barring any permission or time out issues.

Try modifying your code to work with an existing Pass-thru query. I am wondering if there is some timing oddity from setting the connect property?