Update Query Fails due to Source table not being Updatable

  • Thread starter Thread starter WAstarita
  • Start date Start date
W

WAstarita

I am creating a query to update my SQL server tables from a custom
ODBC driver that accesses my financial software. Basically I do a SQL
Pass Through query to the ODBC Driver which returns the updated
results. I have saved this query and it runs successfully every
time. This query is not updateable. I then create an Update query
which uses the aformentioned query and joins it to the linked SQL
table by there unique ID. Its a basic update query from that point.
Now, the target table is updatable, I've proven that to myself 10
different ways, the source again, is not. I still get "Operation must
use an updateable query." error.
Here is the source

dbo_Account = Linked SQL Server table, updatable
[Account Dirty] = Source table, is a saved SQL Passthrough query, not
updateable

Update dbo_Account LEFT JOIN [Account Dirty] ON dbo_Account.ListID =
[Account Dirty]ListID SET dbo_Account.Balance = [Account
Dirty].Balance, dbo_Account.TotalBalance = [Account
Dirty].TotalBalance

Any way around this?

Thanks in advance
 
Hi

Try...
Update dbo_Account LEFT JOIN [Account Dirty] ON dbo_Account.ListID =
[Account Dirty]ListID SET dbo_Account.Balance = [Account
Dirty].Balance, dbo_Account.TotalBalance = [Account
Dirty].TotalBalance
Click to expand...

Update dbo_Account
Set Balance = dlookup("Balance", "[Account Dirty]", "ListID = " & ListID),
TotalBalance = dlookup("TotalBalance", "[Account Dirty]", "ListID = " &
ListID)


I've assumed ListID is numeric. If it is text then the last parameter of the
dlookup function would be...

"ListID = """ & ListID & """"

hth

Andy Hull


WAstarita said:
I am creating a query to update my SQL server tables from a custom
ODBC driver that accesses my financial software. Basically I do a SQL
Pass Through query to the ODBC Driver which returns the updated
results. I have saved this query and it runs successfully every
time. This query is not updateable. I then create an Update query
which uses the aformentioned query and joins it to the linked SQL
table by there unique ID. Its a basic update query from that point.
Now, the target table is updatable, I've proven that to myself 10
different ways, the source again, is not. I still get "Operation must
use an updateable query." error.
Here is the source

dbo_Account = Linked SQL Server table, updatable
[Account Dirty] = Source table, is a saved SQL Passthrough query, not
updateable

Update dbo_Account LEFT JOIN [Account Dirty] ON dbo_Account.ListID =
[Account Dirty]ListID SET dbo_Account.Balance = [Account
Dirty].Balance, dbo_Account.TotalBalance = [Account
Dirty].TotalBalance

Any way around this?

Thanks in advance
Click to expand...
 
Unfortunately, since this is a SQL Passthrough query, DLookup would
not be available since SQL engine would be doing the processing, not
access.
 
Back
Top