Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Table From Code (2000)

    Hi,

    I have a table called tblWebUpdate. This table is made up of of the following fields:

    WebUpdateId - autonumber
    WebPropertyId - Number field to link to the property that needs updating
    WebSalesID - Track the user who orchestrated the change
    WebDate - Date the change occured
    Upload Date - Date Information was Uploaded to Website

    On our Property form on the afterupdate or certain events (information field that would be published to the web) it looks to see if there is already that property by it's propertyid number in the table and the upload date is blank. If it isn't, it adds it to the table, this is already working. If it already exisits, it doesn't need to add it again. Again this element is working.

    We have the vba code to export the information from a query to a csv file, which again works fine. What i need to do is to automatically go back and update the upload date fields for the contents of the query at that moment in time with todays date.

    How can I do this?
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Table From Code (2000)

    Something like

    Dim strSQL As String
    strSQL = "UPDATE [qrySomething] SET [Upload Date]=Date()"
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Table From Code (2000)

    Is there a way to do it without the yellow box coming up asking you if you want to update 'x' rows?
    Best Regards,

    Luke

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Update Table From Code (2000)

    I believe you can set the warnings to off
    Dim strSQL As String

    DoCmd.SetWarnings False

    strSQL = "UPDATE [qrySomething] SET [Upload Date]=Date()"
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    You can also turn them off in the Tools:Options:Edit Find and take the check out of Confirm Action Queries

    Ken

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Table From Code (2000)

    Ken,

    Thanks for response, works a treat.
    Best Regards,

    Luke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Table From Code (2000)

    Or use

    CurrentDb.Execute strSQL

    as I suggested in the comment in my original reply.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Update Table From Code (2000)

    Hans,

    Wow, I never used the CurrentDb.Execute before. That has some potential on my end, and reduces lines of code.

    Thanks for showing a different approach.

    Ken

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Table From Code (2000)

    One caveat, though.

    DoCmd.RunSQL is an Access command. It handles parameter queries and references to open forms.

    CurrentDb.Execute is a DAO command, it operates at a lower level. This definitely has advantages, but it doesn't recognize parameters and references to forms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •