I am trying to copy all the contents from range A2 to A6 in Sheet 1 to Table1 of my Access database but having no luck. I can transfer one piece of data at a time but no more with out using a loop to select each piece of data and transfer it to the database. Is this possible to transfer ranges from excel into access in one hit? My code is below. Thanks.
Dim cnn As Object
Dim rst As ADODB.Recordset
Dim MyConn As String
Set cnn = CreateObject("ADODB.Connection")
MyConn = "C:\Database1.mdb"
Set cnn = New ADODB.Connection
.Provider = "Microsoft.ACE.OLEDB.12.0"
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer