insert into statement from recordset 1

[ide]

Hi,

I wonder if somebody knows: Is there a way to append records of a recordset simply into an another recordset (table)? (ADO).
I have a read-only database. I want to insert some (120-100) records all at once from this into a table of the client.
***************************************************************************
an option f. e. : getrows
...
Set rst = cnn.Execute("SELECT MERESEK.* FROM MERESEK WHERE [MERESEK].[ID]>= " & lIDMax - Val(Forms![Main]![txtNumRecords]))

rstTarget.CursorType = adOpenKeyset
rstTarget.CursorLocation = adUseClient
rstTarget.LockType = adLockOptimistic
rstTarget.Open "MERESEK", CurrentProject.Connection

myArr = rst.GetRows
For i = 1 To rst.Fields.Count
str1 = str1 & rst.Fields(i - 1).Name
If i <> rst.Fields.Count Then str1 = str1 & &quot;, &quot;
Next i

For lIndex = 0 To rst.RecordCount - 1
For j = 0 To rst.Fields.Count - 1
If IsNull(myArr(j, lIndex)) Then
str2 = str2 & &quot;Null&quot;
Else
str2 = str2 & Chr(39) & myArr(j, lIndex) & Chr(39)
End If

If j <> rst.Fields.Count - 1 Then str2 = str2 & &quot;, &quot;
Next j
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = &quot;INSERT INTO MERESEK (&quot; & str1 & &quot Values (&quot; & str2 & &quot&quot;

cmd.CommandType = adCmdText
cmd.Execute

str2 = &quot;&quot;
Next lIndex
...

***************************************************************************
It works, but it's too slow. (with 800 rec ~ 25 sec)
It worked thus far faster with a simple INSERT INTO command (bellow), but now the permissions are limited.

DoCmd.RunSQL &quot;INSERT INTO MERESEK &quot; & _
&quot;SELECT MERESEK.* &quot; & _
&quot;FROM MERESEK IN &quot; & Chr(34) & strAnalMDBPath & Chr(34) & _
&quot;WHERE [MERESEK].[ID]>= &quot; & lIDMax - Val(Forms![Main]![txtNumRecords])

What to do? Now I am in despair. ide

 

[GHolden]

I don't know if this will help, I did run a test to compare using INSERT vs AddNew

ie. open the recordset you want to add from and write the fields to the new table using rst.AddNew

I tried in three combinations all using ADO.

Access front end to SQL Server
VB front end to SQL Server
VB front end to Access back end

I expected the INSERT to be faster, however, in all three tests (writing 1000 five field records) the AddNew took about one third of the time.

It may be worth a try.

Good Luck!
There are two ways to write error-free programs; only the third one works.

 

[sera]

yay that is a ton faster
Star for you!

Sera

Sera
I often believe that...
My computer is possessed!