By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 2,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

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

P: 24
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:

Expand|Select|Wrap|Line Numbers
  1. Sub TestProc()
  2. Dim lCount As Long
  3. Dim wbResults As Workbook
  4. Dim wbCodeBook As Workbook
  5.  
  6. Application.ScreenUpdating = False
  7. Application.DisplayAlerts = False
  8. Application.EnableEvents = False
  9.  
  10. On Error Resume Next
  11.  
  12. Set wbCodeBook = ThisWorkbook
  13.  
  14.     With Application.FileSearch
  15.         .NewSearch
  16.          'Remember to change path to local conditions of risk group
  17.         .LookIn = "C:\Documents and Settings\basbergb\Desktop\usethisone_risk\pristine\access\IncomingRiskCandidateFiles"
  18.         .FileType = msoFileTypeExcelWorkbooks
  19.         .Filename = "*.xls"
  20.  
  21.             If .Execute > 0 Then 'Workbooks in folder
  22.                 For lCount = 1 To .FoundFiles.Count 'Loop through all.
  23.                  Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
  24.  
  25.  
  26. Dim db As DAO.Database, rs As DAO.Recordset, r As Integer, Ans As Integer, myWS As Worksheet, crit As String
  27.     Set db = OpenDatabase("C:\Documents and Settings\basbergb\desktop\usethisone_risk\pristine\access\risk.mdb")
  28.     Set rs = db.OpenRecordset("CandidateRisk", dbOpenTable)
  29.     Set myWS = Sheets("Candidate Risk Worksheet")
  30.     Dim rs1 As DAO.Recordset
  31.     'get all records in a table
  32.     'r = 2 'starting row in the worksheet
  33.     'Do While Len(Range("A" & r).Formula) > 0
  34.     'Do While r < 4
  35.  
  36.  
  37.  
  38.    'rs1.FindFirst "title=myWS.Range('B7').Value"
  39.    'rs.FindFirst "CandidateRisk.title=bob"
  40.    'Debug.Print rs.Fields("title")
  41.     'Set rs1 = db.OpenRecordset("select * from CandidateRisk where title = '7'")
  42.     rs1.FindFirst ("title=7")
  43.     If rs1.EOF Then
  44.     'If rs.NoMatch Then
  45.  
  46.         With rs
  47.             .AddNew 'create a new record
  48.             ' add values to each field in the record
  49.             .Fields("title") = myWS.Range("B7").Value
  50.             .Fields("status") = myWS.Range("K7").Value
  51.             .Fields("IDby") = myWS.Range("B11").Value
  52.             .Fields("IPT_WGID") = myWS.Range("G11").Value
  53.             .Fields("dateID") = myWS.Range("K11").Value
  54.             .Fields("riskOwner") = myWS.Range("B14").Value
  55.             .Fields("IPT_WGRO") = myWS.Range("G14").Value
  56.             .Fields("dateAssigned") = myWS.Range("K14").Value
  57.             .Fields("dateFirstPresented") = myWS.Range("K17").Value
  58.             .Fields("ifThenPerf") = myWS.Range("C19").Value
  59.             .Fields("sitPerf") = myWS.Range("C20").Value
  60.             .Fields("LH_Perf") = myWS.Range("E21").Value
  61.             .Fields("CQ_Perf") = myWS.Range("E22").Value
  62.             .Fields("RHA_Perf") = myWS.Range("F23").Value
  63.             .Fields("ifThenCost") = myWS.Range("C19").Value
  64.             .Fields("sitCost") = myWS.Range("C20").Value
  65.             .Fields("LH_Cost") = myWS.Range("E21").Value
  66.             .Fields("CQ_Cost") = myWS.Range("E22").Value
  67.             .Fields("RHA_Cost") = myWS.Range("F23").Value
  68.             .Fields("ifThenSched") = myWS.Range("C19").Value
  69.             .Fields("sitSched") = myWS.Range("C20").Value
  70.             .Fields("LH_Sched") = myWS.Range("E21").Value
  71.             .Fields("CQ_Sched") = myWS.Range("E22").Value
  72.             .Fields("RHA_Sched") = myWS.Range("F23").Value
  73.             .Fields("DAESriskFactor") = myWS.Range("B40").Value
  74.             .Fields("reqRiskBasedOn") = myWS.Range("J40").Value
  75.  
  76.             .Update 'stores the new record
  77.         End With
  78.         Ans = MsgBox("Candidate Risk written to Access database", vbInformation, "Transferred Data")
  79.         'r = r + 1
  80.         'Loop
  81.  
  82.         Else
  83.         crit = "7"
  84.         rs.MoveFirst
  85.         rs.Index = "title"
  86.         rs.Seek "=", crit
  87.         MsgBox rs!Title
  88.  rs.Edit
  89.  
  90.     rs!Title = myWS.Range("B7").Value
  91.     rs!Status = myWS.Range("K7").Value
  92.     rs!IDby = myWS.Range("B11").Value
  93.     rs!IPT_WGID = myWS.Range("G11").Value
  94.     rs!dateID = myWS.Range("K11").Value
  95.     rs!riskOwner = myWS.Range("B14").Value
  96.     rs!IPT_WGRO = myWS.Range("G14").Value
  97.     rs!dateAssigned = myWS.Range("K14").Value
  98.     rs!dateFirstPresented = myWS.Range("K17").Value
  99.            rs!ifThenPerf = myWS.Range("C19").Value
  100.             rs!sitPerf = myWS.Range("C20").Value
  101.             rs!LH_Perf = myWS.Range("E21").Value
  102.             rs!CQ_Perf = myWS.Range("E22").Value
  103.             rs!RHA_Perf = myWS.Range("F23").Value
  104.             rs!ifThenCost = myWS.Range("C19").Value
  105.             rs!sitCost = myWS.Range("C20").Value
  106.             rs!LH_Cost = myWS.Range("E21").Value
  107.             rs!CQ_Cost = myWS.Range("E22").Value
  108.             rs!RHA_Cost = myWS.Range("F23").Value
  109.             rs!ifThenSched = myWS.Range("C19").Value
  110.             rs!sitSched = myWS.Range("C20").Value
  111.             rs!LH_Sched = myWS.Range("E21").Value
  112.             rs!CQ_Sched = myWS.Range("E22").Value
  113.             rs!RHA_Sched = myWS.Range("F23").Value
  114.             rs!DAESriskFactor = myWS.Range("B40").Value
  115.             rs!reqRiskBasedOn = myWS.Range("J40").Value
  116.  
  117.  rs.Update
  118.  Ans = MsgBox("Successfully edited the record", vbInformation, "Updated values")
  119. End If
  120.  
  121.         rs.Close
  122.         Set rs = Nothing
  123.         db.Close
  124.         Set db = Nothing
  125.  
  126.                  wbResults.Close SaveChanges:=True
  127.                  Next lCount
  128.             End If
  129.     End With
  130.  
  131.     On Error GoTo 0
  132.     Application.ScreenUpdating = True
  133.     Application.DisplayAlerts = True
  134.     Application.EnableEvents = True
  135. End Sub
  136.  
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.
Jan 4 '07 #1
Share this Question
Share on Google+
11 Replies


Expert 5K+
P: 8,434
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.
Jan 5 '07 #2

P: 24
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.
Jan 5 '07 #3

Expert 5K+
P: 8,434
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!
Jan 5 '07 #4

P: 24
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.
Jan 5 '07 #5

P: 24
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:
Expand|Select|Wrap|Line Numbers
  1. Sub TestProc()
  2. Dim lCount As Long
  3. Dim wbResults As Workbook
  4. Dim wbCodeBook As Workbook
  5.  
  6. Application.ScreenUpdating = False
  7. Application.DisplayAlerts = False
  8. Application.EnableEvents = False
  9.  
  10. 'On Error Resume Next
  11. Set wbCodeBook = ThisWorkbook
  12.  
  13.     With Application.FileSearch
  14.         .NewSearch
  15.          'Change path to local conditions of risk group
  16.         .LookIn = "C:\Documents and Settings\basbergb\Desktop\usethisone_risk\pristine\access\IncomingRiskCandidateFiles"
  17.         .FileType = msoFileTypeExcelWorkbooks
  18.         .Filename = "*.xls"
  19.  
  20.             If .Execute > 0 Then 'Workbooks in folder
  21.             For lCount = 1 To .FoundFiles.Count 'Loop through all.
  22.             'Open Workbook x and Set a Workbook variable to it
  23.             Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
  24.  
  25. 'I put some code I want to perform on each workbook between these lines
  26. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  27. 'Sub DAOFromExcelToAccess()
  28. 'exports data from the active worksheet to a table in an Access database
  29. Dim db As DAO.Database, rs As DAO.Recordset, r As Integer, Ans As Integer, myWS As Worksheet, crit As String
  30.     Set db = OpenDatabase("C:\Documents and Settings\basbergb\desktop\usethisone_risk\pristine\access\risk.mdb")
  31.     Set rs = db.OpenRecordset("CandidateRisk", dbOpenTable)
  32.     Set myWS = Sheets("Candidate Risk Worksheet")
  33.  
  34.     Dim STRrtitle As String
  35.     Dim vrtIDby As Variant
  36.  
  37. 'rs.Index = "riskIndex"
  38.  
  39.  
  40. 'Get rtitle in Excel
  41. STRrtitle = Range("B" & 7).Value
  42.  
  43. 'Get IDby in Excel
  44. 'vrtIDby = Range("B" & 11).Value
  45.  
  46. With rs
  47. .Index = "riskIndex"
  48. 'Find record in database
  49. .Seek "=", STRrtitle
  50. End With
  51.  
  52. 'if record not found in database, add it
  53.  
  54. If rs.NoMatch = True Then
  55. 'Record not found so create new record:
  56.  With rs
  57.             .AddNew 'create a new record
  58.             .Fields("rtitle") = myWS.Range("B7").Value
  59.             .Fields("status") = myWS.Range("K7").Value
  60.             .Fields("IDby") = myWS.Range("B11").Value
  61.             .Fields("IPT_WGID") = myWS.Range("G11").Value
  62.             .Fields("dateID") = myWS.Range("K11").Value
  63.             .Fields("riskOwner") = myWS.Range("B14").Value
  64.             .Fields("IPT_WGRO") = myWS.Range("G14").Value
  65.             .Fields("dateAssigned") = myWS.Range("K14").Value
  66.             .Fields("dateFirstPresented") = myWS.Range("K17").Value
  67.             .Fields("ifThenPerf") = myWS.Range("C19").Value
  68.             .Fields("sitPerf") = myWS.Range("C20").Value
  69.             .Fields("LH_Perf") = myWS.Range("E21").Value
  70.             .Fields("CQ_Perf") = myWS.Range("E22").Value
  71.             .Fields("RHA_Perf") = myWS.Range("F23").Value
  72.             .Fields("ifThenCost") = myWS.Range("C19").Value
  73.             .Fields("sitCost") = myWS.Range("C20").Value
  74.             .Fields("LH_Cost") = myWS.Range("E21").Value
  75.             .Fields("CQ_Cost") = myWS.Range("E22").Value
  76.             .Fields("RHA_Cost") = myWS.Range("F23").Value
  77.             .Fields("ifThenSched") = myWS.Range("C19").Value
  78.             .Fields("sitSched") = myWS.Range("C20").Value
  79.             .Fields("LH_Sched") = myWS.Range("E21").Value
  80.             .Fields("CQ_Sched") = myWS.Range("E22").Value
  81.             .Fields("RHA_Sched") = myWS.Range("F23").Value
  82.             .Fields("DAESriskFactor") = myWS.Range("B40").Value
  83.             .Fields("reqRiskBasedOn") = myWS.Range("J40").Value
  84.  
  85.             .Update 'stores the new record
  86.         End With
  87.         Ans = MsgBox("Candidate Risk written to Access database", vbInformation, "Transferred Data")
  88.  
  89. Else
  90.  
  91. 'Record is found so edit it
  92. With rs
  93.             .Edit
  94.             .Fields("rtitle") = myWS.Range("B7").Value
  95.             .Fields("status") = myWS.Range("K7").Value
  96.             .Fields("IDby") = myWS.Range("B11").Value
  97.             .Fields("IPT_WGID") = myWS.Range("G11").Value
  98.             .Fields("dateID") = myWS.Range("K11").Value
  99.             .Fields("riskOwner") = myWS.Range("B14").Value
  100.             .Fields("IPT_WGRO") = myWS.Range("G14").Value
  101.             .Fields("dateAssigned") = myWS.Range("K14").Value
  102.             .Fields("dateFirstPresented") = myWS.Range("K17").Value
  103.             .Fields("ifThenPerf") = myWS.Range("C19").Value
  104.             .Fields("sitPerf") = myWS.Range("C20").Value
  105.             .Fields("LH_Perf") = myWS.Range("E21").Value
  106.             .Fields("CQ_Perf") = myWS.Range("E22").Value
  107.             .Fields("RHA_Perf") = myWS.Range("F23").Value
  108.             .Fields("ifThenCost") = myWS.Range("C19").Value
  109.             .Fields("sitCost") = myWS.Range("C20").Value
  110.             .Fields("LH_Cost") = myWS.Range("E21").Value
  111.             .Fields("CQ_Cost") = myWS.Range("E22").Value
  112.             .Fields("RHA_Cost") = myWS.Range("F23").Value
  113.             .Fields("ifThenSched") = myWS.Range("C19").Value
  114.             .Fields("sitSched") = myWS.Range("C20").Value
  115.             .Fields("LH_Sched") = myWS.Range("E21").Value
  116.             .Fields("CQ_Sched") = myWS.Range("E22").Value
  117.             .Fields("RHA_Sched") = myWS.Range("F23").Value
  118.             .Fields("DAESriskFactor") = myWS.Range("B40").Value
  119.             .Fields("reqRiskBasedOn") = myWS.Range("J40").Value
  120.     .Update
  121. End With
  122.  
  123.  
  124. Ans = MsgBox("Successfully edited the record", vbInformation, "Updated values")
  125. End If
  126.                  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  127.  
  128.                  wbResults.Close SaveChanges:=True
  129.  
  130.                  Next lCount
  131.             End If
  132.     End With
  133.  
  134.     On Error GoTo 0
  135.     Application.ScreenUpdating = True
  136.     Application.DisplayAlerts = True
  137.     Application.EnableEvents = True
  138. End Sub
  139.  
Jan 6 '07 #6

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. rs.Index = Range("riskIndex")
-Adrian.
Jan 6 '07 #7

P: 24
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 :
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 6 '07 #8

P: 24
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.
Jan 6 '07 #9

Expert 5K+
P: 8,434
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...
Expand|Select|Wrap|Line Numbers
  1. Debug.Print "["; SomeVariable; "]"
Jan 6 '07 #10

P: 24
good idea.
Jan 7 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
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 :)
Jan 7 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.