我有一个在本地使用SQL Server 2012 Express的应用程序,带有带有表单、宏和模块的Microsoft Access 2016前端。它被用于现场收集检查数据,包括许多照片。问题是,当插入到主远程SQL Server 2012时,我得到一个错误:
Microsoft ODBC SQL Server驱动程序错误:查询超时已过期
如果用户附加了3张以上的照片。
到目前为止,我已经尝试在远程SQL Server上提高远程查询超时(属性\连接),并将服务器刷新间隔提高到240秒(工具\选项)。我在Access的SQL函数中为VBA添加了代码:
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.ConnectionTimeout = 120
我在应用程序启动时运行的AutoExec宏中添加了以下功能:
Function SetTimeout()
Dim Mydb As Database
Set Mydb = CurrentDb
Mydb.QueryTimeout = 640
End Function
最后,我在VBA中的连接字符串末尾添加了"连接超时=90":
Server=localhostSQLEXPRESS2012;Database=DamInspector;
测试更新脚本只需要67秒就可以运行,但我已经尝试了从"0"(无限)到1024的各种时间长度。特定的运行时错误为"-2147217871(80040e31)"[Microsoft][ODBC SQL Server Driver]查询超时已过期
也许是另一种方法
我有14张表,必须由11名检查员同步。其中7张桌子是用来拍照的。在SQL Express的每个本地实例上运行一个存储过程,而不是通过链接表使用VBA编写脚本,这会更好吗?
对我来说,解决方案是在SQL Server的本地实例上的存储过程中创建进程,并从MS Access窗体调用它。我还创建了一个小函数来在每个用户的PC上创建过程,这样我就不必手动执行。由于必须在全州范围内分发给用户,因此操作在Access中进行。为了添加上传检查数据和图片所需的程序,我为以下格式创建了一个功能:
Function CreateProcAppendToAncillaryPics()
'Change mouse to hour glass so end user knows something is going on
Screen.MousePointer = 11
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Dim strCreateProcAncillaryImages As String
Conn.Open ("Driver={SQL Server};Server=localhostSQLEXPRESS2012;Database=DamInspector")
strCreateProcAncillaryImages = "CREATE PROCEDURE dbo.AppendToAncillaryPics AS " & _
"INSERT INTO [xxx.xxx.xxx.xxx].DamInspector.dbo.AncillaryImages " & _
"(tableVersion, ID, techUName, DamsPhoto, PhotoDescription, structName, marker, thisdate, uuid)" & _
" SELECT " & _
"L.tableVersion, L.ID, L.techUName, L.DamsPhoto, L.PhotoDescription, L.structName, L.marker, L.thisdate, L.uuid" & _
" FROM DamInspector.dbo.AncillaryImages AS L" & _
" LEFT OUTER JOIN " & _
"[xxx.xxx.xxx.xxx].DamInspector.dbo.AncillaryImages AS R " & _
"ON L.uuid = R.uuid " & _
"WHERE " & _
"R.uuid IS NULL;"
Conn.Execute strCreateProcAncillaryImages
Conn.Close
'Set the mouse pointer back to normal
Screen.MousePointer = 0
'Notify the user the process is complete.
MsgBox "Upload Process Completed."
End Function
然后我创建了一个函数来调用每个存储过程:
Function Call_ProcAppendToGeneralPics()
Screen.MousePointer = 11
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.Open ("Driver={SQL Server};Server=localhostSQLEXPRESS2012;Database=DamInspector")
Conn.Execute "AppendToAncillaryPics"
Conn.Execute "AppendToAuxSpillwayPics"
Conn.Execute "AppendToCrestPics"
Conn.Execute "AppendToDownstreamPics"
Conn.Execute "AppendToGeneralPics"
Conn.Execute "AppendToOcPics"
Conn.Execute "AppendToRiserPics"
Conn.Execute "AppendToUpstreamPics"
Conn.Close
Screen.MousePointer = 0
'Notify the user the process is complete.
MsgBox "Upload Process Completed."
End Function
可能这一切都可以放在一个过程中,但在这个阶段,我需要知道,如果在插入远程服务器时出现问题,它在哪里坏了。到目前为止还不错,但我们才刚刚开始。