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
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