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