PDA

View Full Version : Saving data to Access databse



sujittalukde
02-02-2008, 12:48 AM
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
02-02-2008, 10:44 AM
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
02-03-2008, 11:37 PM
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
05-03-2008, 04:26 PM
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.