currentdb.execute returns zero records but manually opening query

[Guest]

I execute a number of update/delete & append queries from a module using
currentdb.execute

Most of these execute correctly however a few of these return zero records
affected.

If I manually open the query in the database window then the query executes
correctly. If I use a macro to call the queries then they execute correctly.

I have also tried ADO command.execute with the same result.

Anecdotal suggestions from my users suggest that these queries do execute on
occasion.

Thanks in advance for any pointers on where to focus my research efforts.

 

[Stefan Hoffmann]

hi Trevor,

I execute a number of update/delete & append queries from a module using
currentdb.execute
Most of these execute correctly however a few of these return zero records
affected.

Use

CurrentDb.Execute "SQL", dbFailOnError Or dbSeeChanges

with an appropriate error handler.


mfG
--> stefan <--

 

[John Spencer]

I would create a database object and use that instead of using currentdb()

Dim dbAny as DAO.Database

Set dbAny = CurrentDB()
or
Set dbAny = DBEngine(0)(0)

Then use that along with error trapping to see what is going on. You could
also use the recordsAffected property to see if any changes were made after
each call

dbAny.Execute strSQL, dbfailonerror
If dbAny.RecordsAffected = 0 Then
MsgBox "No records changed by: " & vbcrlf & strSQL
End If

 

[Guest]

Hi Stefan

Thanks for your reply.
I have an append query. When I open the query in the db window and execute
it creates 15 records.

When I execute in VBA CurrentDb.Execute ...
Debug.Print CurrentDb.RecordsAffected = Zero

dbFailOnError - did not return an error. The MS documentation confirms that
this switch will roll back updates if error occurs - my query does not append
any records therefore no updates to rollback.

dbSeeChanges - did not return an error - MS documentation advises that this
"generates a run-time error if another user is changing data you are
editing". This makes sense as I am running a single user.

I appreciate your help, any other suggestions gratefully accepted.

 

[Guest]

Hi John

Thanks for your response. I have to admit that I was a doubting thomas when
I read your suggestion but I have tried it and it has given me a result on
the first query that I execute.

I don't understand why executing the query from an object that refers to the
CurrentDb would work whilst the "direct" reference does not. Is this simply
something that you have found in practise or is there an explanation?

regards

Trevor

 

[Stefan Hoffmann]

hi Trevor,

I have an append query. When I open the query in the db window and execute
it creates 15 records.
When I execute in VBA CurrentDb.Execute ...
Debug.Print CurrentDb.RecordsAffected = Zero

Maybe you can post your query SQL statement.

mfG
--> stefan <--

 

[John Spencer]

The problem is using CurrentDb. This object is ephemeral (lasts only during
the call). So by the time you get to the next line and use CurrentDb again,
the previous "copy" no longer exists. If you use something like the
following snippet, then you should see what you expect.

Dim dbAny as DAO.Database
set DbAny = CurrentDB()

dbAny.Execute ...
MsgBox dbAny.RecordsAffected & "were created"

dbAny.Close

 

[John Spencer]

The explanation is that when you simply call currentdb, Access makes a
"Copy" of the object, uses it, and then gets rid of the "copy".
When you assign the "Copy" to an object, the object continues to exist until
you exit the module or you specifically get rid of the object.

That is a very rough description of what happens.

 

[Stefan Hoffmann]

hi John,

The problem is using CurrentDb. This object is ephemeral (lasts only during
the call). So by the time you get to the next line and use CurrentDb again,
the previous "copy" no longer exists. If you use something like the
following snippet, then you should see what you expect.

Dim dbAny as DAO.Database
set DbAny = CurrentDB()

dbAny.Execute ...
MsgBox dbAny.RecordsAffected & "were created"

dbAny.Close

I have to admit that i don't use CurrentDb. I'm using instead the
CurrentDbC proxy to speed up its usage.


Put it in a standard module:

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If

Set CurrentDbC = m_CurrentDb

End Property


mfG
--> stefan <--

 

[Guest]

Plenty good enough for me

thanks again

Trevor

The explanation is that when you simply call currentdb, Access makes a
"Copy" of the object, uses it, and then gets rid of the "copy".
When you assign the "Copy" to an object, the object continues to exist until
you exit the module or you specifically get rid of the object.

That is a very rough description of what happens.

 

[Guest]

Hi Stefan

If I understand your model correctly then you instantiate the object the
first time it is used and it is only the second time that you use it that you
will get a benefit.

When trying out John's reccomendation the instantiation seemed to happen in
an instant so I put a timer on it and found that instantiating the object
dbAny took only 0.00006 seconds. I'm lost in trying to figure out how you can
save any meaningful time with a global variable declaration or proxy.
Admittedly, I used a new database but it appears to me that this is all
memory time anyway so the size of the db should not make any difference.

Since there is always pressure to speed up one's applications I am
fascinated to explore diffferent ways of doing things.

thanks

Trevor

 

[Stefan Hoffmann]

hi Trevor,

If I understand your model correctly then you instantiate the object the
first time it is used and it is only the second time that you use it that you
will get a benefit. Correct.

When trying out John's reccomendation the instantiation seemed to happen in
an instant so I put a timer on it and found that instantiating the object
dbAny took only 0.00006 seconds. I'm lost in trying to figure out how you can
save any meaningful time with a global variable declaration or proxy.

Good things first: i don't have to write that

Dim myDB As DAO.Database
Set myDB = CurrentDb

in every procedure where i need it. This saves a lot of time.

In an error free environment my proxy is initiated once. Yours depends
on the number of procedure calls, but say a thousend times. So my
solution is a thousend times faster without any sophisticated optimizations.


mfG
--> stefan <--

 

[Guest]

Hi Stefan

I agree with the saving in coding time and I understand that that using the
declaration over and over again will be a more reliable approach. This alone
makes this approach worth while. On the procedure calls, I guess these could
easily mount up, so saving a few milliseconds each time may be worth
something.

thanks for the info

Trevor