How do you write an UPDATE SQL statement between different databases 2

[Arnold]

I am trying to write an SQL statement that will update a table in one MS Access 97 database with data from a table in another MS Access 97 database. The tables have the same structure, design, and format. I want to basically do an Update query, but the table being updated is in a separate database. I have tried to use the "IN" SQL reserved word, but was unsucessful.

 

[Jonathan]

Have you linked the table you are trying to update into the database you are issuing the SQL statement from? This would be an easy way to do it.<br>
<br>
Alternatively, open the other database in code, using the OpenDatabase method of a workspace object. This is a little more complicated; personally I'd go for the linking option.<br>
<br>
Jonathan

 

[Arnold]

I can't really link the table in this instance. I am doing an Archive backup and the user can choose which database to back up to. Is it possible to link the table in code and then break the link after the code is executed? If not, then I need to be able to do an Update SQL string that will update the table in the other database. I already have the other database open with an OpenDatabase method, that isn't a problem, it is the SQL string that I need help with.

 

[bitbrain]

To link a table in code, try the run command. I've not done this but the syntax to get you started is:<br>
<br>
DoCmd.RunCommand acCmdLinkTables<br>
<br>
Doing this much will pop the Link dialog box.<br>
<br>
You can improve it a little bit by pre-loading the database name as follows:<br>
<br>
SendKeys "h:\database\test.mdb{ENTER}"<br>
DoCmd.RunCommand acCmdLinkTables<br>
<br>
The dialog box to select the table will be displayed.<br>
<br>
It's a start...

 

[Jonathan]

I'm a bit confused. If you have already opened databases with the OpenDatabase method, why can't you just use the execute method of the database object to run your SQL? eg dbOtherDatabase.Execute sql_statement <br>
<br>
Alternatively, if you can open the other database with the open database method, then you can easily link a table from it by using the CreateTableDef method of the current database object - you only need the name of the other database and the name of the table:<br>
<br>
dim tblLinked as TableDef<br>
set tblLinked = CurrentDB.CreateTableDef("local_name")<br>
tblLinked.Connect="DATABASE=" & file_name_of_other_database<br>
tblLinked.SourceTable="remote_table_name"<br>
Currentdb.TableDefs.Append tblLinked<br>
Currentdb.TableDefs.Refresh<br>
<br>
&lt; insert code to do updates &gt;<br>
<br>
CurrentDB.Tabledefs.Delete "local_name"<br>
CurrentDB.Tabledefs.Refresh<br>
<br>
I think that's about right.<br>
<br>
Jonathan

 

[Arnold]

Thanks for your help Jonathan. I did end up temporarily linking that table, then running an update query on it, and then deleting the linked table. It all worked out.

 

[bsilvert]

I see you've got it to work, but I believe that linking and deleting the link is unnecessary. The SQL statement in Access is <br>
<br>
UPDATE DISTINCTROW DataTable IN 'c:\db1.mdb' SET Field1 = whatever);<br>
<br>