Module to remove noisewords (1 Viewer)

[harrisw]

Having all kinds of problems with the code below...
If I run it it gives out maxlocks errors. Ive moved the registry entry to the max but it still gives the error


Public Function NoiseWords(sCompanyName As Variant)

Dim sTestString As String
Dim sTestString2 As String
Dim sReturnString As String
Dim lCounter As Long
Dim NoiseRs As DAO.Recordset

Set NoiseRs = CurrentDb.OpenRecordset("zz_NoiseWord", 2)

sTestString = sCompanyName
sReturnString = sTestString

If Len(sCompanyName) > 0 And InStr(sCompanyName, "PUBLIC LIMITED COMPANY") = 0 Then

sTestString2 = Right(sTestString, Len(sTestString) - InStrRev(sTestString, " "))
lCounter = Len(sTestString)

Do Until lCounter <= 0

NoiseRs.FindFirst "[Noiseword] = """ & sTestString2 & """"

If NoiseRs.NoMatch = True Then

Exit Do

Else

sReturnString = Trim(Left(sTestString, Len(sTestString) - Len(sTestString2)))

End If

sTestString = Trim(Left(sTestString, InStrRev(sTestString, " ")))
lCounter = InStrRev(sTestString, " ")
sTestString2 = Right(sTestString, Len(sTestString) - InStrRev(sTestString, " "))

Loop


NoiseWords = sReturnString

End If


Any help appreciated.

 

[The_Doc_Man]

I'm going to guess that this runs a few times before it blows up on you.

Part of your problem comes from not reading that book, "The Most Important Things I Know I Learned In Kindergarten."

So what is in THAT book that is so important? "If you open it, close it. If you take it out, put it back." Well, you rode your recordset hard and left it out to dry.

More specifically, you open NoiseRS every time you enter your subroutine but I don't see where you close it. Every time you execute a SET NoiseRS = DB.OpenRecordset, that is the source of one of your locks. But... when you call that routine a second time, the next SET imperative doesn't do what you really wanted it to do. It does NOT close the previous opening of the recordset. All it REALLY does is de-instantiate a pointer. The old recordset is still open but now nobody has a pointer to it. See, your line with the SET verb did TWO things - open a recordset AND establish a way to get to it.

You should always do a recordset.Close when done - at the minimum. If it happens that the recordset is EXTERNAL to the DB (i.e. linked via ODBC or other Office links), also do an explicit

SET recordset = Nothing

to fully deinstantiate it before trying to use it again. Otherwise you will fill up Windows memory with junk a lot faster than normal for Windows.

 

[harrisw]

Thanks for your reply

Ive added a "noisers.close" just before the end function but still get the maxlocks error.

What am I doing wrong?

It also doesnt need to run several times before it blows, it doesnt even run once.

 

[The_Doc_Man]

What is zz_noiseword? Query? Table? ODBC linked entity?

 

[harrisw]

No its just another table in the same access database.

It just consists of one column which is the noiseword