UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> " Is Not A Valid Name., Access 2007    
 
   
NineIron
post Oct 12 2018, 05:37 AM
Post#1



Posts: 2,134
Joined: 18-September 03
From: Massachusetts


I have a database that I inherited from someone who left the company. I'm able to open some queries in design mode but, not others. The db has several linked tables and I have refreshed the links. Any thoughts?
Go to the top of the page
 
JonSmith
post Oct 12 2018, 05:53 AM
Post#2



Posts: 3,989
Joined: 19-October 10



My thought is that it has dead queries in it. The queries once were linked to stuff that has since been deleted and are now useless.
Its not uncommon for developers to not clean up after themselves and leave orphaned objects like this. When I inherit a database or Excel with VBA the first thing I do is add Option Explicit to every VBA module, fix all the mistakes and then clean out all the dead code.

Objects such as Queries are harder but you can use 'Object Dependencies' makes it much easier.
If you look at the SQL view of the query you'll be able to see the tables or queries it tries to read from if you still are unsure.

JS
Go to the top of the page
 
NineIron
post Oct 12 2018, 06:36 AM
Post#3



Posts: 2,134
Joined: 18-September 03
From: Massachusetts


I tried to open up the SQL but, the Design icon is grayed out. Plus, this was an active db so, I don't think he would have deleted any objects or links.
Go to the top of the page
 
JonSmith
post Oct 12 2018, 06:41 AM
Post#4



Posts: 3,989
Joined: 19-October 10



Is it an .accde?

QUOTE
Plus, this was an active db so, I don't think he would have deleted any objects or links.

Of course he could. When he was developing maybe he made a bunch of rubbish and left it in even though it wasn't used. This then gets pushed into the production copy. It does no harm with day to day processing and just lurks there for years. The issue is just how it makes it much more difficult for humans to work with.
Go to the top of the page
 
NineIron
post Oct 12 2018, 06:46 AM
Post#5



Posts: 2,134
Joined: 18-September 03
From: Massachusetts


It's an accdb. Any other thoughts besides deleted links? I really don't think he would have "cleaned things up". There are tables named "000" and "00000". Also, a couple tables with _14 at the end of the name, as in 2014.
Go to the top of the page
 
DanielPineault
post Oct 12 2018, 06:47 AM
Post#6


UtterAccess VIP
Posts: 6,276
Joined: 30-June 11



What is the file extension?

Is the database split? Are you working off of your own dedicated copy of the front-end. If it is shared, are you certain you have exclusive access when you are doing development work?

What about trying the undocumented SaveAsText?

If is it a select query, does it run/work if you execute it?



--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
JonSmith
post Oct 12 2018, 06:49 AM
Post#7



Posts: 3,989
Joined: 19-October 10



I'm maybe not explaining clearly. Thats exactly my point, he did not clean up. He did delete a few objects that weren't needed and that caused these queries to break, so its kind of a partial cleanup that ends up leaving more of a mess to sort out.

If its an .accdb there is no reason for the queries to be locked. Have you ensured the bypass key isn't enabled or anything? Can you share a copy?
Go to the top of the page
 
isladogs
post Oct 12 2018, 07:18 AM
Post#8



Posts: 801
Joined: 4-June 18
From: Somerset, UK


As Jon's already stated IF its an ACCDE (rather than an ACCDB), you won't be able to change design of forms etc
Similarly if you are running it using Access runtime or have opened it as read only or have opened a second copy of the db whilst the first is still open.

If none of those are true, use the Object Dependencies feature in Database Tools.
Click on a query then Object Dependencies.
Choose 'Objects that Depend on Me' or 'Objects that I depend on'

You should see something like this with one or more items

Attached File  Capture.PNG ( 6.02K )Number of downloads: 0


However if a required object has been deleted, you will see something like this

Attached File  Capture2.PNG ( 4.51K )Number of downloads: 0


If you have a large number of objects to check, consider purchasing an addin like MZ Tools which will spped up the task considerably


--------------------
Go to the top of the page
 
JonSmith
post Oct 12 2018, 07:34 AM
Post#9



Posts: 3,989
Joined: 19-October 10



QUOTE
If you have a large number of objects to check, consider purchasing an addin like MZ Tools which will spped up the task considerably


Can MzTools check object dependencies on queries and forms? If so thats great and I didn't know!
Go to the top of the page
 
NineIron
post Oct 12 2018, 07:47 AM
Post#10



Posts: 2,134
Joined: 18-September 03
From: Massachusetts


See attached. I got this error while trying to view dependencies of one of the queries.
Attached File(s)
Attached File  ObjectDependencies.zip ( 17.61K )Number of downloads: 6
 
Go to the top of the page
 
JonSmith
post Oct 12 2018, 07:49 AM
Post#11



Posts: 3,989
Joined: 19-October 10



All indications show the file is ready only. Have you tried editing it from your desktop. Maybe you only have read permissions over the network?
Go to the top of the page
 
NineIron
post Oct 12 2018, 08:15 AM
Post#12



Posts: 2,134
Joined: 18-September 03
From: Massachusetts


I have administrative rights over the whole network.
Go to the top of the page
 
GroverParkGeorge
post Oct 12 2018, 08:19 AM
Post#13


UA Admin
Posts: 34,142
Joined: 20-June 02
From: Newcastle, WA


Where is this accdb located?

In a shared folder?

On your Computer?

Or?

That matters to troubleshooting it.

Look in the same folder where the accdb is located. Is there also a second file, with same name and the extension .laccdb? That's the locking file which Access uses to track which users have the accdb open. If it's there, it means the accdb is open, and it may be you.

If so, open and immediately close the accdb. Does that release and close the .laccdb? If not, "someone else" has it open "somewhere". You may have to track them down.

Sometimes, in extreme situations, I've had to call on IT to either delete the locking file or reboot the server where the accdb is stored. They don't like to do either, so only consider it as a last ditch step.

In the absence of the locking file, you may be opening the accdb in read only. That can actually happen a couple of different ways. One is to copy it to a removable media--a thumb drive--and back to a desktop, where it is designated as read-only. Attached File  readonly.jpg ( 73.63K )Number of downloads: 0

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
isladogs
post Oct 12 2018, 08:25 AM
Post#14



Posts: 801
Joined: 4-June 18
From: Somerset, UK


Yes I agree that the most likely cause is that is is read only.
Try opening a table and see if you can make a simple change to a field.
If not, you should go through the last few posts and check all the reasons that have been suggested for RO status.

One you have solved that, continue checking object dependencies as follows
It will need to switch on the Name AutoCorrect feature so I would do this:

1. Make a BACKUP - possibly TWO just in case
2. Hold the SHIFT key down to open the file with any startup code bypassed
3. Do NOT open any objects
4. Single click the first table to select it & run object dependencies - it will take a few minutes the first time whilst it checks dependency info
Check if anything depends on it - if not you can probably delete it
5. Repeat for ALL database objects tables, queries, forms, reports doing both versions of the dependency check - look for items where objects are marked MISSING

NOTE: the tool isn't perfect - UNION & crosstab queries aren't included so need to be checked manually. Also VBA code and macros aren't checked

Work through VERY carefully - if unsure whether to delete an object, switch off name autocorrect temporarily and prefix the name with e.g. a ~
You can easily reverse the change if necessary or delete later if no problems occur

When done, BACKUP again then carefully check all items work correctly

Many people dislike Name AutoCorrect as it has been known to cause corruption.
So others will recommend switching it off at the end of the check - I may have been lucky but its never caused me issues

Good luck
This post has been edited by isladogs: Oct 12 2018, 08:29 AM

--------------------
Go to the top of the page
 
isladogs
post Oct 12 2018, 08:37 AM
Post#15



Posts: 801
Joined: 4-June 18
From: Somerset, UK


Jon
QUOTE
Can MzTools check object dependencies on queries and forms? If so thats great and I didn't know!


I'll have to recheck MZTools features but Access itself can do that.
I know Total Access Analyser can do so. You could also use the deep search feature in V-Tools in conjunction with other tools

--------------------
Go to the top of the page
 
NineIron
post Oct 12 2018, 01:17 PM
Post#16



Posts: 2,134
Joined: 18-September 03
From: Massachusetts


I keep getting an error. See screen shot that I attached on an earlier thread. I'm giving up and turning to SQL. Thanx for all your help.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th December 2018 - 08:57 PM