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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Hidden But Not Hidden Table, Access 2013    
 
   
mchapa89
post Jan 14 2020, 10:00 AM
Post#1



Posts: 11
Joined: 14-January 20



I have a table I am looking to connect to Excel, but it's not pulling up as an available table.

When I go into our back-end database, the table "tblInvoice" appears hidden. However, in the Navigations Options it has Show Hidden Objects selected. Also, in the properties, the hidden checkbox is grayed out so I can't uncheck it.

Any guidance as to how to unhide this table would be greatly appreciated smile.gif Thank you.
This post has been edited by mchapa89: Jan 14 2020, 10:00 AM
Attached File(s)
Attached File  HiddenTable.JPG ( 39.33K )Number of downloads: 7
 
Go to the top of the page
 
theDBguy
post Jan 14 2020, 10:42 AM
Post#2


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

My first question is, who hid this table? Can they unhide it for you? The other thought I had was, could you create a query based on this table and try to link Excel to that query (not sure if it's possible though since I don't use Excel to connect to Access)?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jan 14 2020, 11:04 AM
Post#3


UA Admin
Posts: 37,503
Joined: 20-June 02
From: Newcastle, WA


Is the back end database, perhaps an accdE?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Jan 14 2020, 11:43 AM
Post#4


UtterAccess VIP
Posts: 2,400
Joined: 4-June 18
From: Somerset, UK


If the Hidden checkbox is disabled, the table has been saved as a system table.
There may be a very good reason for doing that.
Check before trying to restore it to a standard table.

However there is nothing to prevent system tables being exported to Excel

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
mchapa89
post Jan 14 2020, 12:19 PM
Post#5



Posts: 11
Joined: 14-January 20



Thank you--that's helpful.

Exports could do the trick but it's still not time and energy efficient. We would like to create a live report that cross feeds in Excel with our actual supply chain system. Otherwise, I would also agree to leave them as-is.

As far as security goes, the admins who enter this data only have access to the front-end databases and cannot edit the linked tables anyway.

The person who initially created these databases no longer works at our company, so also requesting access is also a bit difficult.

Thank you for your help, though. I'm going to start researching system and standard tables.
Attached File(s)
Attached File  Excel.JPG ( 91.27K )Number of downloads: 2
 
Go to the top of the page
 
isladogs
post Jan 14 2020, 12:46 PM
Post#6


UtterAccess VIP
Posts: 2,400
Joined: 4-June 18
From: Somerset, UK


You will see the same disabled hidden checkbox is you click on any system table in your FE

Whilst it is possible to remove a user defined system table property, I'm deliberately holding back from telling you how.
However, as already mentioned there is nothing to prevent you creating a query of tblInvoice and then importing that into Excel.
But it won't be a 'live feed'

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
theDBguy
post Jan 14 2020, 12:52 PM
Post#7


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Hi. I just gave it a try and was able to link a query from Access into Excel. To get the latest data, I had to Refresh it. In the Connection Property, you can set it to auto-refresh every so often.

Edit: Hmm, unless I didn't wait long enough, I just tried linking Excel directly to the table, and I still had to refresh Excel to see any updates done from Access. Is this correct?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jan 14 2020, 01:01 PM
Post#8


UtterAccess VIP
Posts: 2,400
Joined: 4-June 18
From: Somerset, UK


You can certainly do that to get the latest data if the Access dB is closed.
However, try refreshing the data in Excel when the database is open.
Also open the Excel file then try opening the source database.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
theDBguy
post Jan 14 2020, 01:05 PM
Post#9


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


I had both files open, while testing. I entered some text in Excel to see if it will show up in Access - it didn't. Then, I entered some text in Access to see if it will show up in Excel - it didn't, until I refreshed.

Right now, I just opened Excel and then I opened Access. They both opened up fine. Was something else supposed to happen? I don't do this very often, so I am not sure what I am doing here. Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jan 14 2020, 01:43 PM
Post#10


UtterAccess VIP
Posts: 2,400
Joined: 4-June 18
From: Somerset, UK


Nor me but I was getting messages in whichever file I opened second to the effect that the file was locked for editing.
Whether that was because I was testing in Office 2010 on a tablet I'm unsure.
Whatever was causing that seems to be cured and at the moment no other messages.
Will do some more testing as I'm a bit puzzled now

As you say any changes made in Access can be viewed in Excel but it doesn't work in reverse.
But that may be related to the long standing issue since 2002 or so whereby linked Excel tables cannot be directly updated in Access

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
theDBguy
post Jan 14 2020, 01:53 PM
Post#11


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Sounds good. Thanks for the clarification.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jan 14 2020, 04:16 PM
Post#12


UtterAccess VIP
Posts: 2,400
Joined: 4-June 18
From: Somerset, UK


Although I link Excel files in Access all the time, its unusual for me to do that the thing in reverse
I couldn't understand what I saw earlier so I tested this again on another workstation and got identical results to before - tested first in A2010 & then in A365

First I created a simple Access db with 4 tables:
- tblDummy (standard table)
- tblHidden (hidden table)
- tblSystem (user defined system table created using dbSystemObject)
- USysComplexColumns (copy of Access system table MSysComplexColumns)

Both of the system tables above had the Hidden checkbox disabled as previously described
I then created 2 queries qryStstem. sryUSysComplexColumns so I could link to these in Excel

I then closed the Access db, created a new Excel file and linked the first two tables and the 2 system table queries. So far no problems.

I then reopened the Access file and got the security bar with a message that the database was opened read only.
Attached File  Capture0.PNG ( 24.5K )Number of downloads: 2


I closed both programs, reopened Access followed by Excel.
On clicking Refresh All I got the message below
Attached File  Capture1.PNG ( 3.99K )Number of downloads: 2


Clicking OK led to a whole series of relink windows starting with this masterpiece:
Attached File  Capture2.PNG ( 4.31K )Number of downloads: 2


Next came a DataLink Properties window followed by a OLE DB Initialisation window
Attached File  Capture3.PNG ( 7K )Number of downloads: 0


... then various other messages after which this appeared repeatedly
Attached File  Capture4.PNG ( 2.52K )Number of downloads: 0


After closing and reopening many times, the messages seen in Excel when clicking Refresh All eventually stopped but by that time I had unintentionally broken the links!

Reopening the files in either order, led to the same messages as before.
Clearly I've created an unworkable situation by doing something incorrectly but I'm not sure how or what!
Hopefully someone can explain it as I'm still very confused.

If anyone wants to try using the 2 files I created, these are attached in the zip file












Attached File(s)
Attached File  ExcelLink.zip ( 37.81K )Number of downloads: 4
 

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
mchapa89
post Jan 15 2020, 11:40 AM
Post#13



Posts: 11
Joined: 14-January 20



Wow, this is neat! Using your example, I can pull in the two queries and the two tables (hidden, standard)--"Attach: IslaDogsEx"

In my scenario, however, all of my tables and queries must be "locked" to be like your tblSystem. Even the query I created from my tblInvoice is not pulling up in Excel. "Attach: Access"

Any advice?


Attached File(s)
Attached File  IslaDogsEx.JPG ( 143.62K )Number of downloads: 4
Attached File  Access.JPG ( 164.31K )Number of downloads: 9
 
Go to the top of the page
 
theDBguy
post Jan 15 2020, 11:48 AM
Post#14


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Hi. This is interesting. Could it be that Excel is "smart" enough to prevent potential unauthorized access to data based on if it was queried from a hidden table? Curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jan 15 2020, 12:19 PM
Post#15


UtterAccess VIP
Posts: 2,400
Joined: 4-June 18
From: Somerset, UK


@mchapa89
Are you sure you want a table like tblSystem as the table itself is read only in Access. Surprisingly the query version of it is editable!

Remember that any changes made in Excel aren't passed back to Access whether the source data is from a standard, hidden or system table.
So why does it matter what type of table it is if you are going to edit it in Excel

Out of interest did you have any of the issues I described in my last post?

Your two screenshots don't really explain to me what you are doing.
Clearly you had several problems hence the 6 deleted tables ~CLP... and 4 different error tables.
Can you upload your own file(s) and explain what to look at

@DBG
Not sure i understand your question. Suggest you try doing what I did with different types of table.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
theDBguy
post Jan 15 2020, 12:35 PM
Post#16


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (isladogs)
@DBG
Not sure i understand your question. Suggest you try doing what I did with different types of table.

Yes, I have been doing some tests since. Unfortunately, I can't duplicate either the OP or your experiences. I can still open both files at the same time without any warnings. I made a table (deep) hidden, but it still showed as an option in Excel. I made it a system table, and it did disappear as an option in Excel, but then the query it's based on is still available and shows the data. So...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jan 15 2020, 01:07 PM
Post#17


UtterAccess VIP
Posts: 2,400
Joined: 4-June 18
From: Somerset, UK


@DBG
I deliberately didn't mention deep hidden tables whether system or otherwise but I agree with those results.

I'm puzzled you aren't getting any of the issues i mentioned.
Perhaps you followed a different process. If so, likely I did it wrong!

I believe you are testing in A365.
Did you try closing and reopening in the way I described because it was only after that was done that problems occurred

Would you do me a favour and download the two files in my long post.
See what happens when you open that Excel file both with the Access file closed and then open.
Then create your own Excel file to mimic mine and after linking the tables go through the same steps I did.

Thanks

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
theDBguy
post Jan 15 2020, 01:15 PM
Post#18


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Hi Colin. You're right. I don't know why I went that far when I can clearly see the greyed out table in the OP's posted image. I just couldn't duplicate their result, so I thought maybe it's a deep hidden table - but I don't think so anymore.

Alright, I'll give your sample file a try in a bit and will let you know the results. By the way, I am using 2016 (not O365).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mchapa89
post Jan 15 2020, 02:35 PM
Post#19



Posts: 11
Joined: 14-January 20



I've attached a sample of the data I'm working with--tblInvoice is what I'm unable to pull through Excel.

Attached File(s)
Attached File  Database1.zip ( 839.71K )Number of downloads: 5
 
Go to the top of the page
 
mchapa89
post Jan 15 2020, 02:47 PM
Post#20



Posts: 11
Joined: 14-January 20



Also--no, I didn't experience any of the issues or see similar error messages to the ones from your screenshots.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 06:18 AM