Updating a Record in Access with VBA from Excel

[msoric]

Hi everyone! Long time lurker, first time poster! So I've been pulling my hair out over this. First, I should apologize that the code I post may be ugly.

Here's what I'm trying to do:
- I have a database on a network drive.
- I have an excel file that lots of people have on their machines.
- I would like excel to look through the database and do the following:
- if a exists, update the information from a cell in excel
- if a record doesn't exist, create it.

I've got the second part down pat. It works nicely. My problem lies in the first part. I don't know how to search the db. There are two fields on the excel sheet that have to match the fields in access before the third access field is updating from another cell on the sheet.

Here's what I have thus far - this just seems to overwrite an existing record:

    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
    stDB = "Z:\CA IT PMO\MPR\MPRSAPFIN.accdb"
     
    stCon = "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" & _
    "Data Source=" & stDB & ";"
     
    cnt.Open stCon
  
    rst.Open "MPR_Scope_and_Approach", cnt, 1, 3, adCmdTableDirect
    
With rst
    .Index = "PrimaryKey"
    .Seek Sheets("Project Summary").Cells(3, 3)
    If Not rst.EOF Then
        .Fields("FMS ID") = Sheets("Project Summary").Cells(3, 3).Value
        .Fields("Project ID") = Sheets("Project Summary").Cells(4, 3).Value
        .Fields("Info") = Sheets("Project Summary").Cells(18, 2).Value
    .Update
    Else
    MsgBox "Log Out Failed"
    End If
End With

Please be gentle - it's my first time :P

 

[xenou]

Hi,
welcome to the board.

In your database table, What is the primary key in the table? Are you using it to determine if a record already exists? I ask because this seems a little unclear and doesn't sound like it's using a primary key:
Quote: there are two fields on the excel sheet that have to match the fields in access before the third access field is updating from another cell on the sheet.

ξ

 

[ChrisM]

Always different ways of doing things. I would approach opening the recordset totally differently. You are opening the entire table then doing a search.

I would open the recordset with a sql statement that already incorporates the search:

dim sql as string

sql = "Select * From tblPeople Where FMS ID = " & Sheets("Project Summary").Cells(3, 3).Value   'add more criteria here

rst.Open sql, cnt, adOpenStatic

Now test if any records returned, if yes then you found a match.