Calling stored procedure from MS Access results in error 3146

When I call a SQL Server stored procedure from MS Access front-end using the code shown below, it stops running and throws the runtime error "3146".

This stored procedure is working correctly in SQL Server, but when I run from MS Access, it is working at first, but suddenly stops and throws that runtime error "3146".

For more clearance this stored procedure is for inserting records in an empty table when I do from MS Access it should insert 1000 records, but it inserts 970 records and then stops.

My stored procedure had a select query for inserting records, now I have changed this easy select query to a union query, since I have changed this in the design of stored procedure it is working fine in SQL Server

Public Sub Command0_Click()
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")

qdef.Connect = CurrentDb.TableDefs("[ASBUILT_LIST]").Connect
qdef.SQL = "EXEC Update_Asbuilt2"
qdef.ReturnsRecords = False ''avoid 3065 error
qdef.Execute
qdef.Close

Set qdef = Nothing
End Sub


For I = 0 To Errors.Count-1 Debug.Print Errors(I) Next I

1 Answer
1

If it runs, but not until the end, then it is probably not a permission problem, but a timeout problem

you can do a :

debug.print qdef.ODBCTimeout


to know what your current Timeout value is.

Then just increase it with enough seconds to complete the stored procedure :

Public Sub Command0_Click()
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")

qdef.Connect = CurrentDb.TableDefs("[ASBUILT_LIST]").Connect
' --------------- Define TimeOut in Seconds ----------------
qdef.ODBCTimeout = 2000
' ----------------------------------------------------------
qdef.SQL = "EXEC Update_Asbuilt2"
qdef.ReturnsRecords = False ''avoid 3065 error
qdef.Execute
qdef.Close

Set qdef = Nothing
End Sub


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.