Saving data to Access databse

[sujittalukde]

I am using the following code to save data from excel to access database.
While executing the code I am facing the following problems:
1. It only saves the data of A2 and B2. I want to save data of all the rows say A3,B3,A4,B4 and so on to the database.
2. It saves the data already saved to dbase Say now I have made entry in A2,B2 and saved to dbase. Then again if I run the code it saves the same data of A2 and B2 to next row in the database. I want that if a record is already saved to database, the same should not be saved again.

Sub DataInput()
Dim con As Object
Dim rs As New ADODB.Recordset
 
Set con = CreateObject("ADODB.Connection")
con.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & "\mydb.mdb"
With rs
       .ActiveConnection = con
       .CursorLocation = adUseClient
       .CursorType = adOpenDynamic
       .LockType = adLockOptimistic
       .Source = "select * from table1"
       .Open
End With
Sheets("sheet1").Select
With rs
.AddNew
.Fields("name") = Range("A2").Value
.Fields("amount") = Range("B2").Value
.Update
End With
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
Range("A2").Select
End Sub

[akanchu]

Answer to 1.
If you happen to know the number of rows from the excel sheet then
you can try mixing a loop with your existing code.
say you have 10 rows to load data.

For i = 2 to 10
With rs
.AddNew
.Fields("name") = Range("A"&i).Value
.Fields("amount") = Range("B"&i).Value
.Update
End With
next i

Answer to 2.
you might have to put a search facility before filling the database or if possible make the database fields a primary key so that the database will discard the value being updated.. which ever works for you.

[sujittalukde]

THanks for the reply. For this:

you might have to put a search facility before filling the database

How can I do this programmatically?

[akanchu]

Hi,

you could just apply a select statement on the data in the table, if record found in the table, then do not insert the record.
If not found then insert the record.