OpenSchema method causes suspect record lock in MDB

[damage]

Hello Tek-Tip'ers,

Any help on this would be appreciated.

I have an Access 2000 application which has seperate front and back ends. It uses the following module to check if a table link exists in the frontend before I try and link to it in the backend;

******CODE*************

Public Function TableExists(ObjName As String) As Boolean
'Determines if a table exists in the front end database
'Checks on the connLocal ADO connection

On Error GoTo Err_TableExists

TableExists = False

Set TablesSchema = connLocal.OpenSchema(adSchemaTables)
While Not TablesSchema.EOF
If TablesSchema!TABLE_NAME = ObjName Then
TableExists = True
Else
'Nowt
End If
TablesSchema.MoveNext
Wend

Exit_TableExists:
Err.Clear
Exit Function
Err_TableExists:
TableExists = False
MsgBox "Error in table exists" & vbCrLf & Err.Description
bError = True
Resume Exit_TableExists
End Function


*******CODE************

I have opened my connection to the front end (itself) by calling;

With connLocal
.Provider = "Microsoft.JET.OLEDB.4.0;"
.Open strMainDB
End With

Where strMainDb is the full path to the front end


I am using the LDB Viewer from the MS web site to check my record locks on the back end MDB file. What happens is that as soon as I create my initial ADO connection I get a lock (as expected).
As soon as I call "Set TablesSchema = connLocal.OpenSchema(adSchemaTables)" in the function I get anonther lock appear on the MDB file, but this one is 'suspect' - and remains that way until I close all my connections to the file.

I have another ADO connection object connected directly the back end MDB file - would this fact be causing something funny to happen?

Any help appreciated folks.

All the best,

Mark.

 

[cmmrfrds]

What is kind of problem is this causing?

You should explicitly define and destroy your recordset object. The object will remain in memory until destroyed.

Dim TablesSchema as ADODB.Recordset
Set TablesSchema = New ADODB.Recordset

After finishing

Set TablesSchema = Nothing