By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,417 Members | 904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,417 IT Pros & Developers. It's quick & easy.

update access mdb

P: n/a
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)
Nov 15 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
bz
What is "ti"?
You may have a tblInvoice and ti relationship set up in SQL but not in
Access.
"Stephen Martinelli" <st******@johnstontrading.comwrote in message
news:um**************@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)

Nov 15 '06 #2

P: n/a
ti is just a abreviated name assigned to tbltempPmts
the relationship in access exists
"bz" <no****@yahoo.comwrote in message
news:eN*************@TK2MSFTNGP04.phx.gbl...
What is "ti"?
You may have a tblInvoice and ti relationship set up in SQL but not in
Access.
"Stephen Martinelli" <st******@johnstontrading.comwrote in message
news:um**************@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)


Nov 15 '06 #3

P: n/a
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)

Nov 15 '06 #4

P: n/a
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

"Stephen Martinelli" <st******@johnstontrading.comschreef in bericht
news:ex**************@TK2MSFTNGP03.phx.gbl...
ti is just a abreviated name assigned to tbltempPmts
the relationship in access exists
"bz" <no****@yahoo.comwrote in message
news:eN*************@TK2MSFTNGP04.phx.gbl...
>What is "ti"?
You may have a tblInvoice and ti relationship set up in SQL but not in
Access.
"Stephen Martinelli" <st******@johnstontrading.comwrote in message
news:um**************@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)



Nov 15 '06 #5

P: n/a
bz
Ohhh! I see... I don't think Access can do substitute like that.

"Stephen Martinelli" <st******@johnstontrading.comwrote in message
news:ex**************@TK2MSFTNGP03.phx.gbl...
ti is just a abreviated name assigned to tbltempPmts
the relationship in access exists
"bz" <no****@yahoo.comwrote in message
news:eN*************@TK2MSFTNGP04.phx.gbl...
>What is "ti"?
You may have a tblInvoice and ti relationship set up in SQL but not in
Access.
"Stephen Martinelli" <st******@johnstontrading.comwrote in message
news:um**************@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)



Nov 15 '06 #6

P: n/a
No, Access can indeed do substituion like that. If in doubt, just use the
keyword As.
Example:
tbltempPmts As ti
instead of
tbltempPmts ti

"bz" <no****@yahoo.comwrote in message
news:us**************@TK2MSFTNGP06.phx.gbl...
Ohhh! I see... I don't think Access can do substitute like that.

"Stephen Martinelli" <st******@johnstontrading.comwrote in message
news:ex**************@TK2MSFTNGP03.phx.gbl...
>ti is just a abreviated name assigned to tbltempPmts
the relationship in access exists
"bz" <no****@yahoo.comwrote in message
news:eN*************@TK2MSFTNGP04.phx.gbl...
>>What is "ti"?
You may have a tblInvoice and ti relationship set up in SQL but not in
Access.
"Stephen Martinelli" <st******@johnstontrading.comwrote in message
news:um**************@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)



Nov 15 '06 #7

P: n/a
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

"Tim Patrick" <in*****@invalid.com.invalidwrote in message
news:e3*************************@newsgroups.comcas t.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)


Nov 16 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.