Update Access Table (INSERT INTO)

[mukeshy12390]

Hi frnds,

I am trying to update Access table with worksheet data.
I can update 10 field or 20 , I will put field name manually in code.
But what about If I have 20 tables and 20 sheet in workbook , How can do it dynamically
like in loop.
Below code works fine but only 2 field ( which is I put manually )
You can assume first row in every worksheet contains heading.

It is not necessary u follow my code to edit I just need solutions or basic idea in form.
Any suggestion would be appreciated.


Option Explicit

Public Lpdata As ADODB.Recordset
Public Lpcon As ADODB.Connection
Public opath, ofilename, oextension As String
Public Accesstring As String

Sub Connection()
opath = Cells(1, 2) & "\"
ofilename = opath & Cells(2, 2)
oextension = ofilename & Cells(3, 2)
Debug.Print oextension
Accesstring = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & oextension & ";" & "Persist Security Info = false"
Set Lpcon = New ADODB.Connection
Lpcon.ConnectionString = Accesstring
Lpcon.Open
End Sub


Sub read_LPdata()
Dim c As Integer
Dim ostr As String
ostr = "INSERT INTO E" & "(ID,Fname)" & "Values(70000,'Batman vs SuperMan')"
Connection
Set Lpdata = New ADODB.Recordset
With Lpdata
.ActiveConnection = Lpcon
.Source = ostr
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open
End With
Lpcon.Close
Set Lpdata = Nothing
Set Lpcon = Nothing
End Sub