Thread: Update access tabel from excel

I new for working in access tables can any one access me how to update access table row from excel.
Presently I successfully add the new records from excel to access (Got code from Google)- code as under

Sub up1()
Dim db As Database
Dim rs As Recordset
Dim r As Long
Dim myDB As String
Dim wks As Worksheet

Sheets("Index-DSD").Select
Range("A1").Select

myDB = "C:\DVS\DVS_DSV.mdb"


Set db = OpenDatabase(myDB)

' open the database
Set rs = db.OpenRecordset("Index-DSD", dbOpenTable)

' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("VER_NO") = Range("A" & r).Value
.Fields("TA") = Range("B" & r).Value
.Fields("GEO") = Range("C" & r).Value
.Fields("DB_GOLIVE_DATE") = Range("D" & r).Value
.Fields("DTE_DATA_LOAD") = Range("E" & r).Value
.Fields("STUDY_ID") = Range("F" & r).Value
.Fields("DOC_NAME") = Range("G" & r).Value
.Fields("SrcSheet") = Range("H" & r).Value
.Fields("Changes") = Range("I" & r).Value
.Fields("Keys") = Range("J" & r).Value
.Fields("Keys Comm") = Range("K" & r).Value
.Fields("Global") = Range("L" & r).Value
.Fields("Structure") = Range("M" & r).Value
.Fields("Variables") = Range("N" & r).Value
.Fields("Value Lists") = Range("O" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
MsgBox "Appended " & r - 2 & " Records to your database", vbOKOnly, "Confirmation"
End Sub

What changes I have o do so it will also update the existing records which help me to avoid duplicates.

Thanks in advance..

Maybe this is a dumb question, but why not just use the excel file itself as the source through linked table?

If you really want it to be a table in access, use excel as the linked source and do a select * into to recreate the entire table, rather than worrying about incremental changes.

Originally Posted by psumvp

Maybe this is a dumb question, but why not just use the excel file itself as the source through linked table?

If you really want it to be a table in access, use excel as the linked source and do a select * into to recreate the entire table, rather than worrying about incremental changes.

Hi,

I don't know how u call this is an dumb question or may be you not understand what i want to say.

I have 10 user who going to add data in one database and their manager run report from that.
Is it poosible that to use one access table linked to 10 user at the time??

Insted of Abusive kindly help me on this.....

Vish