Using .Execute Method of CurrentDB Object

[David C. Holley]

I'm starting to play with using the EXECUTE method of the DB object. In
the current sub that I'm working on the statement

CurrentDb.Execute strSQL, dbFailOnError

does not appear to be executing the SQL Statement contained within the
[strSQL] variable.

1. I have confirmed that the statement functions properly as I have run
it using DoCmd.RunSQL

2. I have confirmed that the .Execute statement isn't working as
expected in that the .RecordsAffected property is 0 and the field values
in the underlying table have not been altered.

David H

 

[Allen Browne]

David, post the SQL statement.

One difference is that RunSQL can use the Expression Service (ES) to resolve
references within the string such as:
[Forms].[Form1].[Text0]
Execute can't call the ES, so you need to concatenate the value from the
controls into the string.

However, if that is the issue, you should be getting error messages about
not enough parameters supplied. Are you seeing an error message raised?

 

[PC Datasheet]

CurrentDb is a function and not an object. CurrentDb returns an object
variable of type Database that represents the currently open database. Try
the following:
Dim Db As DAO.Database
Set Db = CurrentDb()
Db.Execute strSQL, dbFailOnError

 

[Dirk Goldgar]

CurrentDb is a function and not an object. CurrentDb returns an object
variable of type Database that represents the currently open
database. Try the following:
Dim Db As DAO.Database
Set Db = CurrentDb()
Db.Execute strSQL, dbFailOnError

That's all well and good, but CurrentDb.Execute should work fine. I use
it all the time.

 

[David C. Holley]

Here's the heart of the code...
Select Case Me.Parent.accountType
Case "Client"
strSQL = ""
strSQL = strSQL & "UPDATE tblTransports SET
tblTransports.lngMasterAccountID = 0, "
strSQL = strSQL & "tblTransports.lngClientID = " &
Me.Parent.accountId & ", "
strSQL = strSQL & "tblTransports.txtBillingMethod = 'Client' "
strSQL = strSQL & "WHERE (((tblTransports.lngTransportID)=
" & Me.lngTransportID & "));"
Case "Master"
strSQL = ""
strSQL = strSQL & "UPDATE tblTransports SET
tblTransports.lngMasterAccountID = " & Me.Parent.accountId & ", "
strSQL = strSQL & "tblTransports.lngClientID = 0, "
strSQL = strSQL & "tblTransports.txtBillingMethod = 'Master' "
strSQL = strSQL & "WHERE (((tblTransports.lngTransportID)=
" & Me.lngTransportID & "));"
End Select

CurrentDb.Execute strSQL, dbFailOnError
MsgBox (CurrentDb.RecordsAffected & " record(s) updated for
invoicing.")

I checked DBEnging.Errors via the Immdiate Window which returned 0. I
also checked CurrentDb.RecordsAffected which also returned 0.

 

[David C. Holley]

Take that back - DBEnging.Errors is returning 'INVALID ARGUEMENT'

Here's the heart of the code...
Select Case Me.Parent.accountType
Case "Client"
strSQL = ""
strSQL = strSQL & "UPDATE tblTransports SET
tblTransports.lngMasterAccountID = 0, "
strSQL = strSQL & "tblTransports.lngClientID = " &
Me.Parent.accountId & ", "
strSQL = strSQL & "tblTransports.txtBillingMethod = 'Client' "
strSQL = strSQL & "WHERE (((tblTransports.lngTransportID)= "
& Me.lngTransportID & "));"
Case "Master"
strSQL = ""
strSQL = strSQL & "UPDATE tblTransports SET
tblTransports.lngMasterAccountID = " & Me.Parent.accountId & ", "
strSQL = strSQL & "tblTransports.lngClientID = 0, "
strSQL = strSQL & "tblTransports.txtBillingMethod = 'Master' "
strSQL = strSQL & "WHERE (((tblTransports.lngTransportID)= "
& Me.lngTransportID & "));"
End Select

CurrentDb.Execute strSQL, dbFailOnError
MsgBox (CurrentDb.RecordsAffected & " record(s) updated for
invoicing.")

I checked DBEnging.Errors via the Immdiate Window which returned 0. I
also checked CurrentDb.RecordsAffected which also returned 0.

I'm starting to play with using the EXECUTE method of the DB object.
In the current sub that I'm working on the statement

CurrentDb.Execute strSQL, dbFailOnError

does not appear to be executing the SQL Statement contained within the
[strSQL] variable.

1. I have confirmed that the statement functions properly as I have
run it using DoCmd.RunSQL

2. I have confirmed that the .Execute statement isn't working as
expected in that the .RecordsAffected property is 0 and the field
values in the underlying table have not been altered.

David H

 

[Allen Browne]

So add:
Debug.Print strSQL
so you get to see what string Access is trying to execute when it fails.

Then copy that string into SQL View of a new query, and see what's wrong.

Chances are that a value was null (so the argument is mal-formed), or you
are not assigning the correct delimiter, or you've missed a comma or space
somewhere.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Take that back - DBEnging.Errors is returning 'INVALID ARGUEMENT'

Here's the heart of the code...
Select Case Me.Parent.accountType
Case "Client"
strSQL = ""
strSQL = strSQL & "UPDATE tblTransports SET
tblTransports.lngMasterAccountID = 0, "
strSQL = strSQL & "tblTransports.lngClientID = " &
Me.Parent.accountId & ", "
strSQL = strSQL & "tblTransports.txtBillingMethod = 'Client'
"
strSQL = strSQL & "WHERE (((tblTransports.lngTransportID)= "
& Me.lngTransportID & "));"
Case "Master"
strSQL = ""
strSQL = strSQL & "UPDATE tblTransports SET
tblTransports.lngMasterAccountID = " & Me.Parent.accountId & ", "
strSQL = strSQL & "tblTransports.lngClientID = 0, "
strSQL = strSQL & "tblTransports.txtBillingMethod = 'Master'
"
strSQL = strSQL & "WHERE (((tblTransports.lngTransportID)= "
& Me.lngTransportID & "));"
End Select

CurrentDb.Execute strSQL, dbFailOnError
MsgBox (CurrentDb.RecordsAffected & " record(s) updated for
invoicing.")

I checked DBEnging.Errors via the Immdiate Window which returned 0. I
also checked CurrentDb.RecordsAffected which also returned 0.

I'm starting to play with using the EXECUTE method of the DB object. In
the current sub that I'm working on the statement

CurrentDb.Execute strSQL, dbFailOnError

does not appear to be executing the SQL Statement contained within the
[strSQL] variable.

1. I have confirmed that the statement functions properly as I have run
it using DoCmd.RunSQL

2. I have confirmed that the .Execute statement isn't working as
expected in that the .RecordsAffected property is 0 and the field values
in the underlying table have not been altered.

David H

 

[David C. Holley]

The value in strSQL is a correctly formed SQL statement as I have used
in DoCmd.RunSQL and it works fine. I snooped around a little bit more
and realized that I AM in fact getting error - INVALID ARGUMENT.

 

[Dirk Goldgar]

CurrentDb.Execute strSQL, dbFailOnError
MsgBox (CurrentDb.RecordsAffected & " record(s) updated for
invoicing.")

David -

This is not going to give you the correct value for RecordsAffected,
because each time you call CurrentDb, you get a new database object. Do
it like this:

With CurrentDb
.Execute strSQL, dbFailOnError
MsgBox (.RecordsAffected & " record(s) updated for invoicing.")
End With

Or else create a Database object variable for the purpose:

Dim db As DAO.Database

Set db = CurrentDb
db.Execute strSQL, dbFailOnError
MsgBox (db.RecordsAffected & " record(s) updated for invoicing.")
Set db = Nothing

 

[David C. Holley]

Well using the With...end with block got it working. thx