Update all fields from 1 table to another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

This is actually a further query to a post 3 days ago. I have posted it
separately as it asks a different question.

I want to copy one table to another table. They are both Access tables. They
both are identical in structure except one will have (a) new records and / or
(b) changed records to the original table.

I have used the:

strSQl = "INSERT INTO " & strTable & " SELECT * FROM tblTemp"
db.Execute strSQl

to insert new records

My question is:

What is the easiest way to update all the changed records?

Thankyou

Leonard
 
Tim Ferguson said:
UPDATE MyTable
SET MyField = NewValue
WHERE ChangedFlag = TRUE


Hope that helps


Tim F
Click to expand...

Tim thanks for that.

Could you tell me how to use this method with the other table.

so, tblOne has fields - fld1, fld2 etc

tblTwo has the same fields

Can I update all fields from tblTwo to tblOne at once or do I have to loop
through them?

Many thanks

Leonard
 
Leonard said:
Tim thanks for that.

Could you tell me how to use this method with the other table.

so, tblOne has fields - fld1, fld2 etc

tblTwo has the same fields

Can I update all fields from tblTwo to tblOne at once or do I have to loop
through them?

Many thanks

Leonard
Click to expand...


I've been working on it and got to this stage:

Dim strSQL As String
Dim strTable As String

Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field


strTable = "tblstudents"

Set tdf = DBEngine(0)(0).TableDefs(strTable)


For Each fld In tdf.Fields

If fld.Name = "studentid" Then

Else

strSQL = "Update " & strTable & " INNER JOIN tblTemp ON
tblstudents.studentid = tblTemp.studentid SET tblstudents." & fld.Name & " =
[tblTemp]![" & fld.Name & "];"

CurrentDb.Execute strSQL

End If

Next fld

MsgBox ("Finished")


Is there a more efficient way?

Thanks
 
Is there a more efficient way?
Click to expand...

I can't telll because I really don't understand what you are trying to
achieve. Your first post said something about copying one table to
another. This is something that I just never do; there may well be some
valid reason for doing this (it, after all, a large universe) but I
haven't found one so far. In any case, there certainly can't be any
sensible reason for keeping two "live" tables duplicated and updated. If
you need to refresh the copy, simply empty it and repopulate it from the
original

delete from newtable;

insert into newtable (fieldone, fieldtwo)
select fieldone, fieldtwo from oldtable;

If you really have to do what it looks like you are doing, you can do it
all in one command

update newtable inner join oldtable
on newtable.studentid = oldtable.studentid
set newtable.fieldone = oldtable.fieldone,
newtable.fieldtwo = oldtable.fieldtwo,
newtable.fieldthree = oldtable.fieldthree,
newtable.updated = date();


.... but I still suspect it's the wrong question.

All the best


Tim F
 
Tim Ferguson said:
I can't telll because I really don't understand what you are trying to
achieve. Your first post said something about copying one table to
another. This is something that I just never do; there may well be some
valid reason for doing this (it, after all, a large universe) but I
haven't found one so far. In any case, there certainly can't be any
sensible reason for keeping two "live" tables duplicated and updated. If
you need to refresh the copy, simply empty it and repopulate it from the
original

delete from newtable;

insert into newtable (fieldone, fieldtwo)
select fieldone, fieldtwo from oldtable;

If you really have to do what it looks like you are doing, you can do it
all in one command

update newtable inner join oldtable
on newtable.studentid = oldtable.studentid
set newtable.fieldone = oldtable.fieldone,
newtable.fieldtwo = oldtable.fieldtwo,
newtable.fieldthree = oldtable.fieldthree,
newtable.updated = date();


.... but I still suspect it's the wrong question.

All the best


Tim F
Click to expand...


Hi Tim

That's great!

Just what I was looking for. I appreciate your time.

Now, for more on the why.

I've developed a database for my school work. Works great. But my teaching
partner and I both need to do report cards. So, she takes a copy on a memory
stick and so do I.

We work on our reports at night - so much for teachers work being over at
3.00pm! Next day I need to join all the data to the main database for both of
our memory sticks. A type of mobile database.

Hence your solution.

Many thanks

Leonard
 
I've developed a database for my school work. Works great. But my
teaching partner and I both need to do report cards. So, she takes a
copy on a memory stick and so do I.
Click to expand...

Sounds like a perfect candidate for database replication... let the Access
team do all the hard work for you.

All the best


Tim F
 
Back
Top