Thanks Ajax,
Here is the full code:
Code:
Sub Test
Dim DbConn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Pth As String: Pth = Range("o2").Value
OpenDatabase = True
Set DbConn = New ADODB.Connection
Set Rs = New ADODB.Recordset
DbConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Pth & ";Persist Security Info=False;"
With Rs
.ActiveConnection = DbConn
.Source = "INSERT INTO " & AccessTableName & " SELECT *" & " FROM " & ListObjects("ExcelTableName")
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
DbConn.Close
Set DbConn = Nothing
End sub
this code sitting in Excel the VBE and supposed to copy all the information that I have in the Excel table and past it in the Access table that have the same field's name's in the same order like the Excel table have.
I hope now I was more clear
Thank you again.