Solved

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

Posted on 2007-11-16
4
5,175 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
  • 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 50 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now