Update Query Fails due to Source table not being Updatable

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
 
G

Guest

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

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
 
W

WAstarita

Unfortunately, since this is a SQL Passthrough query, DLookup would
not be available since SQL engine would be doing the processing, not
access.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top