1

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
Kranthi.Gogikar
  • 31
  • 5
  • did you try with select * from Information_schema.Columns where TABLE_NAME = 'your.table.name' ? – pfigueredo Jun 28 '22 at 08:25
  • Yes @pfigueredo, that actually is not storing the ODBC linked table related columns and Information_schema.Tables is also not storing the those tables in it... – Kranthi.Gogikar Jun 28 '22 at 08:50
  • I don't understand @Kranthi, if you have a linked table in MS-Access that is a table in a SQL server, the fields or columns in the linked table are the same, that's why I suggested you use `select * from Information_schema.Columns where TABLE_NAME = 'your. table.name' ` (not `Information_schema.Tables`) – pfigueredo Jun 28 '22 at 09:24
  • @pfigueredo , the Sql server tables which I linked to MS Access are created by me only in Sql server. So I have linked them to MS Access. I can happily see the data in Access also. But when I try to query the Access database in DBeaver by connecting to .accdb file on information_schema.Columns, it is not showing my tables related columns. In the connection string also I appended ;sysSchema=true for my connection in DBeaver. – Kranthi.Gogikar Jun 28 '22 at 09:59
  • you can create in SQL a view that uses `information_schema.columns` and link it to Access and call it from the Java application – pfigueredo Jun 28 '22 at 12:19

1 Answers1

0

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;
0xD
  • 46
  • 2
  • 6
  • Hi 0xD, can you please elaborate your answer... ? I believe sys.extended_properties belongs to Sql Server but not MS Access db. So if you can elaborate your answer, it will be helpful for me to go forward. – Kranthi.Gogikar Jul 12 '22 at 09:52
  • Hello @Kranthi.Gogikar. As you've noticed from my poorly finished answer, my goal was just to comment on your question, which unfortunately I can't do just yet. I must be wrong, but it seems to me that column descriptions in Access are something contained only in the internal representation of the table, whether local or linked. Like you, I can't get the column descriptions by accessing ACCESS's internal tables. – 0xD Jul 12 '22 at 12:30
  • My solution was to use SQL tables (yes, sys.extended_properties belongs to Sql Server). In ACCESS, I look up the columns of my tables linked to SQL by the SQL system tables, noting what I wrote in that fake answer. I don't know anything about EXCEL tables. If you want I can put, in another comment, the SQL-Access query that I use. – 0xD Jul 12 '22 at 12:31
  • Sure 0xD, please post the query, it may help me. Thanks for the effort. – Kranthi.Gogikar Jul 14 '22 at 07:48
  • @Kranthi.Gogikar, please create a new MS-ACCESS query in sql view mode and try this example. – 0xD Jul 14 '22 at 13:13