Using .Execute Method of CurrentDB Object

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David C. Holley - 22 Jul 2005 02:56 GMT

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

Reply to this Message

Allen Browne - 22 Jul 2005 03:22 GMT

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?

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 12 lines]
>
> David H

Reply to this Message

PC Datasheet - 22 Jul 2005 03:24 GMT

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
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> 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
[quoted text clipped - 12 lines]
>
> David H

Reply to this Message

Dirk Goldgar - 22 Jul 2005 04:00 GMT

> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

David C. Holley - 22 Jul 2005 05:10 GMT

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
[quoted text clipped - 12 lines]
>
> David H

Reply to this Message

David C. Holley - 22 Jul 2005 05:28 GMT

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

> Here's the heart of the code...
> Select Case Me.Parent.accountType
[quoted text clipped - 40 lines]
>>
>> David H

Reply to this Message

Allen Browne - 22 Jul 2005 07:47 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Take that back - DBEnging.Errors is returning 'INVALID ARGUEMENT'
>
[quoted text clipped - 44 lines]
>>>
>>> David H

Reply to this Message

David C. Holley - 22 Jul 2005 11:53 GMT

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.

> So add:
> Debug.Print strSQL
[quoted text clipped - 5 lines]
> are not assigning the correct delimiter, or you've missed a comma or space
> somewhere.

Reply to this Message

Dirk Goldgar - 22 Jul 2005 19:40 GMT

> 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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

David C. Holley - 22 Jul 2005 20:25 GMT

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

>> CurrentDb.Execute strSQL, dbFailOnError
>> MsgBox (CurrentDb.RecordsAffected & " record(s) updated for
[quoted text clipped - 19 lines]
> MsgBox (db.RecordsAffected & " record(s) updated for invoicing.")
> Set db = Nothing

Reply to this Message