Update remote table from local table

chris.ciotti_at_gmail.com
Date: 01/28/05 

Date: 28 Jan 2005 12:38:40 -0800

Greetings -

I'm using an Access front end to a SQL Server (2000) database. 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

Relevant Pages

  • RE: Merge 2 different tables
    ... I finally got a chance to look at the database you sent. ... You mention that it's linked to SQL Server. ... You have a union query built upon other union queries. ... The Temp DB I was sending you worked on the fact I imported the tables from ...
    (microsoft.public.access.queries)
  • Re: temp database transaction log fills up
    ... > I have an unusual situation with Sql Server 7, ... > errorlog that transaction log of the temp database fills up. ...
    (microsoft.public.sqlserver.server)
  • Re: Can we change the Colloation setting for an existing database?
    ... SQL Server MVP ... > I have a database have a different collation setting from the temp db. ...
    (microsoft.public.sqlserver.server)
  • Tempdb is Eating space Urgent!!!
    ... Backing up the database should free up space in the temp ... >Sql Server 7 ... >some space from my tempdb. ...
    (microsoft.public.sqlserver.server)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)