Updating SQL Server table over WAN from Access front end extremely slow

Question

I've got a small database that is hosted on our shared hosting account that I update through an access MDE file with Linked SQL Tables that gets run through task scheduler several times a day.

There's basically a small (under 20 row) user table, and a (presently) 1000 row data table.

Basically what my MDE 'front end' does is query the data out of my production database into temp tables in Access, delete the data from the two remote tables on my hosting provider and repopulate them. So it's linked to two servers (one local and one remote) and I've read sometimes this can be problematic?

The data is slowly growing as more people are asking for access to their data, and at present the table with 1000 rows takes about 5 minutes to delete/repopulate using Linked Tables.

I've alleviated PART of the slowness by deleting the remote table data using SQL Passthrough. Deletion is near instantaneous.

However, I cannot do the same for APPEND because my temp data is only on the local MDE file and uploading it makes no sense (it wouldn't be quicker than it is now).

What I'd like to know is how can I speed up my APPEND queries to the remote server? 1000 rows of data (at something like 8 columns with few nulls) isn't very big when you put it in Excel (a couple hundred KB, MAYBE) so the APPEND taking this long makes no sense.

Both the User and Datatable have a timestamp data column, I've read this helps speed up Access -> SQL delete/appends because Access can check timestamps versus comparing row by row.

I'd love some suggestions on how to speed this up. It's not tons of data by any means.

Private Sub UpdateWebFunc()
On Error GoTo errors
   Dim conn As New ADODB.Connection
   Dim cmd As New ADODB.Command
   Dim rst As New ADODB.Recordset
   Dim rst2 As DAO.Recordset
   Dim qdf As DAO.QueryDef

   conn.ConnectionString = "Blah blah blah here"

   conn.Open

   Set qdf = CurrentDb.QueryDefs("GetWebData")

   Set rst2 = qdf.OpenRecordset
   If Not rst2.BOF And Not rst2.EOF Then
   rst2.MoveFirst
   End If

   With cmd
    .CommandText = ”sp_UpdateWeb”
    .CommandType = adCmdStoredProc
    .ActiveConnection = conn
    .NamedParameters = True
    '.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
    .Parameters.Append .CreateParameter("@SampID", adVarChar, adParamInput, 50)
    .Parameters.Append .CreateParameter("@ReportTitle", adVarChar, adParamInput, 50)
   End With
   conn.BeginTrans
   Do Until rst2.EOF
    cmd.Parameters("@SampID") = rst2!SampID
    cmd.Parameters("@ReportTitle") = rst2!ReportTitle

    cmd.Execute , , adExecuteNoRecords
   rst2.MoveNext
   Loop
   conn.CommitTrans
   conn.Close
Set cmd = Nothing

   rst2.Close
   Set rst2 = Nothing

errors:
   MsgBox "Number: " & Err.Number & vbCr & " Description: " & Err.Description
End Sub

I keep getting stuck at the cmd.execute with a syntax error. I've tried to narrow it down to the two most important fields in the table to eliminate other conflicts.

ERROR: [Microsoft][ODBC SQL Server Driver]Syntax Error or access violation

Here's my SP:

USE [MYDB]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_UpdateWeb] 
    -- Add the parameters for the stored procedure here
    @SampID VarChar(50),
    @ReportTitle VarChar(50)

AS
BEGIN
    -- Insert statements for procedure here
    INSERT INTO [dbo].[WEB] (SampID, ReportTitle) VALUES (@SampID, @ReportTitle)

END

Answer 1

I take it that at the moment you are appending the data by using an access query? You might what to try the brute force method and open the local table in code and looping through that recordset firing off an insert statement for each row. You can wrap this all in a transaction to increase the performance and you could also try having a stored procedure on the SQL server that you call and pass parameters to as opposed to making the insert statement locally.

Code Sample Added*

You already know how to open the local table so I will use presto code for that. You can (and some would argue should) use DAO for the local access table. The stored procedure is then fired on an ADO connection like this

DIM cmd as NEW ADODB.Command
Dim dbCon as NEW ADODB.Connection

dbCon.ConnectionString = “Your connection string to the SQL server”
dbCon.Open

‘Setup the command
With CMD
 .CommandText=”spYour_stored_procedure”
 .CommandType=adCmdStoredProc
 .ActiveConnection=dbCon
 .NamedParameters=True
 .Parameters.Append .CreateParameter("@YourIntField", adInteger, adParamInput, 0, 123456)
 .Parameters.Append .CreateParameter("@YourTextField", adVarChar, adParamInput, 20, “Badger”)
 ‘keep on adding parameters
End With
dbcon.BeginTrans
Do until YourDAORecordset.EOF
 cmd.Parameters(“@YourIntField”)= YourDAORecordset.YourField
 cmd.Parameters(“@YourTextField”)=” YourDAORecordset.YourOtherField
 cmd.Execute , , adExecuteNoRecords
 YourDAORecordset.MoveNext
Loop
dbcon.CommitTrans
Dbcon.close
Set cmd=nothing

---

Answer 2

Why not attaching the remote server as a linked server to your production server, and bypassing Access completely ? I like Access, but in this case, it is unncecessary, and going direct will allow you to use SQL Agent to automate the task.