• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4171
  • Last Modified:

Unable to see tables in an Access .mdb file

I've recently been assigned the task of converting a number of old Access applications, 2003, 2007, to Office Access 2010.  There are dozens of them.  When I hold the "shift" key down and open the database window, some object categories are missing... I can't see them.  I know they're there because in some cases I can open the forms and "see" the underlying queries and tables but I can't get at them.  Is there a way that I can "unhide" these objects?  My suspicion is that these files have been compiled and some of the objects simply aren't updateable anymore.  I've worked with Access for some time but I've never run accross this type of setup before.  Thanks much in advance for your guidance.
0
rdbrunet
Asked:
rdbrunet
  • 18
  • 13
  • 2
  • +3
6 Solutions
 
MINDSUPERBCommented:
Access Option -> Current Database -> Navigation -> Navigation Option

Sincerely,
Ed
0
 
Rey Obrero (Capricorn1)Commented:
Tools > options  > view tab

place a check on Hidden objects then click oK
0
 
MINDSUPERBCommented:
See attached image:


Ed

display-options.jpg
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Helen FeddemaCommented:
Instead of converting, try making a brand-new database in Access 2010 and importing the old tables (and other objects) into it.  See the attached document for some tips on upgrading old databases to Access 2007 or 2010.
Upgrading-to-Access-2007-or-2010.doc
0
 
rdbrunetAuthor Commented:
Yea.  That's the first thing I did.  "Show Hidden Objects" is checked and all Groups for "Object Type" are selected.  This is weird.
0
 
Rey Obrero (Capricorn1)Commented:
can you copy an image of the database object window (a2003), navigation pane ( A2007) and upload here so we can see what you have.
0
 
rdbrunetAuthor Commented:
I tried doing the import of objects from 2007 to 2010 as well.  When I go through the import process, the tables and queries, etc. I expect to see are not visible either?  I've done this a thousand times and never run into this.  These .mdb's are standalone, I've copied them intoto from a production drive to my local drive, there are no links to external data that I can locate.
0
 
rdbrunetAuthor Commented:
This is the tab view that I'm seeing.  I am unable to view or configure so that I can see "Tables" or "Queries"
Access-Database-Window.JPG
0
 
Rey Obrero (Capricorn1)Commented:
can you upload a db..
0
 
rdbrunetAuthor Commented:
I can not upload the database... proprietary information.  Has anyone encountered this before?  I know when you create an .mde or .ade, Access restricts the ability to modify certain objects but this is an .mdb file.
0
 
rdbrunetAuthor Commented:
Nothing!!! Please don't give up.
0
 
GRayLCommented:
What do you see when you click the Down Arrow on [All Access Objects]?
0
 
rdbrunetAuthor Commented:
Just what you see on the screen shot I posted earlier.  Its like the tables and queries are not even there.  Yet when I open a form in Design View, I can see the query and underlying tables in the Properties window.
0
 
Rey Obrero (Capricorn1)Commented:
place this codes in a regular module


Sub UnHideAllTables(Optional blHide As Boolean = False)

Dim db As DAO.Database
Dim tbl As DAO.TableDef
 
Set db = CurrentDb
For Each tbl In db.TableDefs
    Debug.Print tbl.Name
    'Hide or Unhide tables
    If blHide = False Then
    tbl.Attributes = 0
    Else
    tbl.Attributes = 1
    End If
Next
Set tbl = Nothing
db.Close
Set db = Nothing
Application.RefreshDatabaseWindow
End Sub


in the Immediate window type

call unHideAllTables(0)

and hit enter


0
 
rdbrunetAuthor Commented:
I noticed something earlier that may, or may not, have something to do with the price of beans...

WHen I tried to add a table to an underlying form query, I received a dialog box that said "Record is deleted".  I did a Google search on this Access error and the general response was that the database may be corrupted.  Notwithstanding the fact that for the users, the application is working fine, there seems to be no other indication of malfunction.  Could this possible corruption be causing me not to see the tables/queries in the Access database window?
0
 
Jeffrey CoachmanCommented:
Back up the database

Run the compact/repair utility
Compile the code
Run the compact/repair utility, ...again

Did you try Helen's suggestion of creating a new DB and importing the objects...?
0
 
rdbrunetAuthor Commented:
Yes!  That was the first thing I tried.  Compact & Repair does nothing either.  Can anyone recommend a good Access Doctor?
0
 
Rey Obrero (Capricorn1)Commented:
it is better if you respond to all posted comments.
0
 
rdbrunetAuthor Commented:
Capricorn1 I created a module based on the code you suggested... it did nothing.  Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
looking at the image you posted at http:#a37032473 

right click on All Access Objects > Navigation Options

select Object types from Categories
place a check on Tables on Groups for Object types

click Ok
0
 
Rey Obrero (Capricorn1)Commented:
you may need to check also Show Hidden Objects at Display Options
0
 
rdbrunetAuthor Commented:
This has been covered already.  All of the objects are checked for visibility.  Its like the application does not even recognize these objects as part of the file.
0
 
Rey Obrero (Capricorn1)Commented:
rdbrunet,

did you try my last suggestion ?
0
 
Jeffrey CoachmanCommented:
I you are quite sure that the DB is corrupt and in need of a more aggressive "Repair" utility, you can see here:
http://jamiessoftware.tk/articles/accesscorruption.html

Just note that all "Legit" services of this kind will require near irrefutable proof that you are the rightful owner of this data...

0
 
Rey Obrero (Capricorn1)Commented:
rdbrunet,

did you try my last suggestions ?

can you capture the Navigations Options window with Object Type selected and post here
0
 
rdbrunetAuthor Commented:
Capricorn1: Yes I've tried all suggested solutions.  The Navigation window is attached.
Access-Database-Navigation-Optio.JPG
0
 
Rey Obrero (Capricorn1)Commented:
ok.. I can't help anymore without seeing the db.. sorry
0
 
rdbrunetAuthor Commented:
Thanks for trying.
0
 
Helen FeddemaCommented:
I have encountered disappearing tables with compatibility problems between Access 2007 and 2010 .accdb files (and even two people's versions of 2010).  The actual phenomenon is that, on initially opening the database, the tables are briefly visible (a fraction of a second), then disappear and can't be seen thereafter.  After closing and reopening the database, you may get an "unrecognized database format" error message.  My usual solution is to create a new 2007 database, import all objects into it (this usually works), and then continue working on the database in 2007 (I have a VM for this purpose).  There is never a problem with another person opening that database in 2010.  See my Access Archon article on this topic:

http://www.helenfeddema.com/Files/accarch205.zip
0
 
Rey Obrero (Capricorn1)Commented:
< These .mdb's are standalone> http:#a37032429 
0
 
rdbrunetAuthor Commented:
Yes...  I think that they've been running a single Access file with multiple users for too long and the thing just took a dump.
0
 
rdbrunetAuthor Commented:
An update to this question.  Another developer/user in our group has opened several of the Access databases in question and was able to see the Tables/Queries that I couldn't see.  I'm thinking now there's some sort of "permissions" issue here that I'm not aware of.  Probably somewhere in the startup code.
0
 
rdbrunetAuthor Commented:
Yesterday I was given permissions to a SQL Server and voila, when I opened up my troubled applications, the Tables and Queries tabs magically appeared.  Yet I can see no indication that these tables are "linked" to a SQL Server database... Things that make you go Hmmmmm?
0
 
Rey Obrero (Capricorn1)Commented:
if you had followed the comment at http:#a37032476 ,

this problem is long gone.... :-(

0
 
rdbrunetAuthor Commented:
Thanks Capricorn1 but I am not at liberty to upload anything from my worksite.  Any thoughts on why this database is configured this way?  How can I reverse engineer this so we can access the data directly.  I suspect the data is either on a SQL Server RDBMS or that, since the application was set up as an Access project file, that the data is stored in a lite-SQL / Access database.
0
 
Rey Obrero (Capricorn1)Commented:
so it look like this is an ADP not an MDB or ACCDB..
sorry...
better create a new .MDB or .ACCDB and create linked table from your SQL server database.
0
 
rdbrunetAuthor Commented:
Although I appreciate the efforts of all who contributed, all of the responses addressed the symptoms and not the actual problem.  I haven't been able to fully resolve this issue other than to presume that the anomaly is related to the applications being compiled in a Microsoft Access project file after the tables were ODBC linked to a SQL server RDBMS.  Somehow this process protects tables and queries from future modifications.  This is both good and bad.  The downside is that my current employers have lost all of the orignal project files associated with the applications in questions so we're kind of stuck with what we have.  Probably a good argument for not "locking down" applications that don't have a specific security reason for doing so.  Thanks again for all of the feedback.
0
 
GRayLCommented:
rdbrunet:  Am I the only answerer who got scored a 'C' for this effort?  If not, you would do us all a favor by closing the question as 'Unanswered' rather than scoring a 'C' and then adding that no one answered the question. Just my 2¢.  If you can't tell, I absolutely detest 'C's'.
0
 
rdbrunetAuthor Commented:
GrayL:

My apologies. It was not my intent to degrade your assistance or tarnish your service record.  WIthout guys like you and the other responders to this message, guys like me would be at a total loss.  It's my ignorance to how the grading system works that caused this, and not the quality or usefulness  your respone or feedback.  Thanks for your assistance.  I look forward to your guidance in the future.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 18
  • 13
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now