trying to exec a SQL update...

[rss0213]

I have this code...

sqlStr = "UPDATE tblPaymentHeader SET [Voucher] = '" & varVoucherNum & "'"
appAccess.DoCmd.RunSQL sqlStr

...trying to update an Access 2007 table. It's failing, but the only error it tells me is "The RunSQL action was canceled." I know my connection and all is good because I'm executing a command (appAccess.DoCmd.RunSavedImportExport) just prior to executing this step, and it's working fine. Anybody see what I'm doing wrong?

Thanks!
Scott

 

[PHV]

If Voucher is defined as numeric in tblPaymentHeader , then get rid of the single quotes.

BTW, are you sure you want to execute an UPDATE without any WHERE clause ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rss0213]

Voucher is text, so I'm fine there, but you're right about the WHERE clause. See... I'm looping through a table to create a specifically formatted export file (can't use transfertext, etc.). I'm actually looping through the same table I just loaded with the RunSavedImportExport command. But I needed to load it first so I could use a SUM query to total an amount field for the first (batch header record) of my export file. One of the fields (Voucher) in the table I'm loading is not part of the import file. This value is derived from part of another field (PaymentId). So after I load the table, I need to derive the voucher number (which I'm doing inside my loop) and assign the voucher with the UPDATE query. Maybe I just need to Save the PaymentId in a variable and set the WHERE clause to that variable. I'll try that and let you know...

Thanks!
Scott

 

[rss0213]

Well, that didn't work either. I'll post more of the code so you can see what I'm trying to accomplish. Again, I need to import this file (actually a collection of files, but the tables are already loaded for now, so I can get the other file imports working after this is fixed). Then I need to do some things with the data in the table to get an export file. Here's the code...


connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data " & _
"Source=C:\Documents and Settings\apprss\Desktop\Purchasing Work\PurchasingP2P.accdb;" & _
"Persist Security Info=False;"

' SQL statement to run
sqlVoucherNumber = "select * from tblVoucherNumber"
sqlPaymentHeader = "select * from tblPaymentHeader"
sqlBatchSum = "Select SUM(GrossAmount) As BatchAmount From tblPaymentHeader"

Dim cn, cmd1, cmd2, cmd3, cmd4
' Create ADO Connection/Command objects
set cn = createobject("ADODB.Connection")
set cmd1 = createobject("ADODB.Command")
set cmd2 = createobject("ADODB.Command")
set cmd3 = createobject("ADODB.Command")
set cmd4 = createobject("ADODB.Command")

' Open connection
cn.open connectionString

Dim appAccess
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\Documents and Settings\apprss\Desktop\Purchasing Work\PurchasingP2P.accdb"


If (objFSO.FileExists(varPmtDetail)) Then
appAccess.DoCmd.RunSavedImportExport "Import-tblPaymentDetail"
End If

' Associate connection object with command object
cmd1.ActiveConnection = cn
cmd2.ActiveConnection = cn
cmd3.ActiveConnection = cn
cmd4.ActiveConnection = cn

' Set the SQL statement of the command object
cmd1.CommandText = sqlVoucherNumber
cmd2.CommandText = sqlPaymentHeader
cmd3.CommandText = sqlBatchSum
cmd4.CommandText = sqlUpdProcessed

Dim rs1, rs2, rs3
' Execute query
set rs1 = cmd1.execute
set rs2 = cmd2.execute
set rs3 = cmd3.execute

Dim varBatchAmount
varBatchAmount = (round(rs3("BatchAmount"),2)) * 100
varBatchAmount = RightPad(varBatchAmount, 17, "0")

Dim var1Brec
'Format and write 1B record (batch header)
varBatchID = rs1("VoucherPrefix") & vMo & vDay
var1Brec = "1B" & varBatchID & "01077" & varBatchAmount
objOutputFile.WriteLine(var1Brec)

Dim varVoucherNumJustified, varVoucherPrefix, varVoucherNum, varInvoiceDate, _
varAmount, var1Grec, varInvoiceType, varYear, varPmtIDsplit, varPmtID

while rs2.EOF <> true and rs2.BOF <> True
'format voucher number
varVoucherPrefix = rs1("VoucherPrefix") 'get Voucher prefix from Voucher number table
varPmtID = rs2("PaymentID") 'Save PaymentID to variable for UPDATE query
varPmtIDsplit = split(rs2("PaymentID"), "-") 'Split Paymet ID for deriving voucher number
varVoucherNumJustified = RightPad( varPmtIDsplit(1), 5, "0" ) 'Right-justify voucher suffix
varVoucherNum = varVoucherPrefix & varVoucherNumJustified 'Combine voucher prefix and suffix to create voucher number
msgbox("Voucher: " & varVoucherNum)
sqlStr = "UPDATE tblPaymentHeader SET [Voucher] = '" & varVoucherNum & "' WHERE [PaymentID] = '" & varPmtID & "'"
appAccess.DoCmd.RunSQL sqlStr

varVendor = RightPad( rs2("RemittanceLocation"), 9, "0" )

varDate = CDate(rs2("InvoiceDate"))
'msgbox varDate
varYear = Mid(DatePart("yyyy",varDate),3,2)

varInvoiceDate = RightPad( DatePart("m",varDate), 2, "0" ) & RightPad( DatePart("d",varDate), 2, "0" ) & varYear

if ( rs2("GrossAmount") < 0 ) then
varAmount = RightPad( ((round(rs2("GrossAmount"),2)) * -100), 15, "0" )
varInvoiceType = "4"
else
varAmount = RightPad( ((round(rs2("GrossAmount"),2)) * 100), 15, "0" )
varInvoiceType = "1"
end if

'format and output 1G record
var1Grec = "1G" & varBatchID & rs2("Voucher") & varInvoiceType & varVendor & LeftPad( rs2("InvoiceNumber"), 25, " " ) & varInvoiceDate & varAmount
objOutputFile.WriteLine(var1Grec)

'UPDATE PROCESSED FLAG IN PAYMENT HEADER TABLE
sqlStr = "(UPDATE tblPaymentHeader SET Processed=True Where Voucher = '" & varVoucherNum & "')"
appAccess.DoCmd.RunSQL sqlStr

rs2.movenext
wend


Thanks!
Scott

 

[rss0213]

Do you think it's not liking this because rs2 is the same data that I'm trying to update (albeit a QUERY and not the actual TABLE)?

Thanks!
Scott