MS Access 2013, SQL Server 2012 Express, query timeout

Question

I have an app using SQL Server 2012 Express on the local with a Microsoft Access 2016 frontend with forms, macros and modules. It is used in the field to gather inspection data, including many photographs. The problem is that when inserting to the main remote SQL Server 2012 I get an error:

Microsoft ODBC SQL Server Driver error: Query timeout expired

if the user attaches more than 3 photos.

Thus far I’ve tried raising the Remote query timeout on the remote SQL Server (Properties\Connections), I've raised the server refresh interval to 240 seconds (Tools\Options). I've added code to the VBA in the SQL function in Access:

Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.ConnectionTimeout = 120

And I’ve added the following function to the AutoExec macro that runs when the app starts:

Function SetTimeout()
      Dim Mydb As Database
      Set Mydb = CurrentDb
      Mydb.QueryTimeout = 640
  End Function

Finally I've added "Connection Timeout=90" to the end of the connection string in VBA:

Server=localhost\SQLEXPRESS2012;Database=DamInspector;

A test update script only takes 67 seconds to run but I've tried various lengths of time from "0" (infinite) to 1024. The specific Run-time error is '-2147217871 (80040e31)' [Microsoft][ODBC SQL Server Driver]Query timeout expired
Perhaps a different method?
I have 14 tables that must be synced by 11 inspectors. 7 of the tables are for photos. Would it be better to run a stored procedure on each local instance of SQL Express rather than scripting with VBA through the linked tables?

I think you will need a different approach here. An ODBC query isn't really suited to transfer so much data (relative to network speed), over a potentially instable network. Some sort of background transfer (FTP?). — Andre, Jul 27 '16 at 14:28
It isn't uploading photo files but BLOB data within the table. — DLWyer, Jul 27 '16 at 14:54
Yes, I understand, and that's IMHO the problem. I meant a *very* different approach, either not storing the photos in the database at all, or inserting them by a server process once they have been uploaded through a different channel. — Andre, Jul 27 '16 at 15:09
It would be very helpful to see the photo upload code not just the code attempts to remedy the problem. — Parfait, Aug 20 '16 at 22:51

score 1 · Answer 1 · edited Aug 24 '16 at 13:38

The solution for me was to create the process in stored procedures on the local instance of SQL Server and call it from the MS Access form. I also created a small function to create the procedures on each user's PC so I didn't have to manually do so. Since this had to be distributed to users statewide, the action takes place in Access. To add the procedures necessary to upload the inspection data and pictures I created a function for each following this format:

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=localhost\SQLEXPRESS2012;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

Then I created the function to call the each stored procedure:

Function Call_ProcAppendToGeneralPics()
Screen.MousePointer = 11
    Dim Conn As ADODB.Connection
        Set Conn = New ADODB.Connection
        Conn.Open ("Driver={SQL Server};Server=localhost\SQLEXPRESS2012;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

It is possible that this could have all been put in one procedure, but at this stage I needed to know, if there were issues inserting to the remote server, where was it breaking down. So far so good, but we've only begun.

Done. It's only just rolled out statewide, so I didn't list the code initially. — DLWyer, Aug 24 '16 at 12:44

MS Access 2013, SQL Server 2012 Express, query timeout

1 Answers1