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