Updating table in access from another access file.

[xboxown]

Hey,



I have a question about a proper SQL query. I have a table called WorkOrderItem in an access file called AccessA and I want to update the data from the same table in a different access file called AccessB. How do I go about doing that?

Thanks in advance.

[John_G]

One way is to create a link to the table in in the AccessB database. (External Data - New Data Source - From Database - Access). Once you have created the link, that table can be used the same as any local table can, including in SQL update sommands or as a form source.

[xboxown]

I keep getting syntax Update Statement. Why is that?

UPDATE
tblWorkOrderItem
SET
StockID=Table_B.StockID,
CPNo=Table_B.CPNo,
ProductID=Table_B.ProductID,
Source=Table_B.Source,
BFFGrade=Table_B.BFFGrade,
Unit=Table_B.Unit,
Price=Table_B.Price,
IsTax1=Table_B.IsGST,
IsTax2=Table_B.IsPST,
IsTax3=Table_B.IsHST,
PriceAdjustment=Table_B.PriceAdjustment,
Quantity=Table_B.Quantity,
Description=Table_B.Description,
Height=Table_B.Height,
Width=Table_B.Width,
Length=Table_B.Length,
[Note]=Table_B.[Note],
BoardFeet=Table_B.BoardFeet,
IsMedical
SELECT tblWorkOrderItemExternal.StockID, tblWorkOrderItemExternal.CPNo, tblWorkOrderItemExternal.ProductID, tblWorkOrderItemExternal.Source, tblWorkOrderItemExternal.BFFGrade, tblWorkOrderItemExternal.Unit, tblWorkOrderItemExternal.Price, tblWorkOrderItemExternal.IsGST, tblWorkOrderItemExternal.IsPST, tblWorkOrderItemExternal.IsHST, tblWorkOrderItemExternal.PriceAdjustment, tblWorkOrderItemExternal.Quantity, tblWorkOrderItemExternal.Height, tblWorkOrderItemExternal.Width, tblWorkOrderItemExternal.Length, tblWorkOrderItemExternal.[Note], tblWorkOrderItemExternal.BoardFeet, tblWorkOrderItemExternal.IsMedical
FROM tblWorkOrderItemExternal
Where tblWorkOrderItemExternal.WorkOrderID=10348;

[orange]

You are referring to your table as Table_B in one place and as tblWorkOrderItemExternal in the SELECT????

Formatted sql

UPDATE tblWorkOrderItem
SET StockID = Table_B.StockID
,CPNo = Table_B.CPNo
,ProductID = Table_B.ProductID
,Source = Table_B.Source
,BFFGrade = Table_B.BFFGrade
,Unit = Table_B.Unit
,Price = Table_B.Price
,IsTax1 = Table_B.IsGST
,IsTax2 = Table_B.IsPST
,IsTax3 = Table_B.IsHST
,PriceAdjustment = Table_B.PriceAdjustment
,Quantity = Table_B.Quantity
,Description = Table_B.Description
,Height = Table_B.Height
,Width = Table_B.Width
,Length = Table_B.Length
,[Note] = Table_B.[Note]
,BoardFeet = Table_B.BoardFeet
,IsMedical

SELECT tblWorkOrderItemExternal.StockID
,tblWorkOrderItemExternal.CPNo
,tblWorkOrderItemExternal.ProductID
,tblWorkOrderItemExternal.Source
,tblWorkOrderItemExternal.BFFGrade
,tblWorkOrderItemExternal.Unit
,tblWorkOrderItemExternal.Price
,tblWorkOrderItemExternal.IsGST
,tblWorkOrderItemExternal.IsPST
,tblWorkOrderItemExternal.IsHST
,tblWorkOrderItemExternal.PriceAdjustment
,tblWorkOrderItemExternal.Quantity
,tblWorkOrderItemExternal.Height
,tblWorkOrderItemExternal.Width
,tblWorkOrderItemExternal.Length
,tblWorkOrderItemExternal.[Note]
,tblWorkOrderItemExternal.BoardFeet
,tblWorkOrderItemExternal.IsMedical
FROM tblWorkOrderItemExternal
WHERE tblWorkOrderItemExternal.WorkOrderID = 10348

[xboxown]

That is ok. i resolved it without using going complex mode.

Thanks though.

[John_G]

That is incorrect syntax for an SQL Update. SQL Update cannot use the Select... part - that is for an Insert Into statement. What is it you want to do - are you updating an existing record, or adding a new one?

[orange]

Perhaps you could show and/or describe the solution. It may just help someone with similar issue.

[xboxown]

Perhaps you could show and/or describe the solution. It may just help someone with similar issue.

My solution is not access related with SQL. I just simply deleted all the dummy data and that is it. Nothing query complex solution.