Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5190
  • Last Modified:

Access Update Query - SQL View Same Table Structure, Two different databases

Good morning,

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.
0
mpeacock927
Asked:
mpeacock927
  • 2
  • 2
1 Solution
 
ZuZuPetalsCommented:
Have you linked the table from db2 into db1?  If you do, it will behave just as though it where a local table for the purpose of updates etc.
0
 
mpeacock927Author Commented:
Unfortunately I can't use a linked table.  The table in db1 only has information for the one extracted location.  DB1 has to be completely "standalone", for the user's updates.  Only after content has been reviewed by administrator and received QC check by a senior engineer is the content moved back into the main database.    
0
 
ZuZuPetalsCommented:
Hmm... which ever database has the "master" table in it, go there and then link to the table in the other db.  Then go back to the query builder and simply choose the linked table (instead of saying IN (other database).  It will still BE in the other database, but because you've linked it here, it will behave as though it were a local table for the purpose of your update query.

UPDATE [LinkedTableName]  
SET [LinkedTableName].[Field1] =  [TableName].[Field1],
[LinkedTableName].[Field2] =  [TableName].[Field2],
[LinkedTableName].[Field3] =  [TableName].[Field3],
[LinkedTableName].[Field4] =  [TableName].[Field4],
[LinkedTableName].[Field5] =  [TableName].[Field5I]
WHERE ((([TableName].[Loc No])=[Forms]![FormEntry]![EnterLocNo]));

(or something to that effect)... Note that after you've linked the table, you can rename the link anything you like without affecting the actual underlying tables real name.
0
 
mpeacock927Author Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now