What determines if someone has exclusive access to a database? (1 Viewer)
- Thread starter Alc
- Start date
- Status
Good to hear, what with?
- Local time
- Today, 18:58
- Joined
- Feb 19, 2002
- Messages
- 43,233
I can't open your app because you are making a call to an ODBC database. Not only that, you have disabled the close Access button which forced me to use task manager to close the database. I won't be opening it again. But, one thing I did notice before I abandoned the task is that you have compile errors that need to be attended to.
Thanks for the comments. I don't expect you to waste any time on this.
I converted all tables to local versions but forgot to remove the call to connect to them. Now done.
I didn't disable the button. It stops working for some reason when another occurs. I've just gone in and out a few times and it works fine.
Compile errors were caused by creating the cut-down version too quickly and removing certain areas that don't relate to the problem. All now fixed.
If anyone else is looking at this, I've replaced the faulty version with a new one.
I figured I'd give it a try after you said issues were fixed. Not only do I not see what form has the combo mentioned in step 7 (and there are a lot of forms), on the first one I opened I clicked on a tab control page (I guess). It went into some sort of datasheet view and I couldn't close it because it seems you've also disallowed the standard shortcuts. I also had to use Task Manager to get out of it.
Sorry, I'm not sure where the confusion is coming in?
Ignore the tab controls. The tab controls aren't relevant, just use the buttons named.
I've attached a document with pictures of each stage up to 7.
Last edited:
- Local time
- Today, 23:58
- Joined
- Feb 19, 2013
- Messages
- 16,610
you have timer events running - makes it very difficult to edit code whilst code is running. Suggest disable all your timer events, then make your changes. Then reinstate them before final testing and release to users
I guess you expect that I'd download a db, just open it and allow code to run? Not a chance. Thus I don't know what form you're referring to. Since CJL has voiced a 'complaint' I'll also add this - I opened Access and clicked in the list and opened your db by accident. I click on the button to close the db and it errors because of a problem with the code (something about a path if I recall). Again, back to Task Manager to close Access because nothing else works. Moral of the story is, if you know how to raise these roadblocks then you must know how to remove them and should do so. Otherwise, you frustrate those trying to help you.
Last edited:
Okay, not trying to be difficult but it seems I have been. I genuinely appreciate the help certain people have offered.
New version of the db attached. Timer disabled, all menus and close button visible.
Instructions for how I recreate the problem (including relevant form names) as follows:
1. I open the db (form ModelWarranty is displayed)
2. Alt + F11 and make any change to the code. No problems.
5. Click on <Uncovered Items> (form frmuncoveredusermgmt(MV) is displayed)
6. Alt + F11 and make any change to the code. Still no problems.
7. From the ‘Analyst’ drop down on that form, select ‘RB’
8. From the ‘Time and Exceedance’ drop down on that form, select ‘All open items’
9. You should see one record displayed in the top subform
10. In the bottom right subform, type a 12 digit value into the QIC field e.g. 1234567890-12
11. Tab out of that field
12. The VB code errors. If you try to make any changes to it, you get the error message “Run time error 3734: The database has been placed in a state by user ‘Admin’ on machine ‘{my PC name}’ that prevents it from being opened or locked”
Please note that doing things other than the steps described may well cause errors. This does not happen in the full version, so is a result of cutting the db down to size that can be uploaded. I'm just trying to work out why I have a problem with the two forms named, and their related subforms.
New version of the db attached. Timer disabled, all menus and close button visible.
Instructions for how I recreate the problem (including relevant form names) as follows:
1. I open the db (form ModelWarranty is displayed)
2. Alt + F11 and make any change to the code. No problems.
5. Click on <Uncovered Items> (form frmuncoveredusermgmt(MV) is displayed)
6. Alt + F11 and make any change to the code. Still no problems.
7. From the ‘Analyst’ drop down on that form, select ‘RB’
8. From the ‘Time and Exceedance’ drop down on that form, select ‘All open items’
9. You should see one record displayed in the top subform
10. In the bottom right subform, type a 12 digit value into the QIC field e.g. 1234567890-12
11. Tab out of that field
12. The VB code errors. If you try to make any changes to it, you get the error message “Run time error 3734: The database has been placed in a state by user ‘Admin’ on machine ‘{my PC name}’ that prevents it from being opened or locked”
Please note that doing things other than the steps described may well cause errors. This does not happen in the full version, so is a result of cutting the db down to size that can be uploaded. I'm just trying to work out why I have a problem with the two forms named, and their related subforms.
Last edited:
I guess this will take some time. I get error 91 "object variable or with block variable not set" when I tab out. I know what it means, and I know why. Even though you have Option Explicit set at the top of the module, you don't declare db yet you use it all over the place. I've been looking to see if you declared it publicly (globally) but haven't found it, so I'm baffled as to why the code compiles at all. You also invoke to Set db = Nothing, yet in that code, haven't declared it. IIRC, it ought to error on that line as well.
Will continue to play, but have limited time. I'm not getting much else done
Will continue to play, but have limited time. I'm not getting much else done
Please don't spend any more time on this.I guess this will take some time. I get error 91 "object variable or with block variable not set" when I tab out. I know what it means, and I know why. Even though you have Option Explicit set at the top of the module, you don't declare db yet you use it all over the place. I've been looking to see if you declared it publicly (globally) but haven't found it, so I'm baffled as to why the code compiles at all. You also invoke to Set db = Nothing, yet in that code, haven't declared it. IIRC, it ought to error on that line as well.
Will continue to play, but have limited time. I'm not getting much else done
Db is declared as a global variable in module 'modPubRoutines' and was initially set to CurrentDb when in the FormOpen event of ModelWarranty. When I deleted the code that hid the toolbar and stopped the close button working, I inadvertently took out the line that set that variable too.
I'm not used to creating cut down versions of databases (as you have seen) and in my rush to make this database smaller, I removed a load of things that are causing errors which don't exist in my full version.
Thanks for the help but I don't want anyone else wasting time on this, bad enough I'm stuck with it.
OK, wrt db or CurrentDb; I found what I guess you think should be a public declaration. Also found several local (to the form or whatever) declarations for CurrentDb so I guess they're present because the Global one doesn't always work? I'd make them local. Then again, when I went to Advance view here I see you've commented on that.
As for the problem - doesn't occur in this latest copy. Did you test it first and if so, did you get the issue? Back in post 16 I suggested the db issue might be a connection to itself. I see that you are making ODBC connections but I can't tell for sure to what. I'm guessing you're not opening a workspace (or similar action) that involves the db you've already opened, but if you are I'd like to be educated as to why. It's not something I've ever thought wise and it would explain why Access thinks someone else has the db open.
As for time spent - this is how I/we learn. If one person cannot devote the time needed, we tend to do so collectively. By posting my comments I hope to provide useful information to those who might be more successful. In the meantime, let us know if any connection is to the db you're already working in.
EDIT - if it is your desire to avoid further questions from us, NP. Just say so and mark the thread as solved. That should prevent others from selecting it from the list with the intention of responding.
As for the problem - doesn't occur in this latest copy. Did you test it first and if so, did you get the issue? Back in post 16 I suggested the db issue might be a connection to itself. I see that you are making ODBC connections but I can't tell for sure to what. I'm guessing you're not opening a workspace (or similar action) that involves the db you've already opened, but if you are I'd like to be educated as to why. It's not something I've ever thought wise and it would explain why Access thinks someone else has the db open.
As for time spent - this is how I/we learn. If one person cannot devote the time needed, we tend to do so collectively. By posting my comments I hope to provide useful information to those who might be more successful. In the meantime, let us know if any connection is to the db you're already working in.
EDIT - if it is your desire to avoid further questions from us, NP. Just say so and mark the thread as solved. That should prevent others from selecting it from the list with the intention of responding.
The reason for the multiple 'set' locations was for testing, by my predecessor. If he wanted to only open a particular form and test it, thereby bypassing ModelWarranty, the variable db wouldn't get set. By putting it at other points in the code, he got past this. I'll change the code to make them all local.
Yes I did and yes it did
It still happens now, as I test it.
The ODBC code is for making connections to a handful of tables that get used on other forms. Since the problem I was asking about doesn't touch those forms, I deleted the links to the tables when I created the cut down version. I just forgot to take out the code.
No other database accesses this one. The back end is shared by many front ends, not just this one, as the users do a lot of different work on it. In this case, however, the tables involved are not being shared by anyone else.
I will answer questions for as long as people's patience lasts. I'm looking at it myself, obviously, and if I find a solution I'll write it out as clearly as I can and close the thread.
Thanks again.
Just to be clear - the message is telling you that the database you cannot edit is the one you posted and not the other one(s) you connect to? Also clarification on what I wrote - I was asking if the db you posted (or really the original) was connecting to itself, not being connected to from elsewhere. As mentioned, I have only seen that once before and it was the reason for the same problem. But what you just wrote is good info.
What is the lock file telling you re who has it open - just you?
I will review your instructions again and see if I'm missing anything.
What is the lock file telling you re who has it open - just you?
I will review your instructions again and see if I'm missing anything.
Yes. The problem is reproducable in the standalone version I uploaded. It's on my C drive. No other users or databases are involved.
No connections are involved. In the live version, there is a separate front and back end but the problem is the same in this standalone version, which is entirely self contained.
Yes, one entry: my PC ID.
isladogs
MVP / VIP
- Local time
- Today, 23:58
- Joined
- Jan 14, 2017
- Messages
- 18,209
I haven't followed this thread closely but just downloaded the attachment in post #29 and followed your instructions exactly.
Like Micron I got (and fixed) error 91 caused by not defining db
No other error occurs for me on that form.
As a side issue, you do have a lot of VBA references which could cause issues on another workstation. Do you really need them all?
At the very least, I recommend you replace all the version specific references to Word/Excel/Outlook/PowerPoint with the use of late binding.
Good luck
P.S If you really don't want to spend any more time on this I can close the thread for you. Let me know if so.
EDIT:
1. You also have 2 'phantom objects ~TMPCLP.... listed in the VBE. These were objects deleted prior to database crashes.
To remove these, see this thread: https://www.access-programmers.co.uk/forums/showthread.php?t=293579
2. I recognise the name (yours?) displayed in one of the forms. Did you previously post under a different user name?
Like Micron I got (and fixed) error 91 caused by not defining db
No other error occurs for me on that form.
As a side issue, you do have a lot of VBA references which could cause issues on another workstation. Do you really need them all?
At the very least, I recommend you replace all the version specific references to Word/Excel/Outlook/PowerPoint with the use of late binding.
Good luck
P.S If you really don't want to spend any more time on this I can close the thread for you. Let me know if so.
EDIT:
1. You also have 2 'phantom objects ~TMPCLP.... listed in the VBE. These were objects deleted prior to database crashes.
To remove these, see this thread: https://www.access-programmers.co.uk/forums/showthread.php?t=293579
2. I recognise the name (yours?) displayed in one of the forms. Did you previously post under a different user name?
Last edited:
Thanks, will do.
If so, it would have been many years back. I started using an alias after I got some abusive private messages.
I'll take you up on this, I think. I need to keep on it, but no need to keep it open on here. I will delete the attachments from the posts.
- Status
