|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
update access mdbCan 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) What is "ti"?
You may have a tblInvoice and ti relationship set up in SQL but not in Access. Show quoteHide quote "Stephen Martinelli" <steph***@johnstontrading.com> wrote in message news:um2EQ9NCHHA.4832@TK2MSFTNGP06.phx.gbl... > 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 Show quoteHide quote "bz" <nospam@yahoo.com> wrote in message news:eNY0mPOCHHA.204@TK2MSFTNGP04.phx.gbl... > What is "ti"? > You may have a tblInvoice and ti relationship set up in SQL but not in > Access. > > > "Stephen Martinelli" <steph***@johnstontrading.com> wrote in message > news:um2EQ9NCHHA.4832@TK2MSFTNGP06.phx.gbl... >> 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) >> > > 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 Show quoteHide quote "Stephen Martinelli" <steph***@johnstontrading.com> schreef in bericht news:exSpAaOCHHA.3604@TK2MSFTNGP03.phx.gbl... > ti is just a abreviated name assigned to tbltempPmts > the relationship in access exists > "bz" <nospam@yahoo.com> wrote in message > news:eNY0mPOCHHA.204@TK2MSFTNGP04.phx.gbl... >> What is "ti"? >> You may have a tblInvoice and ti relationship set up in SQL but not in >> Access. >> >> >> "Stephen Martinelli" <steph***@johnstontrading.com> wrote in message >> news:um2EQ9NCHHA.4832@TK2MSFTNGP06.phx.gbl... >>> 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) >>> >> >> > > Ohhh! I see... I don't think Access can do substitute like that.
Show quoteHide quote "Stephen Martinelli" <steph***@johnstontrading.com> wrote in message news:exSpAaOCHHA.3604@TK2MSFTNGP03.phx.gbl... > ti is just a abreviated name assigned to tbltempPmts > the relationship in access exists > "bz" <nospam@yahoo.com> wrote in message > news:eNY0mPOCHHA.204@TK2MSFTNGP04.phx.gbl... >> What is "ti"? >> You may have a tblInvoice and ti relationship set up in SQL but not in >> Access. >> >> >> "Stephen Martinelli" <steph***@johnstontrading.com> wrote in message >> news:um2EQ9NCHHA.4832@TK2MSFTNGP06.phx.gbl... >>> 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) >>> >> >> > > No, Access can indeed do substituion like that. If in doubt, just use the
keyword As. Example: tbltempPmts As ti instead of tbltempPmts ti Show quoteHide quote "bz" <nospam@yahoo.com> wrote in message news:usXowzOCHHA.3524@TK2MSFTNGP06.phx.gbl... > Ohhh! I see... I don't think Access can do substitute like that. > > > > "Stephen Martinelli" <steph***@johnstontrading.com> wrote in message > news:exSpAaOCHHA.3604@TK2MSFTNGP03.phx.gbl... >> ti is just a abreviated name assigned to tbltempPmts >> the relationship in access exists >> "bz" <nospam@yahoo.com> wrote in message >> news:eNY0mPOCHHA.204@TK2MSFTNGP04.phx.gbl... >>> What is "ti"? >>> You may have a tblInvoice and ti relationship set up in SQL but not in >>> Access. >>> >>> >>> "Stephen Martinelli" <steph***@johnstontrading.com> wrote in message >>> news:um2EQ9NCHHA.4832@TK2MSFTNGP06.phx.gbl... >>>> 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) >>>> >>> >>> >> >> > > 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. ----- Tim Patrick Start-to-Finish Visual Basic 2005 Show quoteHide quote > 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) 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 Show quoteHide quote "Tim Patrick" <inva***@invalid.com.invalid> wrote in message news:e3b469761f198c8d6be06f48c58@newsgroups.comcast.net... > 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. > > ----- > Tim Patrick > Start-to-Finish Visual Basic 2005 > >> 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) > > 
Other interesting topics
There is no conditional And in VB?
Upgrade? Reference parent form elemets by child VB 2005 and Windows vista Strange but true ... im at a loss help !!! question about inheritance and Imports statement XML over TCP directing output to selected monitor in a multi-monitor desktop File Upload in Windows Application.. Dataset to XML |
|||||||||||||||||||||||
