Using .Execute Method of CurrentDB Object

  • Thread starter Thread starter David C. Holley
  • Start date Start date
D

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
 
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?
 
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
 
PC Datasheet said:
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
Click to expand...

That's all well and good, but CurrentDb.Execute should work fine. I use
it all the time.
 
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.
 
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
Click to expand...
Click to expand...
 
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.

David C. Holley said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
David C. Holley said:
CurrentDb.Execute strSQL, dbFailOnError
MsgBox (CurrentDb.RecordsAffected & " record(s) updated for
invoicing.")
Click to expand...

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
 
Back
Top