如何解决ADP超时的问题?

yodong

如何解决ADP超时的问题?

tmtony

将超时 timeout 时间设置长一点
但如果网络不行或用户多 数据量大的话,还是可能出现这种情况. 最好是大量数据计算 使用存储过程.只返回结果

yodong

[ 本帖最后由 yodong 于 2010-11-20 00:17 编辑 ]

sqlconn = "SQLOLEDB.1ASSWORD=" & password1 & "ERSIST SECURITY INFO=FALSE;USER ID=" & sa1 & ";INITIAL CATALOG=" & data1 & ";DATA SOURCE=" & source1 & ";Connect Timeout=300;Network Library=dbmssocn"

    Dim sConnectionString As String
   
        sConnectionString = "Provider=" & sqlconn
        Application.CurrentProject.OpenConnection sConnectionString
是这样设置吗?

zhuyiwen

tmtony 发表于 2010-11-19 11:14
将超时 timeout 时间设置长一点
但如果网络不行或用户多 数据量大的话,还是可能出现这种情况. 最好是大量数 ...

使用存储过程也可能会超时，呵呵。我就碰到过这个问题。呵呵。不过不是在ADP中。

yodong

怎么解决?

zhuyiwen

cmd.CommandTimeout = 0

yodong

在ADP中如何用VBA代码设置"连接超时设定这个值.请教?

yodong

我的ADP在每次打开的时候是用下面的代码连接到数据库的.
sqlconn = "SQLOLEDB.1ASSWORD=" & password1 & "ERSIST SECURITY INFO=FALSE;USER ID=" & sa1 & ";INITIAL CATALOG=" & data1 & ";DATA SOURCE=" & source1 & ";Connect Timeout=0;Network Library=dbmssocn"
    Dim sConnectionString As String
    sConnectionString = "Provider=" & sqlconn
    Application.CurrentProject.OpenConnection sConnectionString
==============================================
完后,我在里面执行存诸过程方法是:
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
cn.Execute "xs_pro月结删除明细帐 " & nd & "," & m
因为数据比较多,总是超时.如何解决?请教大家.

yodong

'解决超时
Public Function selext(ByVal TmpSQLstmt As String)
On Error GoTo aa
    Dim cmd As New ADODB.Command
    Dim Cnn As New ADODB.Connection
    cmd.CommandTimeout = 0
    Set Cnn = CurrentProject.Connection
    Set cmd.ActiveConnection = Cnn
    'cmd.CommandType = adCmdStoredProc
    cmd.CommandText = TmpSQLstmt
    'MsgBox "Timeout on Connection: " & Cnn.CommandTimeout
    'MsgBox "Timeout on Command:  " & cmd.CommandTimeout 'Debug.Print
    cmd.Execute
    Set cmd = Nothing
    Cnn.Close
    Set Cnn = Nothing
    '================
bb:
Exit Function
aa:
MsgBox Err.Description, vbInformation, sysstr
Resume bb
End Function
'================
暂时这样解决.到现在为止还没有出现提示.
朋友们还有其它的方法吗?

zhuyiwen

呵呵，厉害