Lazarus

Programming => Databases => Topic started by: SymbolicFrank on March 20, 2023, 12:24:48 pm

Title: MDB: list all tables
Post by: SymbolicFrank on March 20, 2023, 12:24:48 pm

According to the documentation, that should be done like this:

SELECT MSysObjects.name
FROM MSysObjects
WHERE
   MSysObjects.TYPE IN (1,4,6)
   AND MSysObjects.name NOT LIKE '~*'   
   AND MSysObjects.name NOT LIKE 'MSys*'
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][+]

MyConnection.GetTableNames(tn);

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

How do I find out if something is a table?

Title: Re: MDB: list all tables
Post by: Chris78 on March 20, 2023, 12:44:10 pm

Does "MDB" mean access database ?

Title: Re: MDB: list all tables
Post by: SymbolicFrank on March 20, 2023, 01:00:12 pm

Ah, sorry. Yes, it does.

Title: Re: MDB: list all tables
Post by: Zvoni 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

Title: Re: MDB: list all tables
Post by: af0815 on March 20, 2023, 07:21:57 pm

Quote from: Zvoni on March 20, 2023, 04:13:07 pm

Since i'm avoiding Access like the devil avoids holy water, ...

+1
You are not alone :-)

Title: Re: MDB: list all tables
Post by: SymbolicFrank on March 20, 2023, 09:23:25 pm

Quote from: Zvoni 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

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.