Newbie - Update Query into separate database

HI there,

Is it possible to use an update query to update fields in a separate
database from the main database where the query sits? I want to run an
update query from the table in G:\Source.mdb to the table F:
\Destination.mdb

The table (tbl_Projects) is exactly the same in both databases. I am
running an append query to add new fields - but I would like to also
update any existing fields that have been changed in G:\Source/mdb.

If not - is there any other way of doing this? I've tried linking /
importing / exporting the tables but the results haven't been correct.

Many thanks in advance
Tim

Re: Newbie - Update Query into separate database

On Apr 8, 1:06=A0pm, timothy.b...@gmail.com wrote:
> HI there,
>
> Is it possible to use an update query to update fields in a separate
> database from the main database where the query sits? I want to run an
> update query from the table in G:\Source.mdb to the table F:
> \Destination.mdb
>
> The table (tbl_Projects) is exactly the same in both databases. I am
> running an append query to add new fields - but I would like to also
> update any existing fields that have been changed in G:\Source/mdb.
>
> If not - is there any other way of doing this? I've tried linking /
> importing / exporting the tables but the results haven't been correct.
>
> Many thanks in advance
> Tim

In:

http://groups.google.com/group/microsoft.public.access/msg/e f639becbdf2faee

I said,

qryGetProDate:
SELECT * FROM [M:\Databases\DB2.mdb].tblImageBuild INNER JOIN [M:
\Databases\DB2.mdb].tblHardwareRequest ON tblImageBuild.HID =3D
tblHardwareRequest.HID ORDER BY tblImageBuild.HID,
tblHardwareRequest.DeliveryDate;


I suspect that this remarkable syntax, which I happened upon in
microsoft.public.access in a post from Jamie Collins, should also work
for your update query. It seems much handier than "IN 'M:\Databases
\DB2.mdb'" because Access seems to get confused quickly with that
construct when there is a local table with the same name as the remote
table. IIRC, his example even showed a way to create an editable
query to an Excel worksheet from Access!

Post back if you need help in coming up with a plan, including an
example of what you are trying to accomplish.

James A. Fortune
CDMAPoster@FortuneJames.com

Re: Newbie - Update Query into separate database

wrote in message
news:78cf1210-85e9-4d74-9952-d0dc8f7506f2@l42g2000hsc.google groups.com...
On Apr 8, 1:06 pm, timothy.b...@gmail.com wrote:
> HI there,
>
> Is it possible to use an update query to update fields in a separate
> database from the main database where the query sits? I want to run an
> update query from the table in G:\Source.mdb to the table F:
> \Destination.mdb
>
> The table (tbl_Projects) is exactly the same in both databases. I am
> running an append query to add new fields - but I would like to also
> update any existing fields that have been changed in G:\Source/mdb.
>
> If not - is there any other way of doing this? I've tried linking /
> importing / exporting the tables but the results haven't been correct.
>
> Many thanks in advance
> Tim

In:

http://groups.google.com/group/microsoft.public.access/msg/e f639becbdf2faee

I said,

qryGetProDate:
SELECT * FROM [M:\Databases\DB2.mdb].tblImageBuild INNER JOIN [M:
\Databases\DB2.mdb].tblHardwareRequest ON tblImageBuild.HID =
tblHardwareRequest.HID ORDER BY tblImageBuild.HID,
tblHardwareRequest.DeliveryDate;


I suspect that this remarkable syntax, which I happened upon in
microsoft.public.access in a post from Jamie Collins, should also work
for your update query. It seems much handier than "IN 'M:\Databases
\DB2.mdb'" because Access seems to get confused quickly with that
construct when there is a local table with the same name as the remote
table. IIRC, his example even showed a way to create an editable
query to an Excel worksheet from Access!

Post back if you need help in coming up with a plan, including an
example of what you are trying to accomplish.

James A. Fortune
CDMAPoster@FortuneJames.com

That is slick,
I just tried it on a table in a 3rd party DB, it appears to opened the table
quicker the same tabled linked. Any ideal why?

Re: Newbie - Update Query into separate database

Hi James - many many thanks for that. I'll give it a go and let you
know how I get on!

Tim

Re: Newbie - Update Query into separate database

On Apr 8, 3:50=A0pm, "paii, Ron" wrote:

> That is slick,
> I just tried it on a table in a 3rd party DB, it appears to open the table=

> quicker than the same tabled linked. Any idea why?

That's a great observation and a great question. Perhaps it is faster
because it does not have to notice that the table is linked and look
up the connection information in order to find the data. But I'm only
guessing. I'm just glad that syntax works at all. Getting rid of
some really ugly workarounds elegantly, IMO, is worth it even if the
syntax makes the query run a little slower. A performance boost would
be an added bonus. The SQL books I have are of no use in
understanding what you saw. Even looking at the source code of, say,
MySQL would only give clues as to how a connection to data is made
without involving linked tables in Access. I suspect that SMB is
involved also. I would love to be able to answer your question
definitively so I welcome any links from anyone to information that
goes into detail about the process Access goes through in obtaining
data from linked tables.

James A. Fortune
CDMAPoster@FortuneJames.com