Repeated Issue - Transferring content from excel to access

dimsums

New Member
Joined
Jun 29, 2020
Messages
31
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
Thank you for your reply
Could you please give me a sample of what if i would have to put in there. I have removed the Do while and it copies all the lined within the range instead of copying only non blank ones
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top