1

I have an Access Database with hundreds of ODBC Linked Tables to a couple of backend SQL Servers. Some of these Linked Tables are to SQL Tables and some are to SQL Views. Is there any way to determine, using VBA, which is to a SQL Table and which is to a SQL View ?

Thanks.

Andre
  • 26,751
  • 7
  • 36
  • 80
Mark Fairhead
  • 21
  • 2
  • That's a good question that I have [wondered about](https://stackoverflow.com/a/32316883/3820271) myself. – Andre May 28 '20 at 11:08

1 Answers1

2

You can query that information from SQL server.

SELECT TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA + '.' + TABLE_NAME = ?

Implement it in VBA:

Dim db As DAO.Database
Set db = CurrentDb
Dim td As DAO.TableDef
Set td = db.TableDefs("SomeLinkedTable")
Dim qd As DAO.QueryDef
Set qd = db.CreateQueryDef("")
qd.Connect = td.Connect
qd.SQL = "SELECT TABLE_TYPE " & _
    "FROM INFORMATION_SCHEMA.TABLES " & _
    "WHERE TABLE_SCHEMA + '.' + TABLE_NAME = '" & Replace(td.SourceTableName, "'", "''") & "'"
Dim rs As DAO.Recordset
Set rs = qd.OpenRecordset
Debug.Print rs!TABLE_TYPE.Value 'Returns VIEW or BASE TABLE depending on the type
Erik A
  • 31,639
  • 12
  • 42
  • 67