I've got a small access front end I've written that takes data out of
our local SQL Server 2008 production database and stores it in some
temporary tables.

Then what the next step in the process is, is to take the data in the
temp tables, do the appropriate joins and insert it into a couple tables
on a SQL Server 2008 instance on my hosting provider.

My problem is:

Updating a table with 900 rows takes about 2 minutes (slow over T1 if
you ask me).

This is just using some Access Query's with linked tables.

My next step was to connect to the remote instance via ADO and 'loop'
through my local tables to the remote tables. This doesn't seem to ahve
increased speed.


The next recommendation was to pass the columns as parameters to a
stored procedure.

That's what I'm presently working on, however every time I go to execute
my ADODB.command definition I get a syntax error / access denied message.

Even if I feed it erroneous data that I know would fit appropriately in
the columns such as "bear", "dog", "cat" into varchar(50) fields.


In short: Can anyone recommend a way that might be easier/faster to add
this data to my remote system. I do this several times a day and since
it runs on my workstation it's becoming a hinderance now that the tables
are actually growing.

Any suggestions on a better method, or tips on a method mentioned above
are greatly appreciated.

-Ryan

Have you tried using passthrough queries? I have a similar situation
with a remote Oracle server, and I changed to using passthrough
queries to get my job done. The time difference was incredible. My
inserts went from around 20mins to a few seconds. The other thing I
have used that was very helpful was transactions in ADO. Set your
connection, make your recordset, start a transaction, add the rows
(900 should be no issue to do in one hit), commit the transaction,
close the connection. The ADO method was approx 2 mins for the same
job. Passthrough was definitely the fastest.

Cheers

The Frog

I have passthrough queries for the deletions which sped up some stuff
dramatically.

The problem with the inserts are the tables the data is coming from is
local, and the pass through query is going to pass those tables to the
remote server and it's gonna go "that table doesn't exist" (or whatever
the actual message is.

I'm presently working testing an ADO method, and it's actually taking
*LONGER* to do this than it is just to have a query built in the query
builder by almost twice as much.



The Frog wrote:

Alright, I've also tried a querydef passthrough, it's still taking
longer than just my linked table query (very weird). Forgive the
formatting. I just wrote it out real fast.

Of course, there's a SP on the remote host called sp_updateweb.


Could you provide an alternative way? You said your passthrough is
lightning fast.


Private Sub TryADO()
'Dim rst As ADODB.Recordset
'Dim conn As New ADODB.Connection
'Dim cmd As ADODB.Command
Dim rst2 As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim qdf2 As DAO.QueryDef

Set qdf = CurrentDb.CreateQueryDef("tmpquery")
qdf.Connect = "ODBC;DRIVER={SQL Server};" & _
"SERVER={SQL Server};" & _
"Network=dbmssocn;" & _
"Database=svr;" & _
"UID=user;" & _
"PWD=pass;"
qdf.ReturnsRecords = False

Set qdf2 = CurrentDb.QueryDefs("GetWebData")

Set rst2 = qdf2.OpenRecordset
rst2.MoveFirst

Do Until rst2.EOF

qdf.SQL = "exec sp_UpdateWeb @BunchofParams=''" <-- took this out
for easy reading
CurrentDb.Execute "tmpquery"
rst2.MoveNext
Loop

CurrentDb.QueryDefs.Delete "tmpquery"

qdf.Close
rst.Close
rst2.Close
qdf2.Close


Set qdf = Nothing
Set qdf2 = Nothing
Set rst = Nothing
Set rst2 = Nothing


End Sub

The Frog wrote:

?
I would create the table local, and then try via a linked table do an append
query.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

That's how I presently do it.


On 12/10/10 7:25 PM, Albert D. Kallal wrote:

?"Ryan" wrote in message news:idumpg$e1s$1 (AT) news (DOT) eternal-september.org...



The code you posted hints that you running an sql command over and over.

You have:

CurrentDb.Execute "tmpquery"
rst2.MoveNext

I saying to create all the data into a temp local table, and then do ONE
append query, NOT execute a query over and over as you have in a loop (which
as you already found out is very slow).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

Yes. The way I do it "normally" (the SLOW way) is via a linked SQL table
and a local temp table in access. Then I just have a query that inserts
the data from the local temp table to the linked SQL Table.

Apparently this uses DAO (or some other method) to do the inserts row by
row, rather than a bulk insert.

The code below was an attempt to use sql passthrough, even though it's
line by line I was hoping it was faster. (it's not)



On 12/10/10 8:19 PM, Albert D. Kallal wrote:

?"Ryan" wrote in message news:iduna9$foi$1 (AT) news (DOT) eternal-september.org...


Ah, ok. Thanks for the follow up. I would have guessed using many individual
statements would be slower.

However, there is two additional things I would try.

First, try using a dao (or ado) reocordset, and use the add.new + code to
add records (as opposed to sql statements). In fact, with ADO you might even
check if bulk inserts are supported.

And, second thing to try? I would continue to use a pass-though, but chunk
together 10, or even 100 records at a time into a single insert statement.
(each sql statement has a large cost, so the less sql statements run, the
faster the whole process. I think this approach could speed things up by a
very large rate. In fact it could likely approach network transfer rates if
this works.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

Ryan wrote:
that gets it records from the results of OPENQUERY (look it up in BOL - it
involves creating a linked server on the SQL Server box pointing at source
database). It is typically faster for SQL to pull records from an external
database into a table rather than push the records from the external
database into the table.