Solved

Can't close Access

Posted on 2014-04-02
20
414 Views
Last Modified: 2016-11-23
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...
0
Comment
Question by:Bellone
  • 11
  • 5
  • 2
  • +1
20 Comments
 
LVL 24

Expert Comment

by:-MAS
Comment Utility
Did you try copying the database and work on the new copy?

I am not sure it will help you but it helped me.
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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?
0
 
LVL 24

Expert Comment

by:-MAS
Comment Utility
Did you try compacting  and repairing after copying?
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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.
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 5

Author Comment

by:Bellone
Comment Utility
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
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<  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.
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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("<name of table here>").Name


 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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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!
0
 
LVL 5

Author Comment

by:Bellone
Comment Utility
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.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
Re-reading back through the thread, two things jumped out:

1. It works fine under one account and not another.

2. It's under a 64 bit edition of Office.

 What I think is that you may have multiple problems, which is what makes what is going on so strange.

 When you have strange problems like this, I find it's always best to go back to basics.  In regards to #1, that's almost always security (sometimes a corrupt user profile is the culprit, but not often).

 So first, check the directory and share (if used) permissions for any directories where the DB's (FE and BE) reside.   Make sure the problem account can create, edit, and delete a text file in each of the directories.   And do that the same way the DB would be accessed (ie. through a mapped drive or share).

 Also make sure your opening the files you think your opening.   With no one in the system, open the app, then check explorer for the existence of .LDB's in the directories.   If you don't find one, then you may be opening the wrong file (often drive mapping and/or home path mapping can be the issue - especially with terminal services involved).

 The second security issue is the registry.  Often under terminal services, parts of the registry are locked down and reading/writing under some accounts is an issue.   Let's skip this for the moment because it's a devil to figure out sometimes.

 You can BTW check fairly quickly if it's a profile issue by simply giving the account that doesn't work the same privs as the account that does.  If the problem goes away, it's either security on the directories, shares, or the registry.

on #2, 64 bit.  That means your using 64 bit drivers in one instance and 32 in the others.   You mentioned mySQL; are you using a DSN as part of that?

When creating DSN's, you must be careful which ODBCad32.EXE you use.  The 32 bit version is in C:\Windows\sysWOW64.   The 64 bit version is in C:\Windows\System32.   When accessing the ODBC Admin Applet from control panel, you are using the 64 bit version.

You can read more about that here:
ODBC Administrator tool displays both the 32-bit and the 64-bit user DSNs in a 64-bit version of Windows
http://support.microsoft.com/kb/942976

Double check on this is that once linked to the table, can you open the table as a table just from the database container (no code)?   Make sure you do this under the account that doesn't work (newer ODBC entries are stored in the registry rather than an .ini file, so again this can go back to a permissions issue).

 I realize you have covered some of this already, but it would be good to go back and just double check some things.

 Things can get a bit crazy under terminal services.

Jim.
0
 
LVL 5

Author Closing Comment

by:Bellone
Comment Utility
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now