Problem with Access function and database Lock (1 Viewer)

[thtadthtshldntb]

Hello Everyone,

I am (have to) use Access 2007 to create and run a database to deal with monthly stats from one of our EMRs.

One of the things that I have to do is normalize all the different ways that the staff free text these entries in for referring provider.

So what I did was create a table with all provider name variants that I update as I find new variations and then wrote this (with help)

Function UpdateProviders()
 
'declare variables
 
DAO.DBEngine.SetOption DAO.dbMaxLocksPerFile, 100000000
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim UPArray() As String
Dim icounter As Long
Dim actrecords As Integer 'will store total value of actual records
Dim strProvTable, strSQL As String
 
'Providr table SQL
 
    strProvTable = "SELECT vpProviderNames.[LastName], vpProviderNames.[FirstName], vpProviderNames.[FullName], vpProviderNames.[KeeporReplace]" & _
                    "FROM vpProviderNames;"
 
'different saved values for strSQL
 
    'strSQL = "SELECT testvpExamlist.[Referring Physician]" & _
            "FROM testvpExamlist;"
           
    'strSQL = "Select testvpimporteddata.[Referring Physician]" & _
            "From testvpimporteddata;"
              
    strSQL = "SELECT testvpimporteddata.[Performing Physician], testvpimporteddata.[Reading Physician], testvpimporteddata.[Referring Physician]" & _
            "FROM testvpimporteddata;"
 
'open recordsets
 
Set db = CurrentDb
Set rs = db.OpenRecordset(strProvTable, dbOpenDynaset, dbSeeChanges)
 
 
'count actual providers and add them to UPArray
 
If Not rs.EOF Then
 
    actrecords = 0
   
    rs.MoveFirst
   
    Do Until rs.EOF 'finds number of actual providers in list
               
        If rs.Fields("KeeporReplace") = 1 Then
            actrecords = actrecords + 1
        End If
       
        rs.MoveNext
                       
    Loop
   
    'Debug.Print "total actual records = " & " " & actrecords
   
    rs.MoveFirst
   
    icounter = 0
   
    ReDim UPArray(actrecords, 3)
   
    Do Until rs.EOF
   
        If rs.Fields("KeeporReplace") = 1 Then
            If Not IsNull(rs.Fields("LastName")) Then
                    UPArray(icounter, 0) = rs.Fields("LastName")
            ElseIf IsNull(rs.Fields("LastName")) Then
                    UPArray(icounter, 0) = ""
            End If
           
            If Not IsNull(rs.Fields("FirstName")) Then
                    UPArray(icounter, 1) = rs.Fields("FirstName")
            ElseIf IsNull(rs.Fields("FirstName")) Then
                    UPArray(icounter, 1) = ""
            End If
           
            UPArray(icounter, 2) = rs.Fields("FullName")
           
            'Debug.Print "items: "; icounter & " " & UPArray(icounter, 0) & " : " & UPArray(icounter, 1) & " : " & UPArray(icounter, 2)
            icounter = icounter + 1
            rs.MoveNext
           
        Else
            rs.MoveNext
           
        End If
       
    Loop
   
End If
   
Set rs = Nothing
 
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
   
'updates providers
        
For i = 0 To rs.Fields.Count - 1 ' keeps loop in database fields
 
    rs.MoveFirst
   
    Do While Not rs.EOF
   
        'Debug.Print rs.Fields(i).Value
   
        For icounter = 0 To actrecords - 1
            'If IsNull(rs.Fields(i).Value) Then
                'rs.Fields(i).Value = "None Entered"
            'End If
           
            If Not IsNull(rs.Fields(i).Value) Then
                If (InStr(1, rs.Fields(i).Value, UPArray(icounter, 0), 1) > 0 And InStr(1, rs.Fields(i).Value, UPArray(icounter, 1), 1) > 0) Then
                    rs.Edit
                    rs.Fields(i).Value = UPArray(icounter, 2)
                    rs.Update
                End If
            ElseIf IsNull(rs.Fields(i).Value) Or (InStr(1, rs.Fields(i).Value, "REFERRING NO", 1)) Then
                rs.Edit
                rs.Fields(i).Value = "No Entry"
                rs.Update
            End If
        Next
                   
        rs.MoveNext
       
    Loop
   
    rs.MoveFirst
   
Next
   
Set rs = Nothing
Set db = Nothing
 
 
End Function

It works as intended, however whenever I run the function, I get an access notice/error in VBA stating that I cannot save changes to my code because I no longer have exclusive access.

This of course goes away when I close and reopen. This is a really annoying thing when I am tweaking the code. Also, if I run it via macro with all tables and vba closed it still does this error.

I have some more tweaking to do to move it to the live tables.

I have searched around the net and the only thing that comes up is that the currentdb function is causing some sort of lockup issue. But I don't know.

Any input is appreciated.

 

[Cronk]

You cannot change module changes if anyone else has the database open. Maybe you have two instances of the database open at the same time?

Are you sure the code runs without error because there is no space in the SQL generated in the string because there is no space before the FROM clause and the single dimension array is used as two dimensions.

Also I very much doubt you need to increase the number of table locks to 100 million. In over 20 years, I've never had to increase the default with some pretty big data sets.

 

[thtadthtshldntb]

You cannot change module changes if anyone else has the database open. Maybe you have two instances of the database open at the same time?

I am the only one who even knows this database exists and if I just open it and run the macro that runs the function on the test table, this behavior occurs. It runs perfectly (except for one aspect of an if statement in terms of skipping a correction/replacement)

Are you sure the code runs without error because there is no space in the SQL generated in the string because there is no space before the FROM clause and the single dimension array is used as two dimensions.

The SQL is literally copy pasted out of test queries. Something might have been lost in formatting because I had copy paste to an email, email it to myself outside of work and then copy paste it into the forum .

The array is redimmed later as a 2d array. I borrowed a piece of this code from someone else in that aspect, when I was figuring out how to skip known idiocy in erroneous data entry the users make for that data field and only correct the providers I want, and just declaring it later might be a cleaner way to do it, but I kind of like declaring all my variables in one section initially

Also I very much doubt you need to increase the number of table locks to 100 million. In over 20 years, I've never had to increase the default with some pretty big data sets.

I was getting an error very early on doing anything like this sort of thing on any of my work workstations and I tracked it down to corporate IT having an absurdly low amount of locks hardcoded in policy. So I just picked a huge number to get it out the testing phase and figured it be something I would refine later, when I did some calcs on this databases growth, etc.

It runs as intended. It reads through those three columns of data and performs all the intended substitutions except 1 (that if statement issue I have to work on).

Something about it however knocks the database out of exclusive mode, even if I set it on open to be in exclusive mode.

 

[Frothingslosh]

After the first big IF/THEN loop, I notice you are setting rs to nothing without closing it. Try explicitly closing the recordset first, and see if this behavior continues. (Anything you explicitly open should be closed when you're done with it, or else it will stay open but unassigned until you close the application. That can cause some odd behavior, such as the application acting like multiple people are using it when only one person has it open.)

While you're at it, you should close the second recordset, too.

 

[thtadthtshldntb]

After the first big IF/THEN loop, I notice you are setting rs to nothing without closing it. Try explicitly closing the recordset first, and see if this behavior continues. (Anything you explicitly open should be closed when you're done with it, or else it will stay open but unassigned until you close the application. That can cause some odd behavior, such as the application acting like multiple people are using it when only one person has it open.)

While you're at it, you should close the second recordset, too.

I added

rs.close before the first and second

set rs = nothing

and

db.close

before

set db = nothing

No change.

I was using googlefu on this issue too and it looks like the .close method is something that does not do anything anymore unless your database is physically in another file, so it closes for that file. I am not sure on that.

 

[Cronk]

Try decompiling your code.

In order and closing/opening the database in between each step
Compact/Repair
Decompile
Compile
Compact/Repair

 

[thtadthtshldntb]

Try decompiling your code.

In order and closing/opening the database in between each step
Compact/Repair
Decompile
Compile
Compact/Repair

This was going to be my next major step, even though I am reasonably sure it would not have solved it.

But I sort of solved it.

for the record I am running Access 2007 on Windows 7. I don't think access has ever been patched by corporate and windows 7 only has gotten security patches and nothing else (policy).

The issue is the use of the currentdb shortcut.

Basically this creates a pointer to your current database. Apparently this is buggy. If I could run it on my home machine (win 10 with access 2016) I bet it would not happen.

So what I did was get rid of currentdb.

Instead i did

set db = opendatabase("my database with location here")

and the problem went away.

thanks guys for your help though. I will avoid using shortcuts like

me.whatever or currentdb on my work machine.

btw, I did cut off 2 orders of magnitude on those file locks and it still works. So once I get some more things added in here I will tweak that.

 

[static]

You are doing a lot of needless looping.

Your code appears to boil down to a simple SQL update statement...

update testvpimporteddata set [fieldX]='fullname' where [fieldX] like ('*firstname*lastname*') or [fieldX] like ('*lastname*firstname*')

Function UpdateProviders()
    Dim db As dao.Database
    Dim rs As dao.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from vpProviderNames where KeeporReplace=1")
    Do Until rs.EOF
        UpdateNames db, "Performing Physician", rs("firstname"), rs("lastname"), rs("fullname")
        UpdateNames db, "Reading Physician", rs("firstname"), rs("lastname"), rs("fullname")
        UpdateNames db, "Referring Physician", rs("firstname"), rs("lastname"), rs("fullname")
        rs.MoveNext
    Loop
    rs.close
End Function

Private Function UpdateNames(db As dao.Database, fld As String, firstname As String, lastname As String, fullname As String) As Long
    SQL = "update testvpimporteddata set [$1]='$2' where [$1] like ('*$3*$4*') or [$1] like ('*$4*$3*')"
    SQL = Replace(SQL, "$1", fld)
    SQL = Replace(SQL, "$2", fullname)
    SQL = Replace(SQL, "$3", firstname)
    SQL = Replace(SQL, "$4", lastname)
    db.Execute SQL
    UpdateNames = db.RecordsAffected
    Debug.Print "field:", fld, "updates", UpdateNames, "for ", fullname
End Function

 

[thtadthtshldntb]

Ill give that a shot. I am always open to suggestions (though as I mentioned using currentdb will cause me a problem).

my code is designed to not need to know how many names are on a staff list or how many fields are being audited ( currently just a provider provider list but eventually being lots of other types of staff, meaning I can just dump this function into other similar databases and have strSQL and strProvtable be the only things I have to sent to it (in its final version which its not quite at yet).

that provider list also contains all known (and regularly input to our chagrin) misspellings of provider names and corrects for them.

like I said, though, I will give it a shot.

 

[static]

"as I mentioned using currentdb will cause me a problem"

If currentdb gives you issues then there is a bigger issue somewhere. currentdb just returns a reference to dbengine(0)(0) and if there was a problem with dbengine(0)(0) Access wouldn't work.


"my code is designed to not need to know how many names are on a staff list"

My code didn't change that. ? All names from vpProviderNames where KeeporReplace=1 are checked.

If your other table testvpimporteddata just had names and a field to identify PhysicianType (Performing/Reading/Referring) you'd only have to do one update per name instead of 3.

 

[thtadthtshldntb]

"as I mentioned using currentdb will cause me a problem"

If currentdb gives you issues then there is a bigger issue somewhere. currentdb just returns a reference to dbengine(0)(0) and if there was a problem with dbengine(0)(0) Access wouldn't work.

There are known issues with currentdb and various version of access and windows. You can search all over for them. There is zero chance of me getting corporate to patch office 2007 and slime chance of an upgrade for the res tof the year....so i cant use it.

"my code is designed to not need to know how many names are on a staff list"

My code didn't change that. ? All names from vpProviderNames where KeeporReplace=1 are checked.

If your other table testvpimporteddata just had names and a field to identify PhysicianType (Performing/Reading/Referring) you'd only have to do one update per name instead of 3.

I said that I am gonna give it a shot. I need to test and see how it runs, to learn it. I am an intermediate type user who has not done much programming beyond some SQL since my mid 20s...got to dust off some old skills.

the updating comes from the fact that staff spell/misspell provider names wrong multiple ways. I have a table with all known misspellings of first and last names.

I don't do three updates, i have the search that looks for the lastanme and first name misspellings or even correct spellings in the whole name string..and if they find it, they replace with the standardized provider name

for example...take this one..

Mushayandebvu, taonei, MD.

That has about ...6 known regular misspellings, so i check for all of them...

 

[static]

There are known issues with currentdb and various version of access and windows. You can search all over for them. There is zero chance of me getting corporate to patch office 2007 and slime chance of an upgrade for the res tof the year....so i cant use it.

I searched for 'known issues with currentdb' and found nothing.
If you have links I'd be interested to read them.

I said that I am gonna give it a shot. I need to test and see how it runs, to learn it. I am an intermediate type user who has not done much programming beyond some SQL since my mid 20s...got to dust off some old skills.

the updating comes from the fact that staff spell/misspell provider names wrong multiple ways. I have a table with all known misspellings of first and last names.

All of this is obvious from reading your code.

I don't do three updates, i have the search that looks for the lastanme and first name misspellings or even correct spellings in the whole name string..and if they find it, they replace with the standardized provider name

for example...take this one..

Mushayandebvu, taonei, MD.

That has about ...6 known regular misspellings, so i check for all of them...

Your code loops through the (3) table fields, my code runs an update for each field. Same difference.
Point stands that one field would be easier to check.

 

[thtadthtshldntb]

I searched for 'known issues with currentdb' and found nothing.
If you have links I'd be interested to read them.

When I searched for the original error message I posted about, and variations on the wording, I found threads which mention that there are issues with currentdb and older access and windows versions. I think the one that actually got me to stop using currentdb in this code was on this forum.

Your code loops through the (3) table fields, my code runs an update for each field. Same difference.
Point stands that one field would be easier to check.

Like I said, I have to see how your code works with what I am doing. Its a different way than I know how to do it. If it works great I will let you know, when I get back to my scheduled time on this project (its something I have to squeeze in around like 10 other things).

I can kind of see what you are doing...you seem to be checking and updating each row at once, as opposed to each field, field by field, like I did.