MS Access 2007 doesn't close properly

[Robbo]

I have been developing on an application and discovered that MS Access isn't
closing properly. If I do a little amount of work with the app all is well,
but if I navigate past about 2 or 3 forms (not consistent) then close the
application I find that MS Access is still running when I look in task
manager. If I then re-open my app, or open another app, I may end up with
just one instance of MS Access running, or I might end up with another. At
one stage I found 4 instances of MS Access running in the task manager list
but I didn't have a single application or MS Access session running. Can
anyone suggest what might be going wrong?

 

[Arvin Meyer MVP]

Care must be taken to close objects that you open because sometimes they
stay in scope after the procedure has ended. So when you open a recordset,
explicitly close it and set it to nothing, like:

Function Foo()
On Error GoTo Error_Handler

Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

Set rst = db.OpenRecordset("Select * From Whatever")
' Do something

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

There was also a bug in Access VBA, which may still be extant where Boolean
controls need to be explicit. Instead of:

If Me.chkWhatever Then

use:

If Me.chkWhatever = True Then

 

[June7]

Do you make connection(s) to other project, perhaps data backend? Any VBA or
macros? Maybe somehow code is opening an Access session.

 

[Robbo]

Thank you for this post. I know I should always set rs to nothing but on one
sub I forgot to do it and that appears to be what was causing the problem.
Thank you very much for pointing me to a solution.
--
Cheers
Rob

Care must be taken to close objects that you open because sometimes they
stay in scope after the procedure has ended. So when you open a recordset,
explicitly close it and set it to nothing, like:

Function Foo()
On Error GoTo Error_Handler

Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

Set rst = db.OpenRecordset("Select * From Whatever")
' Do something

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

There was also a bug in Access VBA, which may still be extant where Boolean
controls need to be explicit. Instead of:

If Me.chkWhatever Then

use:

If Me.chkWhatever = True Then
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Robbo]

Thanks June7. Yes I do make that connection but in this case it appears the
problem occurred because I forgot to set rs=nothing on one of my functions

 

[Robbo]

Spoke too soon. It was a good inclusion to put this in where I had missed it
but problem still exists. It just didn't happen the first time I tried it
after including the set rs=nothing code. Back to the drawing board.
--
Cheers
Rob

Care must be taken to close objects that you open because sometimes they
stay in scope after the procedure has ended. So when you open a recordset,
explicitly close it and set it to nothing, like:

Function Foo()
On Error GoTo Error_Handler

Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb

Set rst = db.OpenRecordset("Select * From Whatever")
' Do something

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

There was also a bug in Access VBA, which may still be extant where Boolean
controls need to be explicit. Instead of:

If Me.chkWhatever Then

use:

If Me.chkWhatever = True Then
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer MVP]

For recordsets, first close them, then set the variable to Nothing.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Spoke too soon. It was a good inclusion to put this in where I had missed
it
but problem still exists. It just didn't happen the first time I tried it
after including the set rs=nothing code. Back to the drawing board.

 

[Robbo]

Hi, After intensive debugging I found that if I comment out one DLookup the
application closes correctly. I have posted below the offending function and
highlighted the offending line of code. I have no idea, yet at least, why
this is happening.

Function FirstSapCode()
Dim sSql As String
Dim rs As Recordset
Dim datBirthday As Date

On Error GoTo FirstSapCode_Error
If IsNull(SAPNum) Then Exit Function
If IsNull(Me.StartDate) Then Exit Function
datBirthday = DLookup("Birthdate", "tblEmployees", Me.SAPNum) 'OFFENDING
CODE
sSql = "SELECT BaseSAPCode, PHSapCode " _
& "FROM tblPayScaleDetails INNER JOIN " _
& "(tblEmployees INNER JOIN tblEmployeePayScales ON
tblEmployees.SAPNum = tblEmployeePayScales.SAPNum) " _
& "ON tblPayScaleDetails.RateTableName =
tblEmployeePayScales.RateTableName " _
& "WHERE tblEmployees.SAPNum='" & Me.SAPNum & "' " _
& "AND tblEmployeePayScales.Effective<=#" & Format(Me.StartDate,
"mm/dd/yyyy") & "# " _
& "AND tblEmployeePayScales.Until>=#" & Format(Me.StartDate,
"mm/dd/yyyy") & "# " _
& "AND tblPayScaleDetails.FromTime<=#" & TimeValue(Me.StartDate) & "#
" _
& "AND tblPayScaleDetails.ToTime>=#" & TimeValue(Me.StartDate) & "# " _
& "AND tblPayScaleDetails.FromAge=" _
& IIf(CalcAge(SAPNum, datBirthday, Me.Parent.FortnightCommencing) >
21, _
21, CalcAge(SAPNum, datBirthday,
Me.Parent.FortnightCommencing)) & " " _
& "AND tblPayScaleDetails.Daynum=" & DLookup("Daynum",
"tblDayNumXRef", Weekday(Me.Start, vbMonday)) & " " _
& "AND tblPayScaleDetails.PayableKey='F'"
Set rs = CurrentDb.OpenRecordset(sSql)
If Not rs.EOF Then
rs.MoveFirst
If Nz(Me.Parent.HolidayDescription, "") <> "" Then
FirstSapCode = rs!PHSAPCode
Else
FirstSapCode = rs!BaseSAPCode
End If
End If

FirstSapCode_Exit:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If


On Error GoTo 0
Exit Function

FirstSapCode_Error:
Stop
LogError Err.Number, Err.Description, "Form_frmSubTimeEntries Subform",
"FirstSapCode", Erl
GoTo FirstSapCode_Exit
Resume

End Function

 

[Robbo]

Re-wrote my DLookup and problem fixed. Could not see the wood for the trees
when I made my last post. Sorry to have wasted your time.

 

[Tony Toews [MVP]]

Re-wrote my DLookup and problem fixed. Could not see the wood for the trees
when I made my last post. Sorry to have wasted your time.

How so? What was the problem?

Inquiring minds would like to know so we can help the next person.

Tony

 

[Robbo]

Hi Tony,

Embarrassed that I didn't pick it up immediately and surprised that the
application even compiled. The code was:
datBirthday = DLookup("Birthdate", "tblEmployees", Me.SAPNum)
and should have been
datBirthday = DLookup("Birthdate", "tblEmployees", "SAPNum = '" & Me.SAPNum
& "'")

 

[Tony Toews [MVP]]

Embarrassed that I didn't pick it up immediately and surprised that the
application even compiled. The code was:
datBirthday = DLookup("Birthdate", "tblEmployees", Me.SAPNum)
and should have been
datBirthday = DLookup("Birthdate", "tblEmployees", "SAPNum = '" & Me.SAPNum
& "'")

I can understand why the application compiled successfully. Access
VBA doesn't do that kind of syntax checking.

That said I would've thought DLookup would fail with a message along
the lines of invalid parameter, bad syntax or something like that.

But then I've never used DLookup.

Tony

 

[John W. Vinson]

I can understand why the application compiled successfully. Access
VBA doesn't do that kind of syntax checking.

That said I would've thought DLookup would fail with a message along
the lines of invalid parameter, bad syntax or something like that.

But then I've never used DLookup.

Tony

Actually that's valid syntax. The third argument just needs to evaluate to
True (nonzero, nonblank) or False (zero). Assuing that SAPNum is nonzero,
this dlookup would just return the first record in the table.

 

[Tony Toews [MVP]]

Actually that's valid syntax. The third argument just needs to evaluate to
True (nonzero, nonblank) or False (zero). Assuing that SAPNum is nonzero,
this dlookup would just return the first record in the table.

Interesting. I would've thought it would evaluate to a Where clause.

Tony