Insert Complete RecordSet to another table in other database MS Access

Question

I have 2 Tables with same colums in two diferent files. One file "Base PT.accdb" where i want to insert all the Recordset from the other file "Sanchez_Mar.mdb". Both tables have 7 Columns with the same name.

I had a lot of problems using "System.Data.OleDb.OleDbConnection" so i decided to use RecordSets. At the moment i'm using loop to insert all, but there is a lot of data so it take quite long. It's posible to insert all the recordset to the other database ?

this is the part of my code i need help:

Sub Copiar_BasePT2(RutaPT As String)
  Dim Cant as Long
  Dim db As Database
  Dim dbBase_PT As Database
  Set dbBase_PT = OpenDatabase(RutaPT & "\Base PT.accdb")
  Set db = CurrentDb
  Dim rsSYM As Recordset
  Set rsSYM = db.OpenRecordset("SELECT * from FINAL_Alarma_SYM")
  rsSYM.MoveLast
  Cant = rsSYM.RecordCount
  rsSYM.MoveFirst

  For i = 1 To Cant 
          dbBase_PT.Execute ("Insert into Tab_PT values('" & rsSYM.Fields(0) & "','" & rsSYM.Fields(1) & "','" & rsSYM.Fields(2) & "','" & rsSYM.Fields(3) & "','" & rsSYM.Fields(4) & "','" & rsSYM.Fields(5) & "','" & rsSYM.Fields(6) & "');")
          rsSYM.MoveNext

          DoEvents

  Next i
End Sub

It is posible to insert all the recordset ? Or how can I bring the table From "Sanchez_Mar.mdb" to "Base PT.accdb" faster?

Zaider Zaider · Accepted Answer · 2015-07-06T15:49:42

You can run an insert query using your other database as the source for the data.

"INSERT INTO Tab_PT (Field1, Field2, Field3, Field4, Field5, Field6, Field7) " & _
" SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7 " & _
" FROM [MS Access;DATABASE=PathToSanchez_MarDB].FINAL_Alarma_SYM"

Insert Complete RecordSet to another table in other database MS Access

2 Answers