Push specific data to Access Table/records

Hi all,

I am really new in programming and I am trying to build a vba code that is going to push specific data from an Excel Sheet to a specific Table/Record/Field in Access.
I am using Access 2007 but I can transfer my database to 2003 if necessary.
I posted my sheet here, to have a better idea of what I am trying to do. You guys are my last resort as I have been unable to find anything on internet.

Thank you

Test.xlsx

Table design:

Products <> Mapping <> Region <> Units_F <> Quarter_F <> Year_F <> ARPU <>

hey everyone, I have solved the code after browsing and changing some stuff around. Here is the code in case any needs it:

Sub ADOFromExcelToAccess()
' 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\GSS_Model_2.4.accdb;"
'Enter your path here
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Forecast_T", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 4 ' the start row in the worksheet
Do While Len(Range("C" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
.Fields("Products") = Range("C" & r).Value
.Fields("Mapping") = Range("A1").Value
.Fields("Region") = Range("B2").Value
.Fields("Units_F") = Range("E" & r).Value
.Fields("Quarter_F") = Range("E3").Value
.Fields("Year_F") = Range("E2").Value
.Fields("ARPU") = Range("D" & r).Value
.Update ' stores the new record
.AddNew ' create a new record
.Fields("Products") = Range("C" & r).Value
.Fields("Mapping") = Range("A1").Value
.Fields("Region") = Range("B2").Value
.Fields("Units_F") = Range("F" & r).Value
.Fields("Quarter_F") = Range("F3").Value
.Fields("Year_F") = Range("F2").Value
.Fields("ARPU") = Range("D" & r).Value
.Update ' stores the new record
.AddNew ' create a new record
.Fields("Products") = Range("C" & r).Value
.Fields("Mapping") = Range("A1").Value
.Fields("Region") = Range("B2").Value
.Fields("Units_F") = Range("G" & r).Value
.Fields("Quarter_F") = Range("G3").Value
.Fields("Year_F") = Range("G2").Value
.Fields("ARPU") = Range("D" & r).Value
.Update ' stores the new record
.AddNew ' create a new record
.Fields("Products") = Range("C" & r).Value
.Fields("Mapping") = Range("A1").Value
.Fields("Region") = Range("B2").Value
.Fields("Units_F") = Range("H" & r).Value
.Fields("Quarter_F") = Range("H3").Value
.Fields("Year_F") = Range("H2").Value
.Fields("ARPU") = Range("D" & r).Value
.Update ' stores the new record
.AddNew ' create a new record
.Fields("Products") = Range("C" & r).Value
.Fields("Mapping") = Range("A1").Value
.Fields("Region") = Range("B2").Value
.Fields("Units_F") = Range("I" & r).Value
.Fields("Quarter_F") = Range("I3").Value
.Fields("Year_F") = Range("I2").Value
.Fields("ARPU") = Range("D" & r).Value
.Update ' stores the new record
.AddNew ' create a new record
.Fields("Products") = Range("C" & r).Value
.Fields("Mapping") = Range("A1").Value
.Fields("Region") = Range("B2").Value
.Fields("Units_F") = Range("J" & r).Value
.Fields("Quarter_F") = Range("J3").Value
.Fields("Year_F") = Range("J2").Value
.Fields("ARPU") = Range("D" & r).Value
.Update ' stores the new record

End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

However, if someone knows how to make it better, saying that instead of only making a loop by row, if it could do it by column too (Which I do not know how to do, that's why I have so many .AddNew and . Update).

Also, if you have a piece of code that check the existing records and update or create new, that would be awesome!

Thanks

Yes, but it will not suit me as I have several sheets that are going to be connected to the same table.

I have been able to shrink the code to a minimum:

Sub ADOFromExcelToAccess()
' 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=\\GSS_Model_2.4.accdb;"
' open a recordset
Set Rs = New ADODB.Recordset
Rs.Open "Forecast_T", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
For i = 4 To 16
x = 0
Do While Len(Range("E" & i).Offset(0, x).Formula) > 0
' repeat until first empty cell in column A
With Rs
.AddNew ' create a new record
.Fields("Products") = Range("C" & i).Value
.Fields("Mapping") = Range("A1").Value
.Fields("Region") = Range("B2").Value
.Fields("ARPU") = Range("D" & i).Value
.Fields("Quarter_F") = Range("E3").Offset(0, x).Value
.Fields("Year_F") = Range("E2").Offset(0, x).Value
.Fields("Units_F") = Range("E" & i).Offset(0, x).Value
.Update
' stores the new record
End With
x = x + 1
Loop
Next i
Rs.Close
Set Rs = Nothing
cn.Close
Set cn = Nothing
End Sub

But it writes a new record each time, creating duplicates...

So I have worked on this code:

Sub ADOFromExcelToAccess()
' 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=\\GSS_Model_2.4.accdb;"
' open a recordset
Set Rs = New ADODB.Recordset
Rs.Open "Forecast_T", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
For i = 4 To 16
x = 0
Do While Len(Range("E" & i).Offset(0, x).Formula) > 0
If Rs.RecordCount > 0 Then
Rs.MoveFirst
If Rs.Fields("Region") = Range("B2") And Rs.Fields("Products") = Range("C" & i) And Rs.Fields("Quarter_F") = Range("E3").Offset(0, x).Value And Rs.Fields("Year_F") = Range("E2").Offset(0, x).Value Then
Rs.Fields("Units_F") = Range("E" & i).Offset(0, x).Value
End If
End If
x = x + 1
Loop
Next i
Rs.Close
Set Rs = Nothing
cn.Close
Set cn = Nothing
MsgBox "Forecast Has Been Updated"
End Sub


This updates only the first record but not the other one. I think I need to use another loop with .EOF but I am not sure.

Anyone can help me to solve this last piece of code?

Well,

here is the final. Hope it helps:

Please Login or Register  to view this content.