0

I have an access 2007 database with two tables main and main1 both tables have the same design and the same fields (85 fields)

I want to update one of them from the other one , is there an easy way to do that? I know I can use update query but I see in this case I have to specify each field in both tables in the query design and that is hard for 85 fields.

So what should I do in this case ?

 UPDATE main 
 SET main.ID = [main1]![ID], 
 main.eng1job = [main1]![eng1job],   
 main.[eng1job-s] = [main1]![eng1job-s], 
 main.[eng1job-q] = [main1]![eng1job-q];
embert
  • 7,336
  • 10
  • 49
  • 78
user1921704
  • 179
  • 3
  • 4
  • 15
  • This might bot be supported in Access. Try one of the MSSQL approaches listed here: http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql – MPelletier Jan 26 '14 at 17:14

1 Answers1

0

Using VBA

dim rs1 as Recordset
dim rs2 as Recordset
dim i   as Integer
dim c   as Integer

    set rs1 = CurrentDb.OpenRecordset("main")
    set rs2 = CurrentDb.OpenRecordset("main1", dbOpenDynaset)
    c = rs1.Fields.Count - 1
    Do Until rs1.EOF
        rs2.AddNew
            For i = 0 To c
                rs2(i) = rs1(i)
            Next
        rs2.Update
        rs1.MoveNext
    Loop
mnieto
  • 3,744
  • 4
  • 21
  • 37
  • No. But If you want to use query designer: You can change the query type to update query. In the dialog box, select the target table. Then, select all the fields from the upper panel and drag them to the grid. Access will fill the update row with the matching fields. There is no need to hand write all the sql query – mnieto Jan 26 '14 at 19:24
  • ok i have put your code in a function and call it using on click event but i get this error Do Unitl rs1.EOF "syntax error" – user1921704 Jan 26 '14 at 21:54