CODEWITHSUNDEEP
ms-accessvba
Bruins1
Bruins1

Reputation: 35

VBA Update Existing Records

I was looking to setup VBA that would add new records to a table, if they did not currently exist (same ID #) and then update all of the columns for existing records (same ID #). I was able to setup the insert statement, but am coming up with errors for updating.

The code below states that I have a

Syntax error (missing operator) in query expression '[update].[date] from [update].

Any help you may provide would be very helpful.

Function Update()
    Dim dbs As Database
    Set dbs = CurrentDb

    dbs.Execute "Update historic_archive Set historic_archive.DATE = [update].[DATE] FROM [update], historic_archive WHERE [update].[id] = historic_archive.id;"

End Function

Upvotes: 2

Views: 759

Answers (2)

Krish
Krish

Reputation: 5917

Two things. 1. Please read more about SQL update command. 2. Never use names that resemble functions like date, update. Read more about naming conventions.

'Below update command will update historic date with update table.date where both id's are same.


Function Update() 
    Dim dbs As Database 
    Set dbs = CurrentDb 

    dbs.Execute "UPDATE historic_archive inner join [UPDATE] ON [update].[id] = historic_archive.id " & _
    "SET historic_archive.[DATE] = [update].[DATE];"
End Function

Upvotes: 3

n8.
n8.

Reputation: 1738

You probably want to change your "WHERE" to "ON"

Function Update()

  Dim dbs As Database

  Set dbs = CurrentDb

  dbs.Execute "UPDATE historic_archive " _
            & "SET historic_archive.DATE = [update].[DATE] " _
            & "FROM [update] " _
            & "JOIN historic_archive ON [update].[id] = historic_archive.id;"

End Function

Upvotes: 2

Related Questions