 MS Access SQl query/replace |
Welcome Guest ( Log In | Click here to Register a free account now! )
Register a free account to unlock additional features at BleepingComputer.com
Welcome to Bleeping Computer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.  |
 Aug 17 2009, 07:51 AM
Post
#1
|
|
 Senior Member  Group: Malware Study Hall Senior Posts: 374 Joined: 2-January 09 From: United Kingdom / Australia Member No.: 277,321  |
I was wondering if there's anyone who can help me out here. I have created an Access database with tables which have a due date (maintenance) field. Presently, the user updates this field when the maintenance has been carried out to indicate the new due date (say a month or a year down the line). What I have been asked to do though is to make it so that the database automatically updates itself. I.e. when the due date is passed the field will update depending upon the criteria for the next due date. (I know this leads it open that if a task doesn't get done it'll get ignored but I have been told that this would not happen!) So I thought I would do:
However, I'm not really that profficient with SQL code and this is definitely beyond me. (for example, how do I do the replace so that the date is updated?) Is creating a query the right way to go? Since I'm sure I can't do this sort of thing with the table alone? Also, if I have multiple tables which each require a different criteria [Due Date]+x can this all be combined or do I need to write out seperate queries (since I know union queries cannot be used to modify data?) Cheers, Casey -------------------- Member of the Bleeping Computer A.I.I. early response team!
|
 |
 
|
|
Aug 27 2009, 09:26 AM
Post
#2
|
|
|
Senior Member Group: Malware Study Hall Senior Posts: 374 Joined: 2-January 09 From: United Kingdom / Australia Member No.: 277,321 |
Ok, so just in case anyone reads this; this is how I did it:
CODE Dim answer answer = MsgBox("Are you sure? This will overwrite all data!", 32 + 1, "Confirmation") If answer = 1 Then Dim LResponse As Integer Dim db As Database Dim rec As Recordset Set db = CurrentDb() Set rec = db.OpenRecordset("Table A", dbOpenTable) rec.MoveFirst Do While Not rec.EOF Do While rec.Fields("Due Date") <= Date rec.Edit rec.Fields("Due Date") = DateAdd("ww", 1, rec.Fields("Due Date")) rec.Update Loop rec.MoveNext Loop LResponse = MsgBox("Database updated successfully", 0, "Task Completed") Else: End End If Just repeat the middle section from Set rec = db.OpenRecordset() to Loop for multiple tables. Casey -------------------- Member of the Bleeping Computer A.I.I. early response team!
|
|
|
|
|
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:
| Lo-Fi Version | Time is now: 21st March 2010 - 02:38 AM |
© 2003-2010 All Rights Reserved Bleeping Computer LLC.