Recent

Author Topic: MDB: list all tables  (Read 839 times)

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
MDB: list all tables
« on: March 20, 2023, 12:24:48 pm »
According to the documentation, that should be done like this:

Code: SQL  [Select][+][-]
  1. SELECT MSysObjects.name
  2. FROM MSysObjects
  3. WHERE
  4.    MSysObjects.TYPE IN (1,4,6)
  5.    AND MSysObjects.name NOT LIKE '~*'  
  6.    AND MSysObjects.name NOT LIKE 'MSys*'
  7. ORDER BY MSysObjects.name

But, unfortunately, there is no way to grant Admin access to MSysObjects (GRANT is an invalid query).

The next one does work:

Code: Pascal  [Select][+][-]
  1. MyConnection.GetTableNames(tn);

But that also includes all the other objects, like queries.

How do I find out if something is a table?


Chris78

  • Newbie
  • Posts: 3
Re: MDB: list all tables
« Reply #1 on: March 20, 2023, 12:44:10 pm »
Does "MDB" mean access database ?

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: MDB: list all tables
« Reply #2 on: March 20, 2023, 01:00:12 pm »
Ah, sorry. Yes, it does.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: MDB: list all tables
« Reply #3 on: March 20, 2023, 04:13:07 pm »
2 ways:
1) GetTableNames returns a TStrings (TStringList). Run through it and remove everything else (hope the naming-schema is clear enough to differentiate)
2) Implement your own "INFORMATION_SCHEMA" -->  create your own Table, where you enter only the Tablenames you're interested in

Since i'm avoiding Access like the devil avoids holy water, i can't give you more info
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

af0815

  • Hero Member
  • *****
  • Posts: 1288
Re: MDB: list all tables
« Reply #4 on: March 20, 2023, 07:21:57 pm »
Since i'm avoiding Access like the devil avoids holy water, ...
+1
You are not alone :-)
regards
Andreas

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: MDB: list all tables
« Reply #5 on: March 20, 2023, 09:23:25 pm »
2 ways:
1) GetTableNames returns a TStrings (TStringList). Run through it and remove everything else (hope the naming-schema is clear enough to differentiate)
2) Implement your own "INFORMATION_SCHEMA" -->  create your own Table, where you enter only the Tablenames you're interested in

Since i'm avoiding Access like the devil avoids holy water, i can't give you more info
The one and only thing I (try to) do with them is move the data (tables) to a real SQL database, for the real processing to take place. But the MDB is what I get delivered, so I have to take what I get.

It does work well in Linux (although it tends to bug out every once a while, on Windows as well), but you get no error messages whatsoever, so development has to happen in Windows.

 

TinyPortal © 2005-2018