Is latency or my VPN choking my Excel to SQL Server uploads?
I am uploading data from Excel to SQL Server using the following structure:

    Private Sub ado_upload()
    
        Dim objConnection As New ADODB.Connection
        Dim objCommand As New ADODB.Command
        Dim strSQL As String
        Dim strDSN As String
        Dim intCounter As Integer
        
        strDSN = "provider=SQLOLEDB;" _
        & "server=<server>;" _
        & "database=<database>;" _
        & "uid=<uid>;pwd=<pwd>;" _
        & "trusted_connection=false;"
        
        With objConnection
            .ConnectionString = strDSN
            .Open
        End With
        
        strSQL = "SET NOCOUNT ON; " _
        & "INSERT INTO dbo.[table1] ( [col1] ) VALUES ( ? );"
        
        With objCommand
            
            .ActiveConnection = objConnection
            .CommandText = strSQL
            .Prepared = True
            .Parameters.Append .CreateParameter("col1", adInteger, adParamInput)
            
            For intCounter = 0 To 9
                .Parameters("Col1").Value = intCounter
                .Execute
            Next intCounter
        
        End With
    
    End Sub

The speed of the procedure depends on the geographic distance between the server and the computer running the procedure. On the server itself it is fast (300,000 inserts in under 10 minutes), on the other side of the country it is relatively slow (300,000 inserts could take hours). Remote uploads operate over a VPN.

I think network latency must be slowing the process down. Is there any way to get around network latency, or tweak the VPN to make the uploads faster?

Thanks!
Is latency or my VPN choking my Excel to SQL Server uploads?
===

I am uploading data from Excel to SQL Server using the following structure:

    Private Sub ado_upload()
    
        Dim objConnection As New ADODB.Connection
        Dim objCommand As New ADODB.Command
        Dim strSQL As String
        Dim strDSN As String
        Dim intCounter As Integer
        
        strDSN = "provider=SQLOLEDB;" _
        & "server=<server>;" _
        & "database=<database>;" _
        & "uid=<uid>;pwd=<pwd>;" _
        & "trusted_connection=false;"
        
        With objConnection
            .ConnectionString = strDSN
            .Open
        End With
        
        strSQL = "SET NOCOUNT ON; " _
        & "INSERT INTO dbo.[table1] ( [col1] ) VALUES ( ? );"
        
        With objCommand
            
            .ActiveConnection = objConnection
            .CommandText = strSQL
            .Prepared = True
            .Parameters.Append .CreateParameter("col1", adInteger, adParamInput)
            
            For intCounter = 0 To 9
                .Parameters("Col1").Value = intCounter
                .Execute
            Next intCounter
        
        End With
    
    End Sub

The speed of the procedure depends on the geographic distance between the server and the computer running the procedure. On the server itself it is fast (300,000 inserts in under 10 minutes), on the other side of the country it is relatively slow (300,000 inserts could take hours). Remote uploads operate over a VPN.

I think network latency must be slowing the process down. Is there any way to get around network latency, or tweak the VPN to make the uploads faster?

Thanks!