How to get the columns of a linked odbc table in MS Access through SQL Query

Question

I have linked a couple of tables in MS Access database from SQL Server and also from Excel. Now I want to query them from a Java application using jdbc. But when I run the query SELECT * FROM sys.MSysObjects Where Type = 4; I am able to see the list of those tables, but I also want to list the columns for those tables. I have queried Information_schema.Columns, UCA_METADATA.Columns, Information_schema.System_Columns etc.. and lot more system tables of MS Access but did not succeed.

Any help to achieve the above is highly appreciated.

Thanks

Answer 1

If you use sys_extended_properties, a note: it only contains fields with a description filled in SQL.

If you don't want to do this work you might have to use Left Join.

For the folowing MS-Access Query example below we must link these system SQL tables: sys_tables, sys_extended_properties, sys_columns, sys_types, INFORMATION_SCHEMA_COLUMNS.

SELECT sysC.name AS [Column], 
       [sysTyp]![name] & " (" & [sysC]![max_length] & ")" AS Type, 
       sysC.is_hidden AS Hidden, 
       sysEP.value AS Description,  
       sysC.is_nullable AS [Null], 
       ISC.COLUMN_DEFAULT AS [Default], 
       sysC.is_identity AS [Identity], 
       ISC.TABLE_CATALOG AS Db, 
       ISC.TABLE_SCHEMA AS Owner, 
       sysT.name AS [Table], sysEP.major_id, sysEP.minor_id 
FROM (((sys_extended_properties AS sysEP 
RIGHT JOIN sys_columns AS sysC 
   ON (sysEP.major_id = sysC.object_id) AND (sysEP.minor_id = sysC.column_id)) 
INNER JOIN sys_tables AS sysT 
   ON sysC.object_id = sysT.object_id) 
INNER JOIN sys_types AS sysTyp 
   ON sysC.system_type_id = sysTyp.system_type_id) 
INNER JOIN INFORMATION_SCHEMA_COLUMNS AS ISC 
   ON (sysC.name = ISC.COLUMN_NAME) AND (sysT.name = ISC.TABLE_NAME)
WHERE (((ISC.TABLE_CATALOG)=[YourDatabaseName]) AND ((ISC.TABLE_SCHEMA)="dbo"))
ORDER BY sysEP.major_id, sysEP.minor_id;