Solved

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

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

Backup Your Microsoft Windows Server®

Backup 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

Suggested Solutions

Title # Comments Views Activity
view mode vs edit mode 17 46
How can I create a table in a linked database, in VB? 7 17
Sub Reports 8 21
Operation must use an updatable query 4 23
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

947 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

22 Experts available now in Live!

Get 1:1 Help Now