时间戳

首页 > 解决方案 > Calling stored procedure from MS Access results in error 3146

sql-server - 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

标签： sql-servervbams-access

解决方案

如果运行了，但没有到最后，那么很可能不是权限问题，而是超时问题

你可以做一个：

debug.print qdef.ODBCTimeout

知道您当前的超时值是多少。

然后只需增加足够的秒数即可完成存储过程：

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

推荐阅读