SQL update of Table1 by data in Table2 in all fields at once

[Milan Wendl, aaaengineering.com]

Hi,

I have 2 tables with same structure but varying records related/linked by ID
field in both tables offcourse.
I would like to update all fields at one shot with Update query at once
without listing all these fields with Set statement.
It seams so simple but I have wasted the half a day without any results.
Do I need to use DAO?

Thank you all for any sugestion.

Milan.

 

[John W. Vinson]

Hi,

I have 2 tables with same structure but varying records related/linked by ID
field in both tables offcourse.
I would like to update all fields at one shot with Update query at once
without listing all these fields with Set statement.

You can't... and why WOULD you?

Storing the same data in two tables is redundant and bad design. Relational
databases use the "Grandmother's Pantry Principle": "A place - ONE place! -
for everything, everything in its place". You should store information once,
in one table, and then link to it to combine that information with information
in other tables.

What's the reason for these two tables? Am I misunderstanding the structure?

It seams so simple but I have wasted the half a day without any results.
Do I need to use DAO?

That, or a query referencing all of the fields.

 

[Milan Wendl, aaaengineering.com]

Hi John,
It's typical onsite/offsite situation.
I have mechanical design company doing document management with 3D CAD
inteface, time tracking, purchasing and inventory database in Access for us
and our customers, which are using my program as well on their site too.
Customers have they own design team (onsite - their office) and us doing
design for them in our office (offsite to them).
Part numbers, revisions, assemblies, suppliers of the new and old parts are
than changed in parallel in real time.
At some point I am bringing a new design to their office and need to
reconsile this data (600 new part numbers etc.) with their data. People do
spelling errors, update quantities and much more.
We should somehow work on same database over the internet.
Table structure can change, so I was hoping to kind off not hard coding all
fields of tables in sql queries.
With help of late night studies, info of people like yourself (seen your
name offcourse) system is quite nice except there is improvement needed in
this synchronizing.
What are a big systems doing? Sharepoint, SQL Server etc?
We are all small companies and cannot afford 20 grand for some stuff.

Thanks for more help.

Milan.

 

[John W. Vinson]

What are a big systems doing? Sharepoint, SQL Server etc?

Those, or Citrix Server (or Windows Terminal Server, though I understand
Citrix is more robust). With Citrix you can have the database (frontend and
backend) on one server, or on a fast stable LAN attached to the Citrix server;
a remote user can log on, start a terminal session, and run the database as if
they were a local user.

Another possible option is Replication, which does work but is pretty
complicated and finicky to implement.

I can't think of any totally easy and transparent solution; it's a choice of
which set of problems you want to put up with!

 

[Milan Wendl, aaaengineering.com]

Hi John,
Thanks againt for your comments.
It's midnight in Canada (Toronto). I have spend the whole day on it using
VBA and DAO, doing it row by row.
So far so good.
I will check on your leads.

Thanks,
Milan.