how do i query index information using SQL (Microsoft Access)

[Guest]

My system manages indexes and is designed to talk (via ODBC) to various
databases (ms access, SQL server, Oracle).
In order to manage indexes, we query index information and then determine
what is missing.
This can be done with SQL Server (eg. using SQL like SELECT * from
SysIndexes, SysIndexKeys WHERE name like.....) but I cannot find a way to do
the same thing with MS access.
Does anyone know how to query MS Access for index info using SQL ?

 

[Douglas J. Steele]

My system manages indexes and is designed to talk (via ODBC) to various
databases (ms access, SQL server, Oracle).
In order to manage indexes, we query index information and then determine
what is missing.
This can be done with SQL Server (eg. using SQL like SELECT * from
SysIndexes, SysIndexKeys WHERE name like.....) but I cannot find a way to
do
the same thing with MS access.
Does anyone know how to query MS Access for index info using SQL ?

Unfortunately, you can't.

You have to use methods of either DAO or ADOX.

 

[Jamie Collins]

Unfortunately, you can't.

You have to use methods of either DAO or ADOX.

Closer is the ADODB OpenSchema approach because you get a recordset
(rather than a hierarchy of collections) e.g.

? CurrentProject.Connection.OpenSchema(adSchemaIndexes).GetString

Jamie.

--

 

[Douglas J. Steele]

Closer is the ADODB OpenSchema approach because you get a recordset
(rather than a hierarchy of collections) e.g.

? CurrentProject.Connection.OpenSchema(adSchemaIndexes).GetString

True. I keep forgetting about OpenSchema!

 

[Guest]

Thanks very much for replying. Its greatly appreciated.

However, you have used terms that I know absolutely nothing about. DAO,
ADOX, ADODB.... I'm not sure what context you guys work in - maybe .NET or
Java ?. My world is Smalltalk talking to databases via native ODBC calls. We
pretty much just pass direct SQL to the database via ODBC.

So it sounds like I can't get the index information.

Thanks for the help. Its nice to have an answer - even if its a 'no'.

 

[Brendan Reynolds]

I've never used Smalltalk, but I checked Cincom's web site (apparently
they're one of the main Smalltalk implementers) and they say their product
supports COM. DAO, ADOX and ADODB are all COM technologies, so it is
possible you may be able to call them from Smalltalk.

 

[Granny Spitz via AccessMonster.com]

Its nice to have an answer - even if its a 'no'.

Hon, the answer isn't "no," it's "you don't know how ... yet." DAO, ADOX and
ADODB are object models in the VBA programming language, which Access
developers use in Access. If these sound strange to you, you asked your
question in an Access newsgroup so the answers you get will be tailored to an
Access environment.

Most of the time SQL is good enough to communicate between databases, but
Access is a desktop database and doesn't have system tables that contain
metadata you can retrieve with SQL like you're used to.

So it sounds like I can't get the index information.

You *can* get the index information. Have you googled for code (other than
SQL)? You're not the first person in the world who wants the table indexes
in an Access database, but doesn't have Access. Chances are, people have
already posted their solutions on the web.