How Insert Data into Table from Json through VBE Access

Question

I am trying to insert below data into access Table thorough vbe but only one record insert in table

Sample file as below:

{"GenericCorporateAlertRequest":\[{"Alert Sequence No":"575000001248141603241","Account number":"57500000124814","Amount":"4900","Mnemonic Code":"OTH","Remitter IFSC":"PUNB0020720","User Reference Number":"PUNBZ24076278426","Cheque No":"","Transaction Description":"NEFT Cr-PUNB0020720-SURAVI DAS-Zuari Finserv Ltd-PUNBZ24076278426","Remitter Name":"SURAVI DAS","Remitter Account":"0207010327572","Value Date":"2024-03-16","Virtual Account":"","Remitter Bank":"PUNJAB NATIONAL BANK","Transaction Date":"2024-03-16 09:01","Debit Credit":"Credit"}\]}

{"GenericCorporateAlertRequest":\[{"Alert Sequence No":"575000001248141603242","Account number":"57500000124814","Amount":"100","Mnemonic Code":"OTH","Remitter IFSC":"PUNB0020720","User Reference Number":"PUNBZ24076280247","Cheque No":"","Transaction Description":"NEFT Cr-PUNB0020720-SURAVI DAS-Zuari Finserv Ltd-PUNBZ24076280247","Remitter Name":"SURAVI DAS","Remitter Account":"0207010327572","Value Date":"2024-03-16","Virtual Account":"","Remitter Bank":"PUNJAB NATIONAL BANK","Transaction Date":"2024-03-16 09:01","Debit Credit":"Credit"}\]}

{"GenericCorporateAlertRequest":\[{"Alert Sequence No":"575000001248141603243","Account number":"57500000124814","Amount":"12050","Mnemonic Code":"IMPS","Remitter IFSC":"9002","User Reference Number":"407609524712","Cheque No":"","Transaction Description":"IMPS CR ECMS-9002-ZUFL14PJAK01-Mr  ANIL  KUMAR-00000037009771200","Remitter Name":"Mr  ANIL  KUMAR","Remitter Account":"00000037009771200","Value Date":"2024-03-16","Virtual Account":"ZUFL14PJAK01","Remitter Bank":"","Transaction Date":"2024-03-16 09:23","Debit Credit":"Credit"}\]}

{"GenericCorporateAlertRequest":\[{"Alert Sequence No":"575000001248141603244","Account number":"57500000124814","Amount":"30000","Mnemonic Code":"OTH","Remitter IFSC":"BARB0VJKIDW","User Reference Number":"BARBQ24076449811","Cheque No":"","Transaction Description":"NEFT Cr-BARB0VJKIDW-SWETA JINDAL W O MANISH JINDAL-ZUARI FINSERV LTD-BARBQ24076449811","Remitter Name":"SWETA JINDAL W O MANISH JINDAL","Remitter Account":"77540100001894","Value Date":"2024-03-16","Virtual Account":"","Remitter Bank":"BANK OF BARODA","Transaction Date":"2024-03-16 11:42","Debit Credit":"Credit"}\]}

Function fun_Check_HDFC_API_Records()
Dim FolderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object
'Dim FileTime As String
DoCmd.SetWarnings 0
Dim Sql1, SQL2 As String
Dim T1, T2 As String
Dim P As Integer
P = 0
  Dim VerData1, VerData2 As Variant
   VerData1 = Array("Alert Sequence No", "Account number", "Amount", "Mnemonic Code", "Remitter IFSC", "User Reference Number", "Cheque No", "Transaction Description", "Remitter Name", "Remitter Account", "Value Date", "Virtual Account", "Remitter Bank", "Transaction Date", "Debit Credit")
   VerData2 = Array("AlertSequenceNo", "Accountnumber", "Amount", "MnemonicCode", "RemitterIFSC", "UserReferenceNumber", "ChequeNo", "TransactionDescription", "RemitterName", "RemitterAccount", "ValueDate", "VirtualAccount", "RemitterBank", "TransactionDate", "DebitCredit")
   
DoCmd.RunSQL "delete * from GenericCorporateAlertRequest"
FileTime = 0
RecordNo = 0
'Set the file name to a variable
FolderName = "D:\auto file\HDFC Bank Live\" & Format(Date, "ddMMYYYY") & "\"

'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(FolderName)

'Use For Each loop to loop through each file in the folder
'For Each FSOFile In FSOFolder.Files
Dim strFile As String, strLine As String
'MsgBox FSOFile
'Debug.Print FSOFile.Name
'strFile = FolderName & "\" & FSOFile.Name
'hdfc_trxn_20032024.txt
'strFile = FolderName & "hdfc_trxn_20032024.txt"
strFile = FolderName & "HDFC_21032024.txt"
'strFile = FolderName & "hdfc_trxn_21032024.txt"
'New Text Document.txt

'Text_SP_LogFileName.Value = Mid(FSOFile, Len(FSOFile) - 9, 6)
Dim Name1 As String

'Add data into Table Start 101

'Dim strFile As String, strLine As String
Dim SequenceNo As Integer
SequenceNo = 1
'strFile = FSOFile.Name
   
  
   Open strFile For Input As #1
   Do Until EOF(1)
      Line Input #1, strLine
    
         
  For P = 0 To 13
   '1
   T1 = VerData1(P)
   T2 = VerData1(P + 1)
   VerData2(P) = Mid(strLine, InStr(strLine, T1) + Len(T1) + 3, InStr(strLine, T2) - InStr(strLine, T1) - (Len(T1)) - 6)
   If Len(VerData2(P)) = 0 Then
   VerData2(P) = VerData2(P) & "-"
   End If
      'VerData2(P) = Mid(strLine, InStr(strLine, VerData1(P)) + Len(VerData1(P)) + 3, InStr(strLine, VerData1(P + 1)) - InStr(strLine, VerData1(P)) - (Len(VerData1(P))) - 6)
   Name1 = Sequencno & VerData1(P) & " - " & VerData2(P)
   Debug.Print Name1
  
     
   T1 = VerData1(14)
   VerData2(14) = Mid(strLine, InStr(strLine, T1) + Len(T1) + 3, InStr(strLine, "}]}") - InStr(strLine, T1) - (Len(T1)) - 4)
  Name1 = Sequencno & VerData1(14) & " - " & VerData2(14)
   Debug.Print Name1
     
   Next P


Debug.Print SequenceNo
            
      Sql1 = "insert into GenericCorporateAlertRequest (SequenceNo, [Alert Sequence No], [Account number], [Amount], [Mnemonic Code], [Remitter IFSC], [User Reference Number], [Cheque No], [Transaction Description], [Remitter Name], [Remitter Account], [Value Date], [Virtual Account], [Remitter Bank], [Transaction Date], [Debit Credit]) values "
      SQL2 = "('" & SequenceNo & "', '" & VerData2(0) & "', '" & VerData2(1) & "', '" & VerData2(2) & "', '" & VerData2(3) & "', '" & VerData2(4) & "', '" & VerData2(5) & "', '" & VerData2(6) & "', '" & VerData2(7) & "', '" & VerData2(8) & "', '" & VerData2(9) & "', '" & VerData2(10) & "', '" & VerData2(11) & "', '" & VerData2(12) & "', '" & VerData2(13) & "', '" & VerData2(14) & "');"
      DoCmd.RunSQL Sql1 & SQL2
      Debug.Print Sql1 & SQL2
     
             'waitsecond (1)
   '   End If
         SequenceNo = SequenceNo + 1
      

         Debug.Print strLine
  
         Loop
       
      
   Close #1


'Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
strFile = ""



'DoCmd.TransferText acExportDelim, , Ex_Table_Name, "w:\sk\" & Ex_Table_Name & ".txt", 1

'Text_ODIN_Responce.Value = "File Generated..."

End Function