VBA: Database locked by Admin

[Harry1234]

Hello Bytes Community

I have a problem with an access DB I was hoping you could help me with

I have a MS Access 2010 database which is locking the database prior to me running the following code which takes a list from the sharepoint portal and inputs it into an array for further processing down the line

It fails on the line in Set ishare_query = dbs.QueryDefs(s trqueryname), either with the database has been locked by the admin in this case me or Object Variable or With block not set

I have tried the following

1) I have compacted and repaired the database

2) I have renamed the database and then compacted and repaired it

3) I have set the properties of the database to shared

Public Function Retrieve_Sharepoint_Records() As Variant

    On Error GoTo ErrorHandler
 
        Dim dbs As DAO.Database
 
        Dim ishare_query As DAO.QueryDef
 
        Dim rstTableName As DAO.Recordset   'Your table
 
        Dim MyArray() As Variant            'Your dynamic array
 
        Dim intArraySize As Integer         'The size of your array
 
        Dim iCounter As Integer             'Index of the array
 
        DoCmd.SetWarnings False
 
 
        strDateLimit = Format(DateAdd("m", -5, Date), "dd-mm-yyyy")

        strqueryname = "Q1"
 
        Set dbs = OpenDatabase("C:\Users\User\Desktop\DB1.accdb")
 
        Set ishare_query = dbs.QueryDefs(strqueryname)
 
        ishare_query.SQL = "SELECT HAWB, Orig Ctr], Dest Ctr], True Value], Undervalued.[True CUR], [Created By], " & _
 
        "Undervalued.Created INTO [1-T_ISHARE INFO]FROM Undervalued WHERE (((Created)>=#" & strDateLimit & "#));"
 
 
 
        DoCmd.OpenQuery "Q1", acViewNormal, acEdit
 
        DoCmd.OpenQuery "Q2", acViewNormal, acEdit
 
		' Table 2 is generated from the query Q2
        Set rstTableName = CurrentDb.OpenRecordset("2-DEDUP")
 
        If Not rstTableName.EOF Then
 
            rstTableName.MoveFirst
 
            rstTableName.MoveFirst
 
            numrecs = rstTableName.RecordCount
 
           'use "GetRows" method to assign to an array
 
            MyArray = rstTableName.GetRows(numrecs)
 
        End If
 
 
 
        Retrieve_Sharepoint_Records = MyArray
 
        Debug.Print "***Ishare Records Retrieved " & numrecs & " Rows***"
 
ErrorHandler:
 
        DoCmd.SetWarnings True
 
        If (Len(Err.Description) > 0) Then
 
            MsgBox (Err.Description)
 
        End If
 
End Function

Anyone who can see the problem will save my sanity J

Is there a piece of code that will unlock the database if the error is thrown

Maybe there is a setting I have missed out on. Any help would be greatly appreciated

[jimatqsi]

Harry,
About the " Object Variable or With block not set", dbs is the object variable in that line and you have not set it. The Set statement above is commented out so dbs is nothing.

I presume when you got the database locked error you were running a test where dbs was actually set?

If so, to resolve that question we need to see the SQL code from the query you're running. You can get that from the query builder in SQL view.

Jim

[jimatqsi]

Also, to get an accurate record count you need to go to the end of the recordset. So 89-93 should read

            rstTableName.MoveLast
 
            numrecs = rstTableName.RecordCount

            rstTableName.MoveFirst

Jim

[Harry1234]

Hi Jim

Firstly thank you very much for the quick response
You are correct it dies on the the line where i set the dbs variable

I use that variable to update the query (which has been created in the Access DB) with the SQL

ishare_query.SQL = "SELECT HAWB, [Orig Ctr], [Dest Ctr], True Value], [True CUR], [Created By], " & _ 
  
        "Undervalued.Created INTO [1-T_ISHARE INFO]FROM Undervalued WHERE (((Created)>=#" & strDateLimit & "#));"

The original is the same except the date is different. I run it once a week and the strDateLimit is the only thing that changes

I have also moved the code around so this is the first function being called and as a result the first use of the DAO

Here is the SQL in the Query

SELECT Undervalued.HAWB, [Orig Ctr], [Dest Ctr], [True Value], [True CUR], [Created By], Created INTO [1-T_ISHARE INFO]
FROM Undervalued
WHERE (((Created)>=#12/27/2013#));

[jimatqsi]

Harry,
I'm not sure I understand. Line 65 cannot work because line 63 is commented out. Are you saying when you remove ' from line 63 the program fails on that line? What is the nature of the failure?

Remove line 39 for now. When it is time to put it back in you'll want to put immediately before the one line that you know you don't want any messages from, and reverse it immediately after that one line. Eliminating messages from the entire code gets in the way of debugging it.

I don't understand why line 59 references query "Q1 Retrieve All Information From ISHARE" but line 73 references query "Q1". What is the relationship between them? And what is query "Q2"?

Jim

[Harry1234]

Hi Jim

Sorry, I was attempting to anonomize the code while in a meeting...multi-tasking :)
I have updated it in the original question

Basically Q1 takes messy information from a sharepoint list
Q2 depuplicates that list

Due to performance issues, Q1 was set up so that it only took the previous couple of months from the sharepoint list

It is then loaded from the table 2-DEDUP into an array for further processing down the code

Apologies again for the ambiguity. I appreciate all feedback as i have a feeling this problem will come up in other projects

[jimatqsi]

Harry, it's a really bad idea to change your posts after getting feedback. It means subsequent readers can't follow the conversation and might not be able to learn what you learned during the discussion.

At this point I am not sure what problem we're trying to solve. Is the error "the database has been locked by the admin" still a problem? Is "the Object Variable or With block not set" still a problem?

I noticed your reposted code still is not moving to the end of the recordset before getting the record count.

Also, what is the name of the database this code is running in? Is it C:\Users\User\D esktop\DB1.accd b? If so, you should use

set dbs = currentdb

instead of

set dbs = OpenDatabase("C:\Users\User\Desktop\DB1.accdb")

That alone might solve your problem with the locked database.

Jim

[Harry1234]

Hi Jim

Apologies for all the confusion
Im running it now with the changes to Set DBS (it takes several hours but so far looks good)

Can you tell me setting it to the current DB is different from simply specifying the full path of the DB

Will this work for example if i load the DB from a network drive

Im interested in understand the why as well as the answer

Again, thank you very much for your help. It has been a tremendous learning experience

[jimatqsi]

Harry,
When you specified the full path to the DB, apparently Access was opening a new, separate channel to the database. That is, it looked like two separate users were accessing the DB. Therefore, you were set up to make a conflict between you and yourself when any resource was locked by either instance of yourself.

If you are going to load the DB from a network drive it shouldn't make any difference unless you plan to allow multiple users to access simultaneously. In that case, only data should be in the remote database and each user should have their own copy of the front-end on their local machine and you may have to build some logic to coordinate user activities so one user is not rebuilding a table that another user is using at that moment.

Glad to know you may have gotten over the hurdle on this one.

Jim

[NeoPa]

I've changed the Best Answer as post #8 wasn't.