Home Posts Topics Members FAQ
bbasberg
24 New Member
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
- SubTestProc()
- DimlCountAsLong
- DimwbResultsAsWorkbook
- DimwbCodeBookAsWorkbook
- Application.ScreenUpdating=False
- Application.DisplayAlerts=False
- Application.EnableEvents=False
- OnErrorResumeNext
- SetwbCodeBook=ThisWorkbook
- WithApplication.FileSearch
- .NewSearch
- 'Remembertochangepathtolocalconditionsofriskgroup
- .LookIn="C:\DocumentsandSettings\basbergb\Desktop\usethisone_risk\pristine\access\IncomingRiskCandidateFiles"
- .FileType=msoFileTypeExcelWorkbooks
- .Filename="*.xls"
- If.Execute>0Then'Workbooksinfolder
- ForlCount=1To.FoundFiles.Count'Loopthroughall.
- SetwbResults=Workbooks.Open(Filename:=.FoundFiles(lCount),UpdateLinks:=0)
- DimdbAsDAO.Database,rsAsDAO.Recordset,rAsInteger,AnsAsInteger,myWSAsWorksheet,critAsString
- Setdb=OpenDatabase("C:\DocumentsandSettings\basbergb\desktop\usethisone_risk\pristine\access\risk.mdb")
- Setrs=db.OpenRecordset("CandidateRisk",dbOpenTable)
- SetmyWS=Sheets("CandidateRiskWorksheet")
- Dimrs1AsDAO.Recordset
- 'getallrecordsinatable
- 'r=2'startingrowintheworksheet
- 'DoWhileLen(Range("A"&r).Formula)>0
- 'DoWhiler<4
- 'rs1.FindFirst"title=myWS.Range('B7').Value"
- 'rs.FindFirst"CandidateRisk.title=bob"
- 'Debug.Printrs.Fields("title")
- 'Setrs1=db.OpenRecordset("select*fromCandidateRiskwheretitle='7'")
- rs1.FindFirst("title=7")
- Ifrs1.EOFThen
- 'Ifrs.NoMatchThen
- Withrs
- .AddNew'createanewrecord
- 'addvaluestoeachfieldintherecord
- .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'storesthenewrecord
- EndWith
- Ans=MsgBox("CandidateRiskwrittentoAccessdatabase",vbInformation,"TransferredData")
- 'r=r+1
- 'Loop
- Else
- crit="7"
- rs.MoveFirst
- rs.Index="title"
- rs.Seek"=",crit
- MsgBoxrs!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("Successfullyeditedtherecord",vbInformation,"Updatedvalues")
- EndIf
- rs.Close
- Setrs=Nothing
- db.Close
- Setdb=Nothing
- wbResults.CloseSaveChanges:=True
- NextlCount
- EndIf
- EndWith
- OnErrorGoTo0
- Application.ScreenUpdating=True
- Application.DisplayAlerts=True
- Application.EnableEvents=True
- EndSub
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
Subscribe Reply
11 5754 
- <
- 1
- 2
bbasberg 24 New Member
good idea.
Jan 7 '07 #11
reply
NeoPa 32,564 Recognized Expert Moderator MVP
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
reply
- <
- 1
- 2
Sign in to post your reply or Sign up for a free account.
Similar topics
| 5 4464 Updating Linked Table - why is action query failing? by: deko |last post by: How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this error: Deleting data in a linked table is not supported by this ISAM. From what I understand, indexed sequential access method (ISAM) drivers are... Microsoft Access / VBA |
| 7 2509 Future of MS Access...... by: Ronnie |last post by: I'm curious if anyone has any insights into what Microsoft has in store for ACCESS in current or future releases? I'm currently working on Access 2000 and haven't seen the newer versions. I'm curious if Microsoft will keep VBA or move ACCESS over to VB.NET or C#? Thanks. Microsoft Access / VBA |
| 6 3660 When should you upgrade access 97/vba applications? by: Terry Bell |last post by: We've had a very large A97 app running fine for the last seven years. I've just converted to SQL Server backend, which is being tested, but meanwhile the JET based version, running under terminal server, has suddenly started running very slowly. The network engineer has thrown up his hands and said "It's Access 97". I've checked out lots of... Microsoft Access / VBA |
| 2829 Controlling Excel from Access: Using Sendkeys to enter data into an Excel Input Box by: Jack |last post by: Windows 2K Pro Access/Excel 2003 Hi there, I have a large number of password protected Excel Workbooks. The files are protected by code that runs when the workbook opens. The code asks the user to key in a password into an input box, the password is compared to a a partciular cell on a hidden (xlVeryHidden) sheet and if it matches the... Microsoft Access / VBA |
| 37 5200 How will Access Survive by: jasmith |last post by: How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why would you even continue to use Access as a backend when you have a much superior option in SQL express? What about as a future front-end... Microsoft Access / VBA |
| 25 2253 Is Access bogus, or is it me? by: DFS |last post by: I have a job to automatically import Excel data and post to database tables, via a point-click interface. Choose-file-and-it-does-the-rest kind of thing. Cient stores data in columns in his sheet (I told them it should be in rows, but that's another issue). Sometimes the sheet gets new columns added. At that point, Access/import spec... Microsoft Access / VBA |
| 1 5726 Switching between Access and Excel using code by: garry.oxnard |last post by: Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access database - containing a list of addresses. Sequence of events is = (1) Excel template opens in its default XXX.xls filename. (2) Code runs to save... Microsoft Access / VBA |
| 4 3332 Updating Access Report using Excel by: somanyusernamesaretakenal |last post by: What I am trying to achieve: Basically I have generated a report in access. This report needs to be updated using excel. (Updating the new data, not changing existing data) What I did was I imported the existing report into Excel (Tools Office Links Analyze It with MS Excel). Then I linked the report to the Excel file. (File Get... Microsoft Access / VBA |
| 4 1934 Somewhat novice Access user, tripped up in a query problem by: pokerboy801 |last post by: OK, I will try to explain this as clearly and as concise as possible. I am using Access, which has three MS Excel Linked tables, to store call center metrics for reps. My Excel workbook has three separate worksheets, all linked to Access. I have a query that performs some minor calculations, not to hard, and will be ued to drive a report.... Microsoft Access / VBA |
| 8111
Problem With Comparison Operator <=> in G++ by: Oralloy |last post by: Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ... C / C++ |
| 8274
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing by: jinu1996 |last post by: In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that... Online Marketing |
| 1 7863 The easy way to turn off automatic updates for Windows 10/11 by: Hystou |last post by: Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For... Windows Server |
| 8140
Discussion: How does Zigbee compare with other wireless protocols in smart home applications? by: tracyyun |last post by: Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the... General |
| 6514
AI Job Threat for Devs by: agi2029 |last post by: Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then... Career Advice |
| 5336 Couldnt get equations in html when convert word .docx file to html file in C#. by: conductexam |last post by: I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert... C# / C Sharp |
| 3776 Trying to create a lan-to-lan vpn between two differents networks by: TSSRALBI |last post by: Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in... Networking - Hardware / Configuration |
| 3795 Windows Forms - .Net 8.0 by: adsilva |last post by: A Windows Forms form does not have the event Unload, like VB6. What one acts like? Visual Basic .NET |
| 1 2282 transfer the data from one system to another through ip address by: 6302768590 |last post by: Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system C# / C Sharp |
By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.
To disable or enable advertisem*nts and analytics tracking please visit the page.
