Repeated Issue - Transferring content from excel to access

dimsums

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I seemed to have posted this elsewhere but i can't find my post, so sorry if it is a repeat.
I have a code that allows me to transfer a few rows (3-16) from excel to access.
However when i check the access table it has repeated transferred rows, for example row 3 will be copied 6 times before going to row 4.
The code is as below, was wondering if someone could help me see where the issue stems from.

Sub ADOFromExcelToAccess2()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Users\smriti.singh\Desktop\Test.accdb;" 'this is where you put the address of the acces file
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable 'this is where the name of the table is put in, rn it is Table1 we can change it
' all records in a table
For i = 3 To 16 'this is what is taken from the spreadsheet so it will start from row 3 and go to 16
x = 0
Do While Len(Range("c" & i).Offset(0, x).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
.Fields("Nature") = Range("A" & i).Value
.Fields("No") = Range("B" & i).Value
.Fields("NameP") = Range("C" & i).Value
.Update
' stores the new record
End With
x = x + 1
Loop
Next i
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
Hi Dimsums
to me it looks like you have your for loop to cover each line, then you have a do..while loop that cycles through the lines and repeats the data transfer of that line for the number of lines that have data. I don't think you need to have the do..while loop in there. only an if to confirm there's something to transfer.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,302
Members
410,545
Latest member
Upsindustrial20
Top