Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update tables with identical structure (Access 2000)

    Can you help me with my update function? I want to update the tables Tbl1 and Tbl2
    in such a way as to update the table Table2 with the data from Table1.
    Public Function Dummy()
    Dim StrSQl As String
    On Error GoTo ErrHandler
    StrSQl = "UPDATE [" & Table1 & "] INNER JOIN [" & Table2 & "] " & _
    "ON [" & Table1 & "].[" & cityID & " ]=[" & _
    Table2 & "].[" & CityID & "] SET " & _
    "[" & Table1 & "].[city]=[" & Table2 & "].[city], " & _
    "[" & Table1 & "].[district]=[" & Table2 & "].[district]"
    ' Execute update query
    CurrentDb.Execute StrSQl, dbFailOnError
    Exit Function
    ErrHandler:
    ' Report error
    ' MsgBox Err.description, vbExclamation
    End Function

    I am afraid i couldnt manage that.

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

    Re: Update tables with identical structure (Access 2000)

    It's not clear what you are trying to do. You keep Table1, Table2 and CityID outside the quotes, so VBA will interpret them as variables, but you haven't assigned them a value, nor do you pass them as arguments to the function. Can you tell us what you want to accomplish?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update tables with identical structure (Access 2000)

    Thank you. I have rewritten my code since my code was not clear. But it is not good either.
    I want to copy the number of the field district from the table Tb2 into the district city of the table Tb1.For example, if the field district of the table Tb1 is 2 and the field district of the table Tb2 is 4,i want to make the field district of the table Tb1 to be 4.
    The field cityid is an autonumber

    Public Function Dummy()
    Dim StrSQl As String
    On Error GoTo ErrHandler
    Exit Function
    ErrHandler:

    StrSQl = "UPDATE Tbl INNER JOIN Tb2 " & _
    "ON Tbl.cityID=Tb2.CityID " & _
    "SET [Tb2].[city]=[Tb1].[city], " & _
    "[Tb2].[district]=[Tb1].[district];"
    CurrentDb.Execute StrSQl, dbFailOnError

    End Function

    Thank you in advance

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

    Re: Update tables with identical structure (Access 2000)

    You originally wrote that you wanted "to update the table Table2 with the data from Table1". Now you say that "if the field district of the table Tb1 is 2 and the field district of the table Tb2 is 4,i want to make the field district of the table Tb1 to be 4." That would mean that you update Table1 with the data from Table2.

    You have put the actual code in the error handler. The effect is that the Exit Function instruction is executed, and your update code isn't. Try placing the code above Exit Function.

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

    Re: Update tables with identical structure (Access 2000)

    Oh, and you have a mixture of Tbl (TB+letter L) and Tb1 (TB+digit 1) in your code. You must use the actual name of the table.

Posting Permissions

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