All Tables are displaying as System Tables (1 Viewer)

[Thales750]

None of the tables can be accessed from a front end, in fact they do not show up as tables when trying to link.

 

[theDBguy]

Who created the tables or the front end?

 

[Thales750]

I did. This system has been running for 6 years. Most of it has been migrated to SQl Server.

The tables in the existing backend are all listed as System Tables and grayed out. Super weird.

 

[Thales750]

I copied them to a new database, same issue. I downloaded it from the client to my system which is running on 2016 same issues.

 

[Isaac]

The only tables that will show in the dialogue that results from trying to link an Access table, while using the FE, are the Access tables in the back end. Not [any kind of] linked tables.
So what I mean is, if you have created linked (SQL Server) tables in the back-end, and you open the FE and try to use the Link tables dialogue to see what tables in the BE you can link to, you won't see any of those linked tables there.
They should simply be linked directly from the FE to SQL Server.

 

[Thales750]

The only tables that will show in the dialogue that results from trying to link an Access table, while using the FE, are the Access tables in the back end. Not [any kind of] linked tables.

I'm not trying to link to linked tables.

In the backend there are no linked table. The Navigational Display will not show any tables unless the "Show System Tables" is checked.

Then it shows all the tables, they can be opened locally, but not linked. If you copy and past them to a new database, the same condition applies.

 

[theDBguy]

I copied them to a new database, same issue. I downloaded it from the client to my system which is running on 2016 same issues.

Have you tried using a different computer? Just curious...

 

[theDBguy]

I'm not trying to link to linked tables.

In the backend there are no linked table. The Navigational Display will not show any tables unless the "Show System Tables" is checked.

Then it shows all the tables, they can be opened locally, but not linked. If you copy and past them to a new database, the same condition applies.

View attachment 85343

Those don't look like they are linked tables. Were they linked tables before? If you change the data in one of those tables, do you see the change in SQL Server?

 

[Isaac]

Ok, then I was confused. I thought you said mostly was migrated to SQL server, so I assumed linked tables were involved.
Not sure what's going on, all I can think of is maybe someone having hidden tables or created custom groups.

 

[Thales750]

The client did a restore from last night.
It works.

 

[theDBguy]

The client did a restore from last night.
It works.

Good job!

 

[Thales750]

Those don't look like they are linked tables. Were they linked tables before? If you change the data in one of those tables, do you see the change in SQL Server?

No that is the actual "backend". In the backend the tables are displayed as System Tables

 

[Thales750]

Good job!

Thanks, it still leaves a huge mystery.

 

[theDBguy]

No that is the actual "backend". In the backend the tables are displayed as System Tables

Okay, now I'm confused. I thought the backend was SQL Server. The only way I know how to convert/change a local table into a system table is through code. Do you know anyone using your db who could do that?

 

[Thales750]

Okay, now I'm confused. I thought the backend was SQL Server. The only way I know how to convert/change a local table into a system table is through code. Do you know anyone using your db who could do that?

I do not. I will make a new one with a few non-proprietary tables if you want to see it.

 

[Thales750]

I just got the ok to finish the migration. They made me stop after the virus sent their entire work home. The have a hundred or so people that were remoting in and were out of IT resources.

But this last fiasco has re prioritized the SQL Migration. This split up, half migrated, system has been a real nightmare.

 

[isladogs]

The only tables that will show in the dialogue that results from trying to link an Access table, while using the FE, are the Access tables in the back end. Not [any kind of] linked tables.
So what I mean is, if you have created linked (SQL Server) tables in the back-end, and you open the FE and try to use the Link tables dialogue to see what tables in the BE you can link to, you won't see any of those linked tables there.
They should simply be linked directly from the FE to SQL Server.

I think this comment is somewhat misleading.
If you try to link to tables in a separate app / BE, you will indeed only see the local tables not those linked to the BE.
However if you try to import tables, you will see a list of all local & linked tables whether those are Access/SQL/Excel or whatever.
Of course if you 'import' linked tables from another database, these remain as linked tables in the FE

 

[isladogs]

Do any of the tables show if you tick show hidden objects but not Show System Objects?

I do not. I will make a new one with a few non-proprietary tables if you want to see it.

I have seen something like this a long time ago but can't remember what caused it or how I resolved it at the time
It would be interesting to see the properties of these tables.

Create the following query on the MSysObjects table. It should list all tables other than true system tables:

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects
WHERE ((Not ((MSysObjects.Name) Like "MSys*" Or (MSysObjects.Name) Like "f_*")) AND ((MSysObjects.Type)=1) AND ((MSysObjects.Flags)<>0));

I would be interested to know the Flags value for all your tables wrongly 'flagged' (sorry!) as system tables.
NOTE: If any have Flags=8, they are just hidden tables NOT system tables

EDIT One further issue.
The third table in your screenshot is a genuine system table beginning f_... followed by a long string and it probably ends _Data. That table should NEVER be visible in the nav pane and indicates further problems with your BE

 

[Isaac]

I think this comment is somewhat misleading.

How is it misleading?
While you are correct in the distinction between what shows in Link vs. Import dialogue, the original post was concerning Linking. And, my comment specifically stated Linking too.

 

[isladogs]

How is it misleading?
While you are correct in the distinction between what shows in Link vs. Import dialogue, the original post was concerning Linking. And, my comment specifically stated Linking too.

I felt it could give the impression to anyone reading this topic that users cannot link tables that are linked in another database.