sql server (alternate): Greetings -


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

There are no linked tables in the FE database.


I have the following (DAO):


Set Db =3D CurrentDb
Set Qdf =3D Db.CreateQueryDef(TMP_QUERY_NAME)


Qdf.connect =3D ConnectString()


sqlString =3D "UPDATE tblRemote " & _
"SET " & _
"tblRemote.Some_Foo =3D tblLocal.Foo, " & _
"FROM tblRemote INNER JOIN tblLocal " & _
"ON tblRemote.Some_ID =3D tblLocal.Some_ID;"


Qdf.sql =3D sqlString
Qdf.ReturnsRecords =3D False
Qdf.Execute dbFailOnError


Is there any way of doing this without adding a linked table=AD?
Thanks,=20


chris

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

[quoted text, click to view]
(in article <1107133028.393670.206590@z14g2000cwz.googlegroups.com>):

[quoted text, click to view]

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.

[quoted text, click to view]

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

--chris