App does not shut down on exiting the db

[QB]

I have a rather large (in the sense of vba) db that on 2pc does not shut down
the app when the user exits the db. This also mean the db remains locked
when it shouldn't....

I have been troubleshooting and can seem to locate the issue. I thought it
was with regards to not setting object variables to nothing, but have since
cleaned this up.

What other causes could be at the root cause of this problem?
Why would this only occur on 2 PCs out of 20-30 PCs?

Thank you for the help,

QB

 

[Jack Leach]

Is there some process that these two users may be running that opens another
(possibly hidden) instance of access? Is there any chance that another
instance may already be loaded before this app starts? Does the db appear to
close but is still in the task manager, or does it simply not shut down at
all? If it doesn't not appear to close try pressing ctl+break to break any
code execution, you may be able to enter the code and step through in debug
mode (depending on your settings) to see of there may be a combination of
circumstance that would lead to an infintate loop...

Take a look here to programmatically check for an existing instance of
access before loading your app... either than or they may be some code
running that's preventing the shutdown of the app.

http://www.mvps.org/access/api/api0041.htm

http://www.mvps.org/access/api/api0007.htm

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[QB]

No this is the only use for access, so no they do not already have it open.
No other instances running.

Yes, Acess is no longer visble to the user when they exit the db, but still
in the tack manager.

QB

 

[Jack Leach]

I'm not sure what else it might be. Do you have any code that might be
creating another instance (opening a remote db perhaps)?

I'm out of ideas other than that. You might want to check out the API from
mvps just to make sure, if there is a hidden instance of it open it won't be
evident... run through the startup and shutdown code on those computers to
make sure everything is going as planned. Check your code with an emphasis
on any external functions if there is any...

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[John Spencer]

As I vaguely recall there was a bug in earlier versions involving the use of
booleans in VBA modules that could cause a problem. If my memory serves me
right it had something to do with code like the following

'===== This could cause the database to remain open ====
Dim tfBool as Boolean

If TfBool Then
do x
else
do y
End IF

as opposed to the following which would not have the same effect.

Dim tfBool as Boolean
If tfBool is True Then
do x
else
do y
End If

Again, I must stress that I only vaguely recall this and it might all be a
figment of my imagination or just a nightmare that lives on in my head.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

As I vaguely recall there was a bug in earlier versions involving
the use of booleans in VBA modules that could cause a problem. If
my memory serves me right it had something to do with code like
the following

'===== This could cause the database to remain open ====
Dim tfBool as Boolean

If TfBool Then
do x
else
do y
End IF

I think that Boolean variables were not the cause of the problem,
but getting Boolean values from controls, such a checkboxes.

Thus:

If Me!chkCheckBox Then

....should be changed to:

If (Me!chkCheckBox) Then

....or:

If Me!chkCheckBox = True Then

The first version causes implicit references that don't get
resolved, while the alternatives force evaluation of the value in
the checkbox control so there is no implicit reference to the
control.

BTW, in regard to implicit references, I believe I recently resolved
an ongoing problem that I'd had with concatenating memo fields. What
I found was this:

Public Function ConcMemos(varMemo1 As Variant, _
varMemo2 As Variant) As Variant
ConcMemos = varMemo1 & varMemo2
End Function

....when the passed in values were fields from a recordset would
result sometimes (but not always) in the return value being
truncated around 255 characters, even when the sum of the lengths of
the passed-in values was greater than 255 characters.

I believe I've solved this with this:

Public Function ConcMemos(ByVal varMemo1 As Variant, _
ByVal varMemo2 As Variant) As Variant
ConcMemos = varMemo1 & varMemo2
End Function

When called like this:

Dim rs As DAO.Recordset
Dim strOutput As String

Set rs = CurrentDB.OpenRecordset("SELECT * FROM tblInventory")
If rs.Recordcount > 0 Then
rs.MoveFirst
Do Until rs.EOF
strOutput = ConcMemos(rs!Memo1, rs!Memo2)
Debug.Print strOutput
Loop
End If

....without the ByVal (i.e., implicit ByRef), the truncation occurs,
and when using ByVal, it does not.

Apparently, with the implicit ByRef there were too many chained
references and the data buffers were just not getting properly
allocated.

This is a problem I've been struggling with for almost 10 years!

It's made me decide to use ByRef only when I know I need it.

 

[vanderghast]

ByVal or ByRef is only applicable to the argument and that value is always
push on the stack, there is no buffer directly involved in that process, so
if there is a problem of memory, technically, that would be a stack
overflow, rather than a buffer overflow.

As far as 'chaining' to be involved, object arguments do NOT create copies
of the object, so no extra buffer is directly involved there either.


Here a simplified review of the possibilities:

1-ByVal i AS Long

is like creation of a long on the stack, initialized with a given value
supplied by the calling procedure. Changing the argument in this proc does
not change the value of the variable used by the procedure having called
this one.

2-ByRef i AS Long

is like the creation of a 32 bit pointers pointing, on the stack,
referring to an integer already existing in the calling procedure. Changing
the argument in this proc. changes the value of the variable used by the
procedure having called this one (unless it was a computed expression).

3-ByVal o AS object

is like the creation of a 32 bit pointer on the stack, initialized with
the value of an existing 32 bit pointer in the calling procedure. Changing
the argument in this proc does not change the object that the variable used
by the procedure having called this one points to, since only a copy of the
reference, NOT of the object, was made. And that copy is only 32 bit (on a
32 bit OS). In fact, the mechanic is the same as ByVal i AS (integer
being a pointer), our case 1.

4-ByRef o AS object

is like the creation of a 32 bit pointer on the stack, a 32 bit pointer
of a 32 bit pointer of an object. Changing the argument in this proc simply
makes the variable used by the procedure having called this one to points to
that different object (than the one used at the start of the proc). The
mechanic is the same as ByRef i AS (integer being a pointer), our case 2.



So I fail to see how "data buffers were just not getting properly
allocated" can be involved trough ByVal/ByRef use for arguments in procedure
declaration, since no buffer are involved by the only use of these keywords.




Vanderghast, Access MVP

 

[David W. Fenton]

So I fail to see how "data buffers were just not getting properly
allocated" can be involved trough ByVal/ByRef use for arguments in
procedure declaration, since no buffer are involved by the only
use of these keywords.

Memo fields in a recordset being passed to a function ByVal were
getting truncated upon concatenation. Passing ByVal eliminated the
truncation. The difference, so far as I understand it, is that with
ByVal a copy of the data in the memo fields was being passed instead
of a pointer to the memory location where the recordset data was
located.

I have been struggling with this problem for about a decade in a
particular application that needs to concatenate memo fields (this
website is driven by data uploaded from the Access application
involved: http://wurlitzerbruck.com). Changing my parameter
declarations to ByVal (instead of the implicit ByRef) has resolved
the issue in all the cases I've tested.

What other explanation would you offer?

 

[Jack Leach]

http://www.wurlitzerbruck.com/

cool stuff, I could spend some money there <g>

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[vanderghast]

A source of the problem can be about how & is implemented. I mean, for + as
example, 4+7 does not imply the same set of operations that in 4.60 +
(7.8/3) . A bug in + with integers won't necessary also be a bug in +
between floating point numbers (or the reverse). So the problem may also be
about a bug in & in (Variant holding a pointer of memo) & (Variant holding
a pointer of memo) while it is absent in (Variant holding a memo) &
(Variant holding a memo). Note that memo is not a VBA object neither a VBA
datatype, but this explanation assumes it is somehow seen as one, by the
compiler.


That *could* also imply some automatic data conversion from one object type
to another type, also given the arguments are declared as Variant. Here, it
is logical to see that the compiler expect the arguments of & to be strings
and thus convert the memo (or pointer of memo) into string (or pointer to
string), and the problem is in one of these conversions, but not in the
other.


I don't claim it is an exhaustive list of all possible sources of the
problem.



Have you ever contacted Microsoft about this bug? You have to a free support
(or two) per year, isn't ? (or having found a solution, you don't bother
much bothering others, things that I also do myself )


Vanderghast, Access MVP

 

[David W. Fenton]

A source of the problem can be about how & is implemented. I
mean, for + as example, 4+7 does not imply the same set of
operations that in 4.60 + (7.8/3) . A bug in + with integers won't
necessary also be a bug in + between floating point numbers (or
the reverse). So the problem may also be about a bug in & in
(Variant holding a pointer of memo) & (Variant holding a pointer
of memo) while it is absent in (Variant holding a memo) &
(Variant holding a memo).

It could be that I've used string variables in some cases. In the
recent re-architecting of the code, I may have upgraded some old
assumptions and changed that.

Note that memo is not a VBA object neither a VBA
datatype, but this explanation assumes it is somehow seen as one,
by the compiler.

The object involved is a member of the fields collection of a DAO
recordset, and I'd assume the problem is with the handling of those
references.

That *could* also imply some automatic data conversion from one
object type to another type, also given the arguments are declared
as Variant. Here, it is logical to see that the compiler expect
the arguments of & to be strings and thus convert the memo (or
pointer of memo) into string (or pointer to string), and the
problem is in one of these conversions, but not in the other.

I don't claim it is an exhaustive list of all possible sources of
the problem.

I have spent a lot of time trying various things and have only
resolved it recently. There have also been cases where something
like this caused the problem, too:

Public Sub OutputHTML(rs As DAO.Recordset)
Dim strOutput As String

With rs
Do Until .EOF
strOutput = "<div class=" & Chr(34) & "Results" _
& Chr(34) & ">"
strOutput = strOutput & !Memo1 & !Memo1
strOutput = strOutput & "</div>"
Loop
End With
[do something with strOutput]
End Sub

Placing () around !Memo1 and !Memo2 seems to have fixed it in this
case, but not always. At other times, assigning the value of each
memo field to its own string variable seems to have fixed it, but I
can't say for sure that it did in all cases.

The main thing I've been trying to do is eliminate any hidden
reference issues, i.e., getting the source recordset fields' data
into strings as early as possible, and before the concatenation.
That combined with the ByVal setting on the functions that do
various forms of concatenation seems to have fixed the problem, but
I have no way of knowing if I've found all the edge cases, or just
most of them, because it was always one of those things that seemed
to have no pattern.

Have you ever contacted Microsoft about this bug? You have to a
free support (or two) per year, isn't ? (or having found a
solution, you don't bother much bothering others, things that I
also do myself )

The problem is that I can't come up with a reliable repro scenario.

 

[vanderghast]

Note that I am technically wrong when I said pointer, it is, technically, a
reference. Subtle difference, but same end effect. I will continue to use
pointer.

I am also wrong when I said Variant holding a pointer to a memo, it is
rather a pointer to a Variant holding a memo. In other words, ByRef is
always a pointer (reference) to something.

I see another possible pattern, one that involved the Variant matching a
Field object. It is, indeed, the whole field object, not JUST its intended
default value, its value, which add another possible source of different
execution path taken at runtime. And that could also be closer to the latest
example you mentioned.



Vanderghast, Access MVP/