[Solved] Unable to see underlying table name in a base view

[cminarik]

I created a base view and added a couple of column names from a table.

When I go back to Edit the view, the column names show up, but the underlying table name does not.

How do I see the name of the underlying table in a base view?

TIA.

Chuck

[UnklDonald418]

Because Views are stored in the underlying database instead of the Base front end, it is a good idea to retain a copy of the query used to create the View.

If you have an Embedded Base database, right click on the View and select Edit in SQL View to see the query.

If you have a JDBC connection run the following query to display the view definition(s)

Code: Select all

 Select
       INFORMATION_SCHEMA.VIEWS.TABLE_NAME, 
       INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION
    From INFORMATION_SCHEMA.VIEWS

[cminarik]

Thanks for your reply. I guess I should have provided more information.

I am trying to open an MS Access database...(*.accdb). The views are Access queries.

I tried to connect as "Microsoft Access 2007" and ODBC.

There is no option for "Edit in SQL view"...and when I try to run your query, I get the following error message:

Could not find file 'C:\Program Files (x86)\OpenOffice 4\program\INFORMATION_SCHEMA.mdb'.

Can I not see the underlying tables for an MS Access db?

TIA...Chuck

[UnklDonald418]

Base is a relatively small program that can be used as a basic front end for numerous database engines but it lacks many of the bells and whistles of large commercial front ends like MSAccess
Using Base with an Access connection you can see the tables, their fields and data but Base doesn't know anything about the query behind a View. A View has the appearance of a table to Base which is why the Editor can only see the list of fields.
It comes as no surprise that Microsoft uses a different method to display the Schema, so you will probably need to get the view definition using Access.
https://learn.microsoft.com/en-us/sql/r ... rver-ver16

[cminarik]

Thanks...Chuck