Queries on tables from different data sources

[John]

Hi

I need to perform two queries in sequence on two identical access tables.
The source table is coming from a dataset returned by a web method and the
destination table is from a local access table. The queries are;

UPDATE [destTbl] INNER JOIN srcTbl ON [destTbl].entry_id = srcTbl.entry_id
SET [destTbl].field1 = [srcTbl].[field1], [destTbl].field2 =
[srcTbl].[field2];

and

INSERT INTO [destTbl] (field1, field2)
SELECT srcTbl.field1, srcTbl.field2
FROM srcTbl
WHERE (((srcTbl.entry_id) Not In (SELECT srcTbl.entry_id FROM [destTbl])));

I have two problems. a) What code do I need to tell the query that srcTble
is from the web method. b) There seem to be two connections; a web
connection and a local connection. What connection should I use to execute
the query?

If I have totally misunderstood how to carry this out then please let me
know.

Thanks

Regards

[Dino Chiesa [Microsoft]]

I think you asked the same question on the other newsgroup.

In short,
you cannot "perform a database query" across the web service.
"Performing the query" implies you are directly connected to the database.
If you got the dataset from a webservice, then you probably cannot update
the database table directly, because the db is probably accessible only from
the webservice that delivered the dataset to you.

So, if you want to update the db, then you'll need to send the modified
dataset back to the webservice, and have the webservice "perform the query"
(the update).

On the second query (the insert), I answered that separately. Obviously
here you are using a local db connection to your MS Access database.

-Dino
"John" <jo**@nospam.infovis.co.uk> wrote in message
news:eW*************@TK2MSFTNGP10.phx.gbl...

Hi

I need to perform two queries in sequence on two identical access tables.
The source table is coming from a dataset returned by a web method and the
destination table is from a local access table. The queries are;

UPDATE [destTbl] INNER JOIN srcTbl ON [destTbl].entry_id = srcTbl.entry_id
SET [destTbl].field1 = [srcTbl].[field1], [destTbl].field2 =
[srcTbl].[field2];

and

INSERT INTO [destTbl] (field1, field2)
SELECT srcTbl.field1, srcTbl.field2
FROM srcTbl
WHERE (((srcTbl.entry_id) Not In (SELECT srcTbl.entry_id FROM [destTbl])));
I have two problems. a) What code do I need to tell the query that srcTble
is from the web method. b) There seem to be two connections; a web
connection and a local connection. What connection should I use to execute
the query?

If I have totally misunderstood how to carry this out then please let me
know.

Thanks

Regards

[John]

Hi Dino

Thanks for this. I am not sure if I was clear enough. I am trying to update
the local db from the web service dataset. So update and insert is not a
problem. What puzzles me is this; what connection string I can use with the
da to perform a select on the web dataset to access the remote data which I
can then insert or update into the local db.

Thanks

Regards
"Dino Chiesa [Microsoft]" <di****@online.microsoft.com> wrote in message
news:OV**************@TK2MSFTNGP09.phx.gbl...

I think you asked the same question on the other newsgroup.

In short,
you cannot "perform a database query" across the web service.
"Performing the query" implies you are directly connected to the database.
If you got the dataset from a webservice, then you probably cannot update
the database table directly, because the db is probably accessible only from the webservice that delivered the dataset to you.

So, if you want to update the db, then you'll need to send the modified
dataset back to the webservice, and have the webservice "perform the query" (the update).

On the second query (the insert), I answered that separately. Obviously
here you are using a local db connection to your MS Access database.

-Dino
"John" <jo**@nospam.infovis.co.uk> wrote in message
news:eW*************@TK2MSFTNGP10.phx.gbl...

Hi

I need to perform two queries in sequence on two identical access tables. The source table is coming from a dataset returned by a web method and the destination table is from a local access table. The queries are;

UPDATE [destTbl] INNER JOIN srcTbl ON [destTbl].entry_id = srcTbl.entry_id SET [destTbl].field1 = [srcTbl].[field1], [destTbl].field2 =
[srcTbl].[field2];

and

INSERT INTO [destTbl] (field1, field2)
SELECT srcTbl.field1, srcTbl.field2
FROM srcTbl
WHERE (((srcTbl.entry_id) Not In (SELECT srcTbl.entry_id FROM

[destTbl])));

I have two problems. a) What code do I need to tell the query that srcTble is from the web method. b) There seem to be two connections; a web
connection and a local connection. What connection should I use to execute the query?

If I have totally misunderstood how to carry this out then please let me
know.

Thanks

Regards

[Dino Chiesa [Microsoft]]

"John" <jo**@nospam.infovis.co.uk> wrote in message
news:u4**************@tk2msftngp13.phx.gbl...

What puzzles me is this; what connection string I can use with the
da to perform a select on the web dataset to access the remote data which I can then insert or update into the local db.

The dataset you received from the webservice - there is no connection
string. The DataSet is a disconnected data cache. It has already been
populated by the time you receive it from the webservice. The data is not
remote at this point. the Dataset contains a local copy of the data that is
owned and managed by a remote database - the database that the webservice
queried for you.

You do not "select" from the dataset to access the data. You can just copy
the data, by calling da.Update(). But you will have to mark each datarow
as being updated, or the Update() will not apply.

This is what is illustrated in the following example:
http://www.winisp.net/cheeso/srcview...le=copydata.cs
-D
Thanks

Regards