update access mdb

  • Thread starter Thread starter Stephen Martinelli
  • Start date Start date
S

Stephen Martinelli

Can anyone tell me why this statement works with sql server with VB.net but
crashes
when run against a access mdb?....What do I need to change here guys?


Update tblInvoice set ar_totalPaid =
(select sum(r_amountpaid) from tbltempPmts ti where ti.r_InvoiceNo =
tblInvoice.InvoiceNo)
 
ti is just a abreviated name assigned to tbltempPmts
the relationship in access exists
 
Access does not include the robust subquery features that you find in databases
like SQL Server and Oracle. I don't think you can reference the parent query
within the child query when doing an update in Access. Multi-table updates
in Access often trigger a "non-updatable query" warnings. You might have
to use a different solution, such as writing a function that returns the
sum when given an invoice number, or running distinct SQL statements for
each invoice you want to update.
 
Stephen,

If you have problems with language.vb code than this is your newsgroup.

I saw however not much VB language in your question or is this new VB.Net
language code?.

Cor
 
No, Access can indeed do substituion like that. If in doubt, just use the
keyword As.
Example:
tbltempPmts As ti
instead of
tbltempPmts ti
 
I second Tim's answer - this operation certainly cannot be done in Access.

I struggle with Access every day - after two years I'm still trying to get
my boss to let me switch to SQL Server!!!
_____________________________________
The Grim Reaper
 
Back
Top