Solved

Unlocking Tables not used by anyone while other users are using the DB

Posted on 2011-02-16
15
1,005 Views
Last Modified: 2012-05-11
I have a DB used by 20 users at any given time.  There is an administrative routine that drop and creates tables.  Users have no access to these tables.

When users are in the DB though I get the following message:

"You tried to lock table '900 Available Hours Prep 1a' while opening it, but the table cannot be locked because it is currently in use. Wait a moment, and then try the operation again"


I tried with the following code but I get the same message:

             DoCmd.DeleteObject acTable, "900 Available Hours Prep 1a"
0
Comment
Question by:fitaliano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
15 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 34912548
It's time you learned - I don't talk to people who put spaces in names, bye.
0
 
LVL 85
ID: 34912674
Yes, PLEASE adopt proper nameing conventions. This will end up biting you at some point.

Access can lock several tables for a single operation, depending on the relationships between those tables. It's entirely possible that your drop/recreate routines are impacting this, and locking those tables.

Is there a reason you must drop the table? In most cases it's better to delete the information and then just refill the table. This would almost certainly stop your error message as well.

And please - do something about your object names. There's just no reason to name a table like that, and if you EVER have to move up to SQL Server you'll find that those names aren't allowed.
0
 

Author Comment

by:fitaliano
ID: 34912693
I changed the name to 900_Available_Hours_Prep_1a"

Any suggestion to solve my issue now?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 85
ID: 34938381
I'll restate my question to you:

Is there a reason you must drop the table? In most cases it's better to delete the information and then just refill the table. This would almost certainly stop your error message as well.

0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34940855
You can try to delete .ldb file in the same directory as the mdb.

In case the file is locked on the system level, you can try utilities described at this webpage: http://www.howtohaven.com/system/deleteundeletablefiles.shtml. But it's all quite unstable, and may result in data loss and in damage to the data in the database, so it would be better to come up with less intrusive solution.

By the way, there's absolutely nothing wrong in having spaces in table names, except the little trouble of enclosing these names in quotes whenever they are used. They are also supported in SQL Server (source: http://msdn.microsoft.com/en-us/library/ms175874.aspx )
0
 

Author Comment

by:fitaliano
ID: 34943066
the reason why i drop the table is because I have complex calculations that I'm staging putting data in some tables and doing other queries on these staging tables up to some other tables and so on...

0
 
LVL 44

Expert Comment

by:GRayL
ID: 34943635
vadimrapp1:  Google - Access naming convention - the first three hits say do not use spaces - one even says do not use underscores - I'm a believer.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34943781
GrayL, author's thread is not the best place for discussions between experts; if you want, we can open a private thread to discuss your beliefs.
0
 
LVL 85
ID: 34943797
<By the way, there's absolutely nothing wrong in having spaces in table names, except the little trouble of enclosing these names in quotes whenever they are used. They are also supported in SQL Server (source: http://msdn.microsoft.com/en-us/library/ms175874.aspx ) >

The document you cite specifically states this in the "Rules for Regular Identifiers" section:

"4. Embedded spaces or special characters are not allowed."

And further:

"Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement. For example: "

So you CAN use them - they're just not allowed, as I stated in my earlier comment. You must take special care to delimit them correctly in order to use them in your code, just as you do when working with non-standard object names in Access.

0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34947269
<the reason why i drop the table is because I have complex calculations that I'm staging putting data in some tables and doing other queries on these staging tables up to some other tables and so on...>

Understood, however if you're simply using SELECT INTO for your make table stuff, you can easily convert that to INSERT INTO and avoid the possibility of locking tables while dropping them. Granted you could still have lock issues when doing the INSERTS, but those would be more rare with a single user (i.e. you) doing the INSERTS.

If you could explain a bit more about your process perhaps we could assist further.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34951427
Now that the main question is resolved - regarding the spaces in table names: what the document says is the identifiers can be regular and delimited. There's nothing that says that delimited are somehow inferior to the regular, or that either of them are recommended over another, not to mention "aren't allowed".

Also, if I may, I think it's more productive and courteous to the question author to first resolve the problem he posted, and only by that to earn in his eyes the authority to give general advise criticizing his coding style - even when that advise is 100% right on target.
0
 
LVL 85
ID: 34951625
"4. Embedded spaces or special characters are not allowed."

I don't see how that is ambiguous or vague in any way. I realize that the article does go further to say  that if you decide to use characters or conventions which are disallowed, then you must take special steps to insure they can be parsed correctly.

Also, I'd be willing to bet that if you asked this question to the experienced developer community at large, you'd find a very solid (if not overwhelming) majority of those developers would come down on the side of "no spaces in object names", regardless of what environment we're talking about (i.e. db development, app developement, etc). This may change at some point, of course, but I don't see it changing in the near future.

And, as _alias99 said in your Community Support request, this might be more appropriate to continue in the Experts Lounge or the Access Discussion thread.

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

724 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