?
Solved

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

Posted on 2007-11-16
4
Medium Priority
?
5,185 Views
Last Modified: 2010-04-21
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
Comment
Question by:mpeacock927
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:ZuZuPetals
ID: 20298934
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
 

Author Comment

by:mpeacock927
ID: 20298973
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
 
LVL 2

Accepted Solution

by:
ZuZuPetals earned 200 total points
ID: 20299047
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
 

Author Closing Comment

by:mpeacock927
ID: 31409565
Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question