Download ADO Table

Topic

New Reply

Hi,

I’m trying to get a faster method for Downloading a web based “SQL server” tables data to a local table for backup, currently i’m using:

Sub ConnectDB()
Set oConn = New ADODB.Connection
oConn.Open “Driver={SQL Server};” & _
“Server=www.somewhere.com;” & _
“Address=???.???.???.???,1433;” & _
“Network=DBMSSOCN;” & _
“Database=????????;” & _
“Uid=????????;” & _
“Pwd=??????;”
End Sub

To connect then

SQL1 = “Select * from Remote_Table”
rs123.Open SQL1, oConn
Set rs22 = CurrentDb.OpenRecordset(“Local_Table”)
rs123.MoveFirst
Do While Not rs123.EOF
DoEvents
rs22.AddNew
rs22![Field1] = rs123![Field1]
rs22![field2] = rs123![field2]
etc

rs22.Update
rs123.MoveNext
Loop

This is very slow. In a perfect scenario I just want to Append the [Remote_table] to a preformated blank [Local_table], any suggestions???

At the moment it can take an Hour to Download.

With thanks in Antici Graliv

Reply | Quote

Replies

After you connect, what happens if you change your sql string to a simple append query and use docmd.runsql rather than looping through the recordset? Since it is an append query, if you don’t want the warnings that go with append queries you can add docmd.SetWarnings False before and True after to kill the warnings.

SQL1 = “INSERT INTO Local_Table ” & _
“SELECT Remote_Table.* ” & _
“FROM Remote_Table;”

docmd.SetWarnings False
docmd.runsql
docmd.SetWarnings True

Peter

Reply | Quote

After you connect, what happens if you change your sql string to a simple append query and use docmd.runsql rather than looping through the recordset? Since it is an append query, if you don’t want the warnings that go with append queries you can add docmd.SetWarnings False before and True after to kill the warnings.

SQL1 = “INSERT INTO Local_Table ” & _
“SELECT Remote_Table.* ” & _
“FROM Remote_Table;”

docmd.SetWarnings False
docmd.runsql
docmd.SetWarnings True

Apologies for the delay in reply only just got back on this project

Peter I tried that and it searches for the [Remote_Table] Table in the current Database and gives an error message to that effect.

Reply | Quote

Alternatively set up a maintenance task on the SQL server to backup to disk and use a remote share as the destination.

cheers, Paul

Reply | Quote

I thought about that shortly after I posted. I always work with DAO and have never been comfortable with ADO. My guess is that it is something to do with establishing the remote connection but I’m out of my depth at that point. In your original code, it just seemed to me a little inefficient to loop through the recordset rather than just run a query. Not sure how running SQL fits into the picture. As Paul suggests, maybe this is better run on the SQL Server end. One final thought and again, this may not be possible given the web-based setup: Can you link your Access database to the table in question in SQL Server? Then I think Access would see it as local. Just a thought. Over to people who know better

Reply | Quote