Report is locking database

[Ken]

I have a split Acc2000 database with several reports. Most of my reports are
based on a QueryDef. When I run a report, a locking instance occurs (which
is normal) and when I close it the lock is released.

Unfortunately, one of my reports isn't cooperating. It never releases the
lock when it closes and I don't know why. Is there any way to force this to
happen? Thanks.

Ken

 

[Maurice]

Are you creating the querydefs based on a recordset. Maybe the recordset
isn't being closed when finishing creating the querydef.

should be something like:

rs.close
set rs=nothing

But that's merely an assumption that I think you already did...

Maurice

 

[Ken]

Maurice,

Thanks for your reply. Unfortunately, I'm not opening a recordset. The
QueryDef is, however, based on another query ("qryInventory") which is based
on a table. Here's my code:

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If

CurrentDb.QueryDefs("qryInventory2").SQL = strSQL

'DoCmd.OpenQuery "qryInventory2", acViewPreview

DoCmd.OpenReport "rptInventoryDetailed", acViewPreview

As you can see, it's pretty straight-forward. The BuildSQLString function
(not shown) allows the user to build the strSQL string on-the-fly.

What's interesting is that if I comment out the report, and display the
query instead, my back-end unlocks when I close it and the form. So, the
query doesn't appear to be the problem. Only after opening the report do I
run into my problem.

Ken

 

[Maurice]

Ken, you made it clear. The qeurydef is not the problem. Have you tried
looking at the report properties. There is an options which locks records.
Check this to see if it says "No Locking". Just to make sure this isn't the
problem.