OLEDB - How to insert records from collection into AccessDb by using OLEDB

[saagarmutyala]

Hi Team, How can we insert records into AccessDB by using OLEDB connection. Here, the scenario I have records in the form of "Collection". I want to export this collection to Access DB

[krishna.kodimurali9]

HI Sagar,

You can configure the connection using OLE DB and execute the query to insert the rows into access DB.

[ajaykumarchowdary143]

HI 
You had a collection.Same collection format only access db also there.
Create new page under OLEDB vbo "give name as Insert collection into access db"
Inputs are Collection and connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=mdb file location"

Code:
Dim moConnection As New OleDbConnection(connectionString)
moConnection.Open()
' Iterate over each row in the collection
For Each row As DataRow In Collection_DataItem.Rows
' Construct the SQL insert command for the current row
Dim insertQuery As String = "INSERT INTO [table name] ([Payment Total], [Invoice Number], [Amount Paid], [Remit ID], [Payment Reference], [Creation Date], [File path]) VALUES " & _
"('" & row("Payment Total") & "', '" & row("Invoice Number") & "', '" & row("Amount Paid") & "', '" & row("Remit ID") & "', '" & row("Payment Reference") & "', '" & row("Creation Date") & "', '" & row("File path") & "')"

' Create the command object
Dim oCommand As New OleDbCommand(insertQuery, moConnection)

' Execute the insert command for the current row
oCommand.ExecuteNonQuery()
Next

' Close the database connection
moConnection.Close()

Update as per needs. Above fields are my requirement. and its working.