Bellone
asked on
Can't close Access
I hope the following will strike a chord with someone, as it is certainly puzzling me. I have Access 2010 (64-bit) running on a recent Dell server, configured as a terminal server under Server 2008 R2.
The main Access application acts as the front-end with links to tables and views on a sizeable MySQL backend. There are only about half-a-dozen users at the moment, all, like me, accessing the application via RDP, and all with their own copy of the front-end. So far, when this error has occurred I have been the only user connected (as TS Admin)
I have several other Access dbs which carry out certain processes required by the front-end at weekly intervals, one of which is giving the problem. When I run a UDF in one of the modules, which refers to a table or query (not necessarily the same one each time) the system says it can't find it, notwithstanding the table or query is definitely there, and can be opened and its contents viewed.
My inclination at that point is to stop the offending procedure, close down the module, and compact-and-repair the database. I am then advised that this cannot be done as 'another instance of the database is open'. (This despite the fact that I am the only user on the server, and have only opened this db once.) I therefore tell it to close the db, which it can't. Using 'Close' or clicking the x at the top right, either causes the current instance of Access to be refreshed, or causes a new instance to open (I'm not sure which). Anyway, it just keeps on opening Access until I reach for Task Manager and kill it. What is more, I can't kill it by closing the running application - I have to delete the process, which does close it. Incidentally, Task Manager only shows one instance of Access running.
I have been developing in Access for quite a few years and am reasonably confident the code is OK. Apart from anything else, it has been running perfectly well for weeks. Have also tried the following:
Logged off and on again
Stopped and restarted MySQL
Rebooted the server
Copied the contents of the offending database to a new version and binned the original
Rebuilt the linked MySQL views
Removed Office 2010 and loaded a completely new version
None of which made a bit of difference. If anyone has ideas, I should be very glad to hear them...
The main Access application acts as the front-end with links to tables and views on a sizeable MySQL backend. There are only about half-a-dozen users at the moment, all, like me, accessing the application via RDP, and all with their own copy of the front-end. So far, when this error has occurred I have been the only user connected (as TS Admin)
I have several other Access dbs which carry out certain processes required by the front-end at weekly intervals, one of which is giving the problem. When I run a UDF in one of the modules, which refers to a table or query (not necessarily the same one each time) the system says it can't find it, notwithstanding the table or query is definitely there, and can be opened and its contents viewed.
My inclination at that point is to stop the offending procedure, close down the module, and compact-and-repair the database. I am then advised that this cannot be done as 'another instance of the database is open'. (This despite the fact that I am the only user on the server, and have only opened this db once.) I therefore tell it to close the db, which it can't. Using 'Close' or clicking the x at the top right, either causes the current instance of Access to be refreshed, or causes a new instance to open (I'm not sure which). Anyway, it just keeps on opening Access until I reach for Task Manager and kill it. What is more, I can't kill it by closing the running application - I have to delete the process, which does close it. Incidentally, Task Manager only shows one instance of Access running.
I have been developing in Access for quite a few years and am reasonably confident the code is OK. Apart from anything else, it has been running perfectly well for weeks. Have also tried the following:
Logged off and on again
Stopped and restarted MySQL
Rebooted the server
Copied the contents of the offending database to a new version and binned the original
Rebuilt the linked MySQL views
Removed Office 2010 and loaded a completely new version
None of which made a bit of difference. If anyone has ideas, I should be very glad to hear them...
ASKER
Yes, tried that. Worked once, then same problem, except that it was a different linked table it said it couldn't find. Did you get the same 'couldn't close Access' phenomenon?
Did you try compacting and repairing after copying?
ASKER
Not straight away after opening, so I have just tried it again - created a new db, copied in the contents of the old one, and immediately compacted and repaired. This worked, and also allowed me to exit from Access without the previous problem. Unfortunately, that is the end of the good news. When I tried to run the relevant UDF, the same problem occurred as previously - it wouldn't recognize the linked tables, even though they exist and can be opened.
<<I have several other Access dbs which carry out certain processes required by the front-end at weekly intervals, one of which is giving the problem. When I run a UDF in one of the modules, which refers to a table or query (not necessarily the same one each time) the system says it can't find it, notwithstanding the table or query is definitely there, and can be opened and its contents viewed. >>
Under terminal services, you need to be mindful of references, especially if your using a library MDA.
While each user has their own copy of the FE, references are a hard coded path, and all will be point to C:\<something>
So one thing that can bite you there is security. If users don't have privs for a directory, you'll see all kinds of errors.
<<I am then advised that this cannot be done as 'another instance of the database is open'. (This despite the fact that I am the only user on the server, and have only opened this db once.) I therefore tell it to close the db, which it can't. Using 'Close' or clicking the x at the top right, either causes the current instance of Access to be refreshed, or causes a new instance to open (I'm not sure which). >>
I've seen some weird random errors both under Windows 2003 server and Windows 2008 R2 Server; "out of memory", "administrator has place in a state", etc and nothing is ever wrong.
Kill the app, re-execute, and it takes off fine. I'm not sure if it's Access or the OS, or a combination of both. I do know that Access has had terminate bugs for a number of years (running in task scheduler often shows a memory access violation at shutdown) and of course we all know that it's terrible with garbage clean-up.
So with that in mind, there are two things I found which usually help:
1. Close all objects you open in code and set object variables to nothing
2. Use Docmd.Quit rather then Application.Quit
Jim.
Under terminal services, you need to be mindful of references, especially if your using a library MDA.
While each user has their own copy of the FE, references are a hard coded path, and all will be point to C:\<something>
So one thing that can bite you there is security. If users don't have privs for a directory, you'll see all kinds of errors.
<<I am then advised that this cannot be done as 'another instance of the database is open'. (This despite the fact that I am the only user on the server, and have only opened this db once.) I therefore tell it to close the db, which it can't. Using 'Close' or clicking the x at the top right, either causes the current instance of Access to be refreshed, or causes a new instance to open (I'm not sure which). >>
I've seen some weird random errors both under Windows 2003 server and Windows 2008 R2 Server; "out of memory", "administrator has place in a state", etc and nothing is ever wrong.
Kill the app, re-execute, and it takes off fine. I'm not sure if it's Access or the OS, or a combination of both. I do know that Access has had terminate bugs for a number of years (running in task scheduler often shows a memory access violation at shutdown) and of course we all know that it's terrible with garbage clean-up.
So with that in mind, there are two things I found which usually help:
1. Close all objects you open in code and set object variables to nothing
2. Use Docmd.Quit rather then Application.Quit
Jim.
ASKER
Just a thought - the table it is failing to recognise now is linked in from an admin db, which was created in Access 2003 and is a 32-bit mdb, rather than a 64-bit accdb. I'm going to try converting that db to Access 2010, and see if it makes a difference.
ASKER
Hi Jim, thanks for your input. I have taken aboard you remarks about refs - the thing is, this is happening to me, the administrator, who has access to everything!, and I am the only one who will run this weekly-update procedure. Also, I am always careful to set object variables to nothing at the end of a procedure, and do use DoCmd.Quit in the close routine.
I have attached a graphic of the References dialog, showing what is selected
The only recent change, apart from today's reload of Office 2010, is that I have recently installed Peters Shrinker Stretcher on this server. As far as I know, it doesn't use references.
2014-04-02-18-01-29.jpg
I have attached a graphic of the References dialog, showing what is selected
The only recent change, apart from today's reload of Office 2010, is that I have recently installed Peters Shrinker Stretcher on this server. As far as I know, it doesn't use references.
2014-04-02-18-01-29.jpg
I think I would as a starting point with this:
1. Uncheck each of the references and compile. If you get an error re-check the reference (some are required, but I'm wondering about the last couple).
2. Do a /decompile. Start Access using the /decompile switch, open the db. Then close the db and access, re-open and do a compile.
Jim.
1. Uncheck each of the references and compile. If you get an error re-check the reference (some are required, but I'm wondering about the last couple).
2. Do a /decompile. Start Access using the /decompile switch, open the db. Then close the db and access, re-open and do a compile.
Jim.
ASKER
OK, will do and I'll let you know. What I have determined whilst fiddling with the wretched thing is that this 'can't close Access' problem only occurs when the UDF has encountered a 'can't find linked table' error. What I CAN do at this point is open another, error-free, database, which can then be closed in the normal way, without Access reopening. Don't know whether this is relevant, but at least it allows me to get out of Access without resorting to the Task Manager. And it still doesn't answer why the linked tables are not being recognised.
Are the FE and BE on the same computer? If they are not, perhaps you are experiencing network "blips". Access is very sensitive to being disconnected from it's BE and rarely recovers gracefully. This may happen more frequently to you because you are the developer and will have objects open that now can't be saved because the FE and BE became disconnected.
ASKER
FE and BE are on the same computer, which is configured as a terminal server.
Still getting the 'can't close' syndrome, but when I was getting a bit desperate over the weekend, with a load of stats to process, I tried logging in with different credentials. (I created this second account a few months ago because, occasionally, when re-connecting with a dual-monitor configuration, a black screen comes up. I use the second account to connect to the terminal server and log-off my main admin account, which then allows me to revert to the admin account.) Interestingly, when I logged-in using the second account, the problem did not occur and I was able to use Access in the normal way for several hours. Don't know what to make of this - perhaps it rings a bell with someone?
Jim - I am now going to try your two suggestions and will report back.
Bellone
Still getting the 'can't close' syndrome, but when I was getting a bit desperate over the weekend, with a load of stats to process, I tried logging in with different credentials. (I created this second account a few months ago because, occasionally, when re-connecting with a dual-monitor configuration, a black screen comes up. I use the second account to connect to the terminal server and log-off my main admin account, which then allows me to revert to the admin account.) Interestingly, when I logged-in using the second account, the problem did not occur and I was able to use Access in the normal way for several hours. Don't know what to make of this - perhaps it rings a bell with someone?
Jim - I am now going to try your two suggestions and will report back.
Bellone
ASKER
Progress, of a sort. I still can't run the UDF, but at least I can close the database.
In the UDF, when it reaches the line 'Set db = DBEngine(0)(0)' I now get the message 'Error 3045 Couldn't use <filename>; file already in use'. I have copied the offending db twice and now rewritten the function manually, with the same result in all three. Have even inserted 'Set db = nothing' before 'Set db = DBEngine(0)(0)', as well as at the end of the module, in case it is stuck open from a previous failure.
Google offers lots of comments re this error, and MS seems to indicate that it may be due to the system being unable to create a lock file because I don't have the necessary permission.
Having regard to my previous comment, about being able to run the UDF with a different log-in, I am wondering whether my Admin login may have become corrupt. Is this likely? I can run half a dozen other Access applications in the same folder, including accdb, accde, accdr and mdb, with no difficulty.
In the UDF, when it reaches the line 'Set db = DBEngine(0)(0)' I now get the message 'Error 3045 Couldn't use <filename>; file already in use'. I have copied the offending db twice and now rewritten the function manually, with the same result in all three. Have even inserted 'Set db = nothing' before 'Set db = DBEngine(0)(0)', as well as at the end of the module, in case it is stuck open from a previous failure.
Google offers lots of comments re this error, and MS seems to indicate that it may be due to the system being unable to create a lock file because I don't have the necessary permission.
Having regard to my previous comment, about being able to run the UDF with a different log-in, I am wondering whether my Admin login may have become corrupt. Is this likely? I can run half a dozen other Access applications in the same folder, including accdb, accde, accdr and mdb, with no difficulty.
<< I now get the message 'Error 3045 Couldn't use <filename>; file already in use'. >>
I think this is the thing to focus on and it seems like you have an instance opening under exclusive mode for some reason.
Let's see if we can't simplify things a bit.
1. Check if compact and close is set. If so, remove it. Make sure you do this for the FE's and the BE's
2. Make sure the Auto Correct options are turned off under Access options.
3. Verify any anti-virus scanning is set to exclude *.MDB, *.ACCDE, etc files.
Now see if you bump into the problem again. If you do, I'll need a better understanding of what's where and what is running when and how (automated job or interactive).
Jim.
I think this is the thing to focus on and it seems like you have an instance opening under exclusive mode for some reason.
Let's see if we can't simplify things a bit.
1. Check if compact and close is set. If so, remove it. Make sure you do this for the FE's and the BE's
2. Make sure the Auto Correct options are turned off under Access options.
3. Verify any anti-virus scanning is set to exclude *.MDB, *.ACCDE, etc files.
Now see if you bump into the problem again. If you do, I'll need a better understanding of what's where and what is running when and how (automated job or interactive).
Jim.
ASKER
Jim
When working through your suggestions, I remembered that I had changed the db which is giving all the problems to 'exclusive' to try and prevent the multiple instances of Access from occurring. Which worked. The trouble is that, as soon as I switched it back to 'shared' I was back with the original problem of not being able to close Access!
I have gone to a lot of trouble to completely re-write the UDF using different logic to achieve the same result, but every time I try it, I now get a message saying it can't find a certain table. The table is there, and spelt correctly. I have tried deleting and re-attaching it, and even, out of curiosity, deleting the link and importing it, but it is still not being recognised, even when it is physically in the db.
And still this other anomaly - if I run this procedure having logged in with my other credentials, it works perfectly. No 'can't find table', no opening Access 50 times. Got to be something to do with my log-in, don't you think?
When working through your suggestions, I remembered that I had changed the db which is giving all the problems to 'exclusive' to try and prevent the multiple instances of Access from occurring. Which worked. The trouble is that, as soon as I switched it back to 'shared' I was back with the original problem of not being able to close Access!
I have gone to a lot of trouble to completely re-write the UDF using different logic to achieve the same result, but every time I try it, I now get a message saying it can't find a certain table. The table is there, and spelt correctly. I have tried deleting and re-attaching it, and even, out of curiosity, deleting the link and importing it, but it is still not being recognised, even when it is physically in the db.
And still this other anomaly - if I run this procedure having logged in with my other credentials, it works perfectly. No 'can't find table', no opening Access 50 times. Got to be something to do with my log-in, don't you think?
<<I have gone to a lot of trouble to completely re-write the UDF using different logic to achieve the same result, but every time I try it, I now get a message saying it can't find a certain table. The table is there, and spelt correctly. >>
Odd problem. When you get the error, jump to the debug window (ctrl/G), then type:
? CurrentDB().Tabledefs("<na
and hit return. You should get the name of the table back. If you do, use Ctrl/F9 to reset the execution point back to the line that gave the error, then F8 to execute again.
If you don't, then look at the tableDefs() collection to see what's there.
One way or another, you should be able to pin this down a bit more.
Jim.
Odd problem. When you get the error, jump to the debug window (ctrl/G), then type:
? CurrentDB().Tabledefs("<na
and hit return. You should get the name of the table back. If you do, use Ctrl/F9 to reset the execution point back to the line that gave the error, then F8 to execute again.
If you don't, then look at the tableDefs() collection to see what's there.
One way or another, you should be able to pin this down a bit more.
Jim.
Try changing
DBEngine(0)(0)
To
CurrentDB()
When you get the error about fill already in use (I was getting it pretty regularly for a while also but it never stopped me from closing), open Task Manager and see if there is a second instance of Access running.
I assume the data base compiles.
I ran into an extremely strange series of errors recently. I was using tables created by someone else and I hadn't paid attention to the column names. One of the names was "err" and I had to use it in a form. The problem that arose was that Access thought (as I finally discovered) that err referred to the error object even when I used Me.Err or Me.[Err] to refer to it. I ended up having to rename the table column to stop the strange errors.
DBEngine(0)(0)
To
CurrentDB()
When you get the error about fill already in use (I was getting it pretty regularly for a while also but it never stopped me from closing), open Task Manager and see if there is a second instance of Access running.
I assume the data base compiles.
I ran into an extremely strange series of errors recently. I was using tables created by someone else and I hadn't paid attention to the column names. One of the names was "err" and I had to use it in a form. The problem that arose was that Access thought (as I finally discovered) that err referred to the error object even when I used Me.Err or Me.[Err] to refer to it. I ended up having to rename the table column to stop the strange errors.
ASKER
Jim - tried that, and I did get the name of the table back, as you predicted. Tried to execute again, but got the same message: "Run-time error '3076'. The Microsoft Access database engine cannot find the input table or query 'tblLicences'. Make sure it exists and that its name is spelled correctly."
In order to run this test, I had to comment out the error-trapping line, and immediately got a message telling me I didn't have exclusive access to the database (even though I was the only user on the system). It didn't stop me making the change temporarily, but wouldn't save it.
The only thing that is different about tblLicences is that it is linked-in from an Access 2003 db, whereas the current db is 64-bit Access 2010. However, this is the table where, as I mentioned earlier, I tried deleting the link, and physically importing, and VBA still didn't recognise it!
In order to run this test, I had to comment out the error-trapping line, and immediately got a message telling me I didn't have exclusive access to the database (even though I was the only user on the system). It didn't stop me making the change temporarily, but wouldn't save it.
The only thing that is different about tblLicences is that it is linked-in from an Access 2003 db, whereas the current db is 64-bit Access 2010. However, this is the table where, as I mentioned earlier, I tried deleting the link, and physically importing, and VBA still didn't recognise it!
ASKER
PatHarman - I tried using CurrentDB instead of DBEngine early in this sorry saga, but no joy. Also checked Task Manager and can confirm that there is only ever one instance of Access open. What seems to be happening is that when I close an app, another, empty, instance of Access immediately opens. The table which is giving most of the trouble does have a field called Status in it, which is referred to, but as far as I know this is not a reserved word.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim
It seems the answer to this conundrum lay in the 5th para of your remarks above - a corrupt log-in. Since this morning, when, finally, we bit the bullet and deleted the original admin log-in, creating a new one with the same name, but different password, the problem seems to have gone away - at least, so far.
Many thanks for all the thought you did on this, over several weeks.
Best regards
Bellone
It seems the answer to this conundrum lay in the 5th para of your remarks above - a corrupt log-in. Since this morning, when, finally, we bit the bullet and deleted the original admin log-in, creating a new one with the same name, but different password, the problem seems to have gone away - at least, so far.
Many thanks for all the thought you did on this, over several weeks.
Best regards
Bellone
I am not sure it will help you but it helped me.