Copying from one record set to another

Hi, I am trying to do a workaround while I'm trying to resolve an issue with
the SQL Server implementation that does the same thing, but is currently
broken.

Basically, I have an MDB file with two tables in it, and two tables in SQL
Server that look just like the tables in the access database

Both tables have an auto increment field on the Access side. I want to
select the new records from the Access db and insert them into the SQL DB.

I can get to both DBs from Excel. I can write code that pulls the new
records from Access into Excel, now I want to push those records into SQL
Server.

It seems to me I don't really even need the work***. If I got a record set
from Access, and I know it's the same structure as what's in SQL Server, is
there a way to insert the record set from Access in SQL Server?

This doesn't give me any errors ....

Private Sub MoveDataFromAccessToSQLServer()
Dim ADODB_ACConnection As New ADODB.Connection
Dim ADODB_SQConnection As New ADODB.Connection
Dim ADODB_ACRecordSet As New ADODB.Recordset
Dim ADODB_SQRecordSet As New ADODB.Recordset

On Error GoTo ExitRoutine

With ADODB_ACConnection
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=<FileName>.mdb;Persist Security Info=False;"
.Open
If .State <> adStateOpen Then
MsgBox "Could not open Access database"
GoTo ExitRoutine
End If
End With

With ADODB_ACRecordSet
.ActiveConnection = ADODB_ACConnection
.Open "SELECT * FROM Experiment WHERE autonumber > 1000"
End With

With ADODB_SQConnection
.ConnectionString = "Provider=SQLNCLI.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=<DBName>;Data
Source=<Server>"
.Open
If .State <> adStateOpen Then
MsgBox "Could not open SQL database"
GoTo ExitRoutine
End If
End With

With ADODB_SQRecordSet
.ActiveConnection = ADODB_SQConnection
.Open "SELECT * FROM dbsrc.vExperiment WHERE 1=0" 'This gives me an
empty record set
End With

'Here is where I am stuck. I want to just insert everything
'in ADODB_ACRecordSet into ADODB_SQRecordSet
'Is there a way to do this without iterating thorugh
'every field in every row?

ExitRoutine:
If Err.Number <> 0 Then
strError = CStr(Err.Number) & vbCrLf
strError = strError & Err.Description & vbCrLf
strError = strError & Err.Source
MsgBox strError
End If


If ADODB_ACRecordSet.State <> adStateClosed Then
ADODB_ACRecordSet.Close
End If
Set ADODB_ACRecordSet = Nothing

If ADODB_SQRecordSet.State <> adStateClosed Then
ADODB_SQRecordSet.Close
End If
Set ADODB_SQRecordSet = Nothing

If ADODB_ACConnection.State <> adStateClosed Then
ADODB_ACConnection.Close
End If
Set ADODB_ACConnection = Nothing

If ADODB_SQConnection.State <> adStateClosed Then
ADODB_SQConnection.Close
End If
Set ADODB_SQConnection = Nothing

End Sub

.

Loading