updating an Access DB with Excel data via VBA (am I off track here?)

[bbasberg]

Hello,

I have been struggling with this problem for DAYS and have googled my heart out as well as reading any books I could get my hands on but I cannot find any specific references to my problem. I have been able to successfully loop through a directory reading spreadsheets that insert records into an Access database. The spreadsheets in said directory are all of the same format but have different values in the cells. It's when I turned my attention to updating records that already exist in the Access DB that I have hit a brick wall.

I am NOT a VBA expert but have been trying to learn as quickly as possible. I'm sure I'm making lots of kludgy mistakes so I'll apologize in advance for the code that follows. I commented out other attempts I made to get it working. I am using DAO rather than ADO, and the application needs to be automated so I cannot have manual interventions hence the VBA code.

I'm hoping that it's just my neophyte status that is keeping it from working but maybe I cannot do it this way. In a nutshell, I don't know how to "tell" VBA that I want to compare an incoming cell in the Excel spreadsheet with the corresponding field in access to see if they are the same. If they are, of course I want to update not add a new record.

here is the code:

Sub TestProc()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
 
On Error Resume Next
 
Set wbCodeBook = ThisWorkbook
 
    With Application.FileSearch
        .NewSearch
         'Remember to change path to local conditions of risk group
        .LookIn = "C:\Documents and Settings\basbergb\Desktop\usethisone_risk\pristine\access\IncomingRiskCandidateFiles"
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = "*.xls"
 
            If .Execute > 0 Then 'Workbooks in folder
                For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
 
 
Dim db As DAO.Database, rs As DAO.Recordset, r As Integer, Ans As Integer, myWS As Worksheet, crit As String
    Set db = OpenDatabase("C:\Documents and Settings\basbergb\desktop\usethisone_risk\pristine\access\risk.mdb")
    Set rs = db.OpenRecordset("CandidateRisk", dbOpenTable)
    Set myWS = Sheets("Candidate Risk Worksheet")
    Dim rs1 As DAO.Recordset
    'get all records in a table
    'r = 2 'starting row in the worksheet
    'Do While Len(Range("A" & r).Formula) > 0
    'Do While r < 4
 
 
 
   'rs1.FindFirst "title=myWS.Range('B7').Value"
   'rs.FindFirst "CandidateRisk.title=bob"
   'Debug.Print rs.Fields("title")
    'Set rs1 = db.OpenRecordset("select * from CandidateRisk where title = '7'")
    rs1.FindFirst ("title=7")
    If rs1.EOF Then
    'If rs.NoMatch Then
 
        With rs
            .AddNew 'create a new record
            ' add values to each field in the record
            .Fields("title") = myWS.Range("B7").Value
            .Fields("status") = myWS.Range("K7").Value
            .Fields("IDby") = myWS.Range("B11").Value
            .Fields("IPT_WGID") = myWS.Range("G11").Value
            .Fields("dateID") = myWS.Range("K11").Value
            .Fields("riskOwner") = myWS.Range("B14").Value
            .Fields("IPT_WGRO") = myWS.Range("G14").Value
            .Fields("dateAssigned") = myWS.Range("K14").Value
            .Fields("dateFirstPresented") = myWS.Range("K17").Value
            .Fields("ifThenPerf") = myWS.Range("C19").Value
            .Fields("sitPerf") = myWS.Range("C20").Value
            .Fields("LH_Perf") = myWS.Range("E21").Value
            .Fields("CQ_Perf") = myWS.Range("E22").Value
            .Fields("RHA_Perf") = myWS.Range("F23").Value
            .Fields("ifThenCost") = myWS.Range("C19").Value
            .Fields("sitCost") = myWS.Range("C20").Value
            .Fields("LH_Cost") = myWS.Range("E21").Value
            .Fields("CQ_Cost") = myWS.Range("E22").Value
            .Fields("RHA_Cost") = myWS.Range("F23").Value
            .Fields("ifThenSched") = myWS.Range("C19").Value
            .Fields("sitSched") = myWS.Range("C20").Value
            .Fields("LH_Sched") = myWS.Range("E21").Value
            .Fields("CQ_Sched") = myWS.Range("E22").Value
            .Fields("RHA_Sched") = myWS.Range("F23").Value
            .Fields("DAESriskFactor") = myWS.Range("B40").Value
            .Fields("reqRiskBasedOn") = myWS.Range("J40").Value
 
            .Update 'stores the new record
        End With
        Ans = MsgBox("Candidate Risk written to Access database", vbInformation, "Transferred Data")
        'r = r + 1
        'Loop
 
        Else
        crit = "7"
        rs.MoveFirst
        rs.Index = "title"
        rs.Seek "=", crit
        MsgBox rs!Title
 rs.Edit
 
    rs!Title = myWS.Range("B7").Value
    rs!Status = myWS.Range("K7").Value
    rs!IDby = myWS.Range("B11").Value
    rs!IPT_WGID = myWS.Range("G11").Value
    rs!dateID = myWS.Range("K11").Value
    rs!riskOwner = myWS.Range("B14").Value
    rs!IPT_WGRO = myWS.Range("G14").Value
    rs!dateAssigned = myWS.Range("K14").Value
    rs!dateFirstPresented = myWS.Range("K17").Value
           rs!ifThenPerf = myWS.Range("C19").Value
            rs!sitPerf = myWS.Range("C20").Value
            rs!LH_Perf = myWS.Range("E21").Value
            rs!CQ_Perf = myWS.Range("E22").Value
            rs!RHA_Perf = myWS.Range("F23").Value
            rs!ifThenCost = myWS.Range("C19").Value
            rs!sitCost = myWS.Range("C20").Value
            rs!LH_Cost = myWS.Range("E21").Value
            rs!CQ_Cost = myWS.Range("E22").Value
            rs!RHA_Cost = myWS.Range("F23").Value
            rs!ifThenSched = myWS.Range("C19").Value
            rs!sitSched = myWS.Range("C20").Value
            rs!LH_Sched = myWS.Range("E21").Value
            rs!CQ_Sched = myWS.Range("E22").Value
            rs!RHA_Sched = myWS.Range("F23").Value
            rs!DAESriskFactor = myWS.Range("B40").Value
            rs!reqRiskBasedOn = myWS.Range("J40").Value
 
 rs.Update
 Ans = MsgBox("Successfully edited the record", vbInformation, "Updated values")
End If
 
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
 
                 wbResults.Close SaveChanges:=True
                 Next lCount
            End If
    End With
 
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub
 

Thank you (so much) in advance for reading my post and hopefully steering me in the right direction.

P.S. I see that advice is often "link to excel from access" but I don't think that will do it for me in this case, right?

Thanks again.

[Killer42]

The first thing that comes to mind is that you've defined variable rs1, then tried to search it, but it doesn't contain anything yet.

[bbasberg]

Thanks "killer" you are right. Toward the end I was getting desperate and trying things. Should I have two recordsets? How can I compare what is in the Access DB ("risk.mdb") with the record coming in from Excel? Each workbook with worksheet contains only one record. In other words, I'm looking at one incoming record from a single worksheet in each workbook in the directory.

Thanks. I'm going to be working on this all weekend so I'll be sure to see any reply that anyone is kind enough to make.

[Killer42]

Thanks "killer" you are right. Toward the end I was getting desperate and trying things. Should I have two recordsets? How can I compare what is in the Access DB ("risk.mdb") with the record coming in from Excel? Each workbook with worksheet contains only one record. In other words, I'm looking at one incoming record from a single worksheet in each workbook in the directory.

Thanks. I'm going to be working on this all weekend so I'll be sure to see any reply that anyone is kind enough to make.

I have to go now, but I'll try to find time to look in again over the weekend.

And sorry, but without studying the code for a while I couldn't say how many recordsets you need. But certainly you could try opening a second one in rs1, and see what happens. Maybe you could even make it a clone of rs - who knows. However you do it, there's no point searching a non-existent recordset.

Good luck!

[bbasberg]

I have to go now, but I'll try to find time to look in again over the weekend.

And sorry, but without studying the code for a while I couldn't say how many recordsets you need. But certainly you could try opening a second one in rs1, and see what happens. Maybe you could even make it a clone of rs - who knows. However you do it, there's no point searching a non-existent recordset.

Good luck!

Thanks, I will keep working but I am hoping someone can point me in the right direction as I don't really know how to compare the incoming excel data with the Access data to do the update. I really am lost as I've been trying for many days and still cannot get it. Any other gurus have any ideas or have solved this type of problem before?

Thanks.

[bbasberg]

I have been working hard to clean up my code but I am still wondering why all incoming records go to the "AddNew" part of the IF statement and never to the Edit alternative. I believe that it must be because my rs.index never really receives the value from the cell in the spreadsheet that it should use to compare to the corresponding field in the Access DB. I have named the field "rtitle" in Access and made it an index called riskIndex. I am an amateur at using debug and add watch but I tried to add watches for the variables and it appears that rs.Index is never getting the value of riskIndex but rather, "riskIndex" the string. I'm not sure about this though.

Is my syntax wrong. Please help, I have a lot of pressure on me to get this working and I'm just a newbie VB person.

Thanks!!

Here's the code:

Sub TestProc()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
 
'On Error Resume Next
Set wbCodeBook = ThisWorkbook
 
    With Application.FileSearch
        .NewSearch
         'Change path to local conditions of risk group
        .LookIn = "C:\Documents and Settings\basbergb\Desktop\usethisone_risk\pristine\access\IncomingRiskCandidateFiles"
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = "*.xls"
 
            If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
            'Open Workbook x and Set a Workbook variable to it
            Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
 
'I put some code I want to perform on each workbook between these lines
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Sub DAOFromExcelToAccess()
'exports data from the active worksheet to a table in an Access database
Dim db As DAO.Database, rs As DAO.Recordset, r As Integer, Ans As Integer, myWS As Worksheet, crit As String
    Set db = OpenDatabase("C:\Documents and Settings\basbergb\desktop\usethisone_risk\pristine\access\risk.mdb")
    Set rs = db.OpenRecordset("CandidateRisk", dbOpenTable)
    Set myWS = Sheets("Candidate Risk Worksheet")
 
    Dim STRrtitle As String
    Dim vrtIDby As Variant
 
'rs.Index = "riskIndex"
 
 
'Get rtitle in Excel
STRrtitle = Range("B" & 7).Value
 
'Get IDby in Excel
'vrtIDby = Range("B" & 11).Value
 
With rs
.Index = "riskIndex"
'Find record in database
.Seek "=", STRrtitle
End With
 
'if record not found in database, add it
 
If rs.NoMatch = True Then
'Record not found so create new record:
 With rs
            .AddNew 'create a new record
            .Fields("rtitle") = myWS.Range("B7").Value
            .Fields("status") = myWS.Range("K7").Value
            .Fields("IDby") = myWS.Range("B11").Value
            .Fields("IPT_WGID") = myWS.Range("G11").Value
            .Fields("dateID") = myWS.Range("K11").Value
            .Fields("riskOwner") = myWS.Range("B14").Value
            .Fields("IPT_WGRO") = myWS.Range("G14").Value
            .Fields("dateAssigned") = myWS.Range("K14").Value
            .Fields("dateFirstPresented") = myWS.Range("K17").Value
            .Fields("ifThenPerf") = myWS.Range("C19").Value
            .Fields("sitPerf") = myWS.Range("C20").Value
            .Fields("LH_Perf") = myWS.Range("E21").Value
            .Fields("CQ_Perf") = myWS.Range("E22").Value
            .Fields("RHA_Perf") = myWS.Range("F23").Value
            .Fields("ifThenCost") = myWS.Range("C19").Value
            .Fields("sitCost") = myWS.Range("C20").Value
            .Fields("LH_Cost") = myWS.Range("E21").Value
            .Fields("CQ_Cost") = myWS.Range("E22").Value
            .Fields("RHA_Cost") = myWS.Range("F23").Value
            .Fields("ifThenSched") = myWS.Range("C19").Value
            .Fields("sitSched") = myWS.Range("C20").Value
            .Fields("LH_Sched") = myWS.Range("E21").Value
            .Fields("CQ_Sched") = myWS.Range("E22").Value
            .Fields("RHA_Sched") = myWS.Range("F23").Value
            .Fields("DAESriskFactor") = myWS.Range("B40").Value
            .Fields("reqRiskBasedOn") = myWS.Range("J40").Value
 
            .Update 'stores the new record
        End With
        Ans = MsgBox("Candidate Risk written to Access database", vbInformation, "Transferred Data")
 
Else
 
'Record is found so edit it
With rs
            .Edit
            .Fields("rtitle") = myWS.Range("B7").Value
            .Fields("status") = myWS.Range("K7").Value
            .Fields("IDby") = myWS.Range("B11").Value
            .Fields("IPT_WGID") = myWS.Range("G11").Value
            .Fields("dateID") = myWS.Range("K11").Value
            .Fields("riskOwner") = myWS.Range("B14").Value
            .Fields("IPT_WGRO") = myWS.Range("G14").Value
            .Fields("dateAssigned") = myWS.Range("K14").Value
            .Fields("dateFirstPresented") = myWS.Range("K17").Value
            .Fields("ifThenPerf") = myWS.Range("C19").Value
            .Fields("sitPerf") = myWS.Range("C20").Value
            .Fields("LH_Perf") = myWS.Range("E21").Value
            .Fields("CQ_Perf") = myWS.Range("E22").Value
            .Fields("RHA_Perf") = myWS.Range("F23").Value
            .Fields("ifThenCost") = myWS.Range("C19").Value
            .Fields("sitCost") = myWS.Range("C20").Value
            .Fields("LH_Cost") = myWS.Range("E21").Value
            .Fields("CQ_Cost") = myWS.Range("E22").Value
            .Fields("RHA_Cost") = myWS.Range("F23").Value
            .Fields("ifThenSched") = myWS.Range("C19").Value
            .Fields("sitSched") = myWS.Range("C20").Value
            .Fields("LH_Sched") = myWS.Range("E21").Value
            .Fields("CQ_Sched") = myWS.Range("E22").Value
            .Fields("RHA_Sched") = myWS.Range("F23").Value
            .Fields("DAESriskFactor") = myWS.Range("B40").Value
            .Fields("reqRiskBasedOn") = myWS.Range("J40").Value
    .Update
End With
 
 
Ans = MsgBox("Successfully edited the record", vbInformation, "Updated values")
End If
                 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
                 wbResults.Close SaveChanges:=True
 
                 Next lCount
            End If
    End With
 
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub
 

[NeoPa]

Barbara,
It's late here now so I can't look in too much detail, but you ask about the rs.index. In the version of the code posted, rs.index appears only once where you are setting it to the string "riskIndex" (Line 37 - 'rs.Index = "riskIndex") - EXCEPT this is a commented out line. Assuming you tested it before it was commented out this would give you the result you had.
I'm guessing a little, but do you want to say :

rs.Index = Range("riskIndex")

-Adrian.

[bbasberg]

Barbara,
It's late here now so I can't look in too much detail, but you ask about the rs.index. In the version of the code posted, rs.index appears only once where you are setting it to the string "riskIndex" (Line 37 - 'rs.Index = "riskIndex") - EXCEPT this is a commented out line. Assuming you tested it before it was commented out this would give you the result you had.
I'm guessing a little, but do you want to say :

rs.Index = Range("riskIndex")

-Adrian.

Thanks for the reply Adrian. Even though it's almost 1 am here I had to try your suggestion. I'm wondering about it though (after I got an error) because riskIndex is an index in the Access database while the range for the excel spreadsheet (actually it's just one cell) is called STRrtitle. The field in the database is called rtitle. It is only the index that's called riskIndex. Also, I have another reference to rs.index that isn't commented out in a With...End With block a little further down.

I hope you have a chance to reply again. I am very grateful. I have finally started to lose my mind over this problem as it has been consuming me for over a week...
Sorry for all the related and confusing variable/index names.

Barbara

[bbasberg]

Thanks anyway. I got it working. It turns out everything worked great once I saw that I was looking in the excel workbook the program was running from rather than the individual workbooks that I was processing. So the value in the cell of the incoming workbook was never being compared to the riskIndex in the Access database.

I REALLY learned the value of using strategically placed Debug.Print statements and debug with step into!! I had thought I didn't know how to use Debug.Print because I wasn't getting anything in my immediate window. Well, duh, I was looking at a blank cell in the excel spreadsheet I was running the program from instead of the cell in each individual workbook. So qualifying the assignment :
STRrtitle = myWS.Range("rtitle").Value
instead of this:
STRrtitle = Range("rtitle").Value

did the trick. Turns out the riskIndex syntax and logic were fine.

[Killer42]

Glad you got it sorted.

When printing values for debugging, I almost always put some sort of delimiter around them, to make sure I know what I'm seeing. For example...

Debug.Print "["; SomeVariable; "]"

[bbasberg]

good idea.

[NeoPa]

Thanks for the reply Adrian. Even though it's almost 1 am here I had to try your suggestion. I'm wondering about it though (after I got an error) because riskIndex is an index in the Access database while the range for the excel spreadsheet (actually it's just one cell) is called STRrtitle. The field in the database is called rtitle. It is only the index that's called riskIndex. Also, I have another reference to rs.index that isn't commented out in a With...End With block a little further down.

I hope you have a chance to reply again. I am very grateful. I have finally started to lose my mind over this problem as it has been consuming me for over a week...
Sorry for all the related and confusing variable/index names.

Barbara

Almost 01:00 - mine was past 03:00 ;)
This of course, was prepared yesterday for a reply but I was running late for my nephew's surprise 18th birthday party so I had to rush out quickly.
I'm delighted that you've managed to sort out your problem on your own. If truth be told, that's always a better resolution as you learn better that way.
Not always practical or possible though.
Killer's advice is, as always, worth following. Debugging is a very important part of finding and fixing problems - please ask in here for tips if required.
The Locals window, for instance, can be very useful.
Anyway, this one's fixed so that's all good :)