Access Update Query - SQL View Same Table Structure, Two different databases
Posted on 2007-11-16
I'm new to SQL view of Access Query, but I need to do an update query that goes outside to another database.
The tables are the same in both databases (table name and field names). The purpose of db1 is to operate as an off-line version of db2. When the user is done, the information for the specific location is deleted from db2 and then the new information from db1 is appended to db2. This one table in db2 can only have certain fields brought back, as updates to other fields may have occurred since the extraction of the off-line version. I'm trying to use an update query instead of the combination of del/append to preserve those other fields.
On the query grid, I brought field1, field2, field3, field4, and field 5, from tbl1 in db1, then switched to SQL View. I then added IN 'S:\....db2
UPDATE [TableName] IN 'S:\db2.mdb'
SET [TableName].[Field1] = [TableName].[Field1],
[TableName].[Field2] = [TableName].[Field2],
[TableName].[Field3] = [TableName].[Field3],
[TableName].[Field4] = [TableName].[Field4],
[TableName].[Field5] = [TableName].[Field5I]
WHERE ((([TableName].[Loc No])=[Forms]![FormEntry]![EnterLocNo]));
It is just picking up the information and dumping it back down in the same database. How can I designate currentdb.tablename.field1? The name of db1 will vary, but the path will not. The two databases will always be side by side in one directory when the command button to run all these queries is activated.