Action Query run from VBA gives no results

[Ken N]

I run an UPDATE action query by providing the SQL string
to a DoCmd Method:

strSQL = "UDATE myTable innerjoin on anothertable On
myTable.CustID= anotherTable.CustID sET myTable.amount =
myTable.amount + DSUM(anotherTable.amount, anotherTable)
WHERE anotherTable.Date < date() - 180"

This sQL string works in an acion query, but doesn't
update the records when run from VBA

Any Help?

 

[John Viescas]

Well, that SQL isn't valid, so it shouldn't work in a query, either. What's
the real SQL you're trying to execute, and what result are you trying to
achieve? I would expect that you don't need "anotherTable" in the FROM
clause and you need a third parameter in the DSum call to filter the sum on
only matching rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas

 

[Ken N.]

I've been messing with the SQL, but here it is without the
DSUM
Dim strSQL As String, strUpdate As String, strOn As
String
Dim strSet As String, strWhere As String
Dim cnn As Connection

'break up SQL into manageable portions:
strUpdate = "UPDATE CustomerInfo INNER JOIN
TransactionDetails "
strOn = "ON CustomerInfo.CustID =
TransactionDetails.lngCustID "
strSet = "SET CustomerInfo.curStartCredit = nz
([curStartCredit])+(nz([curCredit]))-(nz([curDebit])), " &
_
"CustomerInfo.dtUpDate = dtTransDate "
strWhere = "WHERE (((TransactionDetails.dtTransDate)
<= " & dtArchiveDate & "));"

'Join the SQL
strSQL = strUpdate & strOn & strSet & strWhere


'Run the query and show the results
Set cnn = CurrentProject.Connection
cnn.BeginTrans
cnn.Execute strSQL
cnn.CommitTrans

-----Original Message-----
Well, that SQL isn't valid, so it shouldn't work in a query, either. What's
the real SQL you're trying to execute, and what result are you trying to
achieve? I would expect that you don't

need "anotherTable" in the FROM

 

[Ken N]

The objective of the SQL is to reset the customer's
starting credit amount (used book credit) every six months
or so. My thought is that this will allow archiving of
inactive customers. It will also speed up transactions
when the customer brings in used books or uses book credit.
Old values in the TransactionDetails will be deleted using
a delete SQL string.

-----Original Message-----
Well, that SQL isn't valid, so it shouldn't work in a query, either. What's
the real SQL you're trying to execute, and what result are you trying to
achieve? I would expect that you don't

need "anotherTable" in the FROM

 

[John Viescas]

That SQL might work as long as curStartCredit has the correct value at the
beginning of the available records. The update could take a while if you
have hundreds of transaction records per customer. You should add #
delimiters around the date literal:

strWhere = "WHERE (((TransactionDetails.dtTransDate)
<= #" & dtArchiveDate & "#));"

To do it as a DSum, the SQL might look like:

strSQL = "UPDATE CustomerInfo SET curStartCredit = curStartCredit " & _
"+ DSUM('curCredit', 'TransactionDetails', " & _
"'lngCustID = ' & [CustomerInfo].[CustId] & ' AND dtTransDate <= #'" & _
dtArchiveDate & "#') " & _
"- DSUM('curDebit', 'TransactionDetails', " & _
"'lngCustID = ' & [CustomerInfo].[CustId] & ' AND dtTransDate <= #'" & _
dtArchiveDate & "#');"

Note that I'm filtering each DSum on the current value of
CustomerInfo.CustID and the archive date (which I assume is a variable in
your code).

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

I've been messing with the SQL, but here it is without the
DSUM
Dim strSQL As String, strUpdate As String, strOn As
String
Dim strSet As String, strWhere As String
Dim cnn As Connection

'break up SQL into manageable portions:
strUpdate = "UPDATE CustomerInfo INNER JOIN
TransactionDetails "
strOn = "ON CustomerInfo.CustID =
TransactionDetails.lngCustID "
strSet = "SET CustomerInfo.curStartCredit = nz
([curStartCredit])+(nz([curCredit]))-(nz([curDebit])), " &
_
"CustomerInfo.dtUpDate = dtTransDate "
strWhere = "WHERE (((TransactionDetails.dtTransDate)
<= " & dtArchiveDate & "));"

'Join the SQL
strSQL = strUpdate & strOn & strSet & strWhere


'Run the query and show the results
Set cnn = CurrentProject.Connection
cnn.BeginTrans
cnn.Execute strSQL
cnn.CommitTrans

-----Original Message-----
Well, that SQL isn't valid, so it shouldn't work in a query, either. What's
the real SQL you're trying to execute, and what result are you trying to
achieve? I would expect that you don't

need "anotherTable" in the FROM

clause and you need a third parameter in the DSum call to filter the sum on
only matching rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas



.