Updating acess from excel using ADO

Hello,
I use the below code I got from the internet to update my access from excel. Is there a way for the program to check and see if my project number which is in range("B2:B5600") exists in the database first. If yes then update the fields. If not then add the project number and the other fields. I thanks you in advance.


'The below code send the last row from excel to access
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.Jet.OLEDB.4.0; " & _
"Data Source=F:\excel\database\geo.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "enviro", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table

r = Range("C65500").End(xlUp).Row

' Do While Len(Range("D" & r).Formula) > 0
' repeat until first empty cell in column A
With rs

.AddNew

' add values to each field in the record
.Fields("Field1") = Range("A" & r).Value 'Job #
.Fields("Field2") = Range("B" & r).Value 'Grid
.Fields("Field3") = Range("C" & r).Value 'Project Name
.Fields("Field4") = Range("D" & r).Value 'Location
.Fields("Field5") = Range("E" & r).Value 'Client
' .Fields("Field8") = Range("H" & r).Value



' add more fields if necessary...
.Update ' stores the new record


End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

re: Updating acess from excel using ADO

Hi

Not sure how you are working this, but assuming that you can retieve the project number from the DB and save in a variable (ie PrjNo below), then you could do something like this. Obviously you would not need the messages in the finished code, that's just to prove/debug the code.

This seem a little too easy, so I am probable missing something!!


MTB

re: Updating acess from excel using ADO

Hi,

You can also use the "FIND" menthod..

CheckThis

Regards
Veena

re: Updating acess from excel using ADO

Thank you for you prompt respond.
I tried what you send me and I still can not make this work. I have modified the code I had but I am getting Run time error 3704. I am sending you the code and please help and let me know what I am doing wrong. I feel real dumb :( . Thanks again.

here is the code I modified.
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
For i = 3000 To 65000
project = Cells(i, 2)
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\geo.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "geo", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
' r = 3 ' the start row in the worksheet
Do While Len(Range("D" & i).Formula) > 0
' repeat until first empty cell in column A
With rs
strSQL = "select * from TableName where Field1 = 'project'"
' On Error Resume Next
.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText
' On Error GoTo 0
If .State = adStateOpen Then ' successfully opened the recordset
If .EOF Then ' no records returned
.AddNew ' create a new record
.Fields("Field2") = Range("B" & i).Value
.Fields("Field3") = Range("C" & i).Value
.Fields("Field4") = Range("D" & i).Value
.Fields("Field5") = Range("E" & i).Value
.Fields("Field6") = Range("F" & i).Value
.Fields("Field8") = Range("H" & i).Value
.Update ' stores the new record
Else ' one (or more records returned)
' edit existing record
.Fields("Field4") = Range("D" & i).Value
.Fields("Field5") = Range("E" & i).Value
.Fields("Field6") = Range("F" & i).Value
.Fields("Field8") = Range("H" & i).Value
.Update ' stores the new record
End If
.Close ' close the recordset
End If
End With
r = 1 + 1
Loop
Next i
Set rs = Nothing
End Sub