How to get bigdata from one table and insert into another in VBA?

Question

I have table with columns like key,English Phrase and that phrase with other 40 languages.See in following image :

I want to break the records of these table by it's language column like following image:

I did this using the following code:

Sub InsertIntoMasterPhrases()
    Dim objRecordsetMaster As ADODB.Recordset
    Set objRecordsetMaster = New ADODB.Recordset
    Dim objRecordset As ADODB.Recordset
    Set objRecordset = New ADODB.Recordset
    objRecordsetMaster.ActiveConnection = CurrentProject.Connection
    objRecordset.ActiveConnection = CurrentProject.Connection
    objRecordsetMaster.Open ("SELECT [Master Table].* FROM [Master Table];")
    While objRecordsetMaster.EOF = False
        objRecordset.Open ("Select  [SAP_LANGUAGE to LANG].[LANGUAGE NAME],  [SAP_LANGUAGE to LANG].[LANGUAGE] " & _
        "From [SAP_LANGUAGE to LANG]")
        While objRecordset.EOF = False
            key = objRecordsetMaster.Fields("Key").Value
            englishPhrase = objRecordsetMaster.Fields("English Phrase").Value
            language = objRecordset.Fields("LANGUAGE").Value
            translation = objRecordsetMaster.Fields(languageName).Value

            If (GetRecordsExist(CStr(key), CStr(englishPhrase), CStr(language)) = "") Then
                Query = "INSERT INTO [Language Sample](Key,English,Translation,Language)VALUES ('" & key & "','" & englishPhrase & "','" & translation & "','" & language & "');"
                CurrentDb.Execute Query
            End If
            objRecordset.MoveNext
        Wend
        objRecordset.Close
        objRecordsetMaster.MoveNext
    Wend
    objRecordsetMaster.Close
End Sub
//Checking records already exist in table
Function GetRecordsExist(key As String, english As String, language As String) As String
   Dim db As Database
   Dim Lrs As DAO.Recordset
   Dim LGST As String
   Set db = CurrentDb()
   Set Lrs = db.OpenRecordset("SELECT KEY FROM [Language Sample] where KEY='" & key & "' and English='" & english & "' and Language = '" & language & "'")
   If Lrs.EOF = False Then
      LGST = "Found"
   Else
      LGST = ""
   End If
   Lrs.Close
   Set Lrs = Nothing
   GetRecordsExist = LGST
End Function

In the Master table i have 15000 records and when its breaking 15000 records it becomes 15000 * 40 = 600000. above code inserting almost 10000 records per minutes and after few hour it' hangs up . But also it don't produce any error then i have to restart the access. Kindly help how can i do it in better way.

Answer 1

Alternative 1:

Use a large UNION query to append many records with one SQL statement, as described here:

How to simulate UNPIVOT in Access 2010?

You will probably want to split it into several chunks (e.g. 5 or 10 languages at a time), or Access might choke on the query.

Alternative 2:

Instead of running INSERT statements for each record, use a DAO recordset with .AddNew. This is faster by magnitudes, see this answer: https://stackoverflow.com/a/33025620/3820271