Update remote table from local table

[chris.ciotti]

Greetings -
I'm using an Access front end to a SQL Server (2000) databas*e. Via
several steps, I create a temp table and manipulate the data* in it.
I
want to update the backend with this new data but my UPDATE *query
fails
as my temp table is local and the SQL database doesn't know *about it.

There are no linked tables in the FE database.
I have the following (DAO):
Set Db = CurrentDb
Set Qdf = Db.CreateQueryDef(TMP_QUERY_NAME)
Qdf.connect = ConnectString()
sqlString = "UPDATE tblRemote " & _
"SET " & _
"tblRemote.Some_Foo = tblLocal.Foo, " & _
"FROM tblRemote INNER JOIN tblLocal " & _
"ON tblRemote.Some_ID = tblLocal.Some_ID;"
Qdf.sql = sqlString
Qdf.ReturnsRecords = False
Qdf.Execute dbFailOnError
Is there any way of doing this without adding a linked table*?
Thanks,
chris

[dbmonitor]

When you say there are no linked tables in the FE, how are you
transfering the data from the back end to the front? After all,
wouldn't tblRemote have to be a link table itself? You are connecting
to the current access database to update the rows and if this table is
not a link table then you cannot update it on the sql server.

If you do not want a link table then you will need to create a new
connection to the SQL Server, retrieve the information from tblLocal
into a recordset and build and execute an update on the SQL Server for
each record in your set.

--
David Rowland
MS SQL Server DBMonitor author
http://dbmonitor.tripod.com

[chris ciotti]

On Sun, 30 Jan 2005 19:57:08 -0500, dbmonitor wrote
(in article <11**********************@z14g2000cwz.googlegroups .com>):

When you say there are no linked tables in the FE, how are you
transfering the data from the back end to the front? After all,
wouldn't tblRemote have to be a link table itself? You are connecting
to the current access database to update the rows and if this table is
not a link table then you cannot update it on the sql server.

Hi -

Thanks for the reply. I'm not using linked tables, I only connect (via code)
when necessary via the .Connect property of the Querydef object.
If you do not want a link table then you will need to create a new
connection to the SQL Server, retrieve the information from tblLocal
into a recordset and build and execute an update on the SQL Server for
each record in your set.

I'll give this a try, might you have some example code? It can be in any
language. Thanks.

--chris