Solved

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

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

738 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