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


G

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 ?
 
Ad

Advertisements

D

Douglas J. Steele

mike wells sydney said:
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.
 
J

Jamie Collins

Douglas said:
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.

--
 
D

Douglas J. Steele

Jamie Collins said:
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!
 
G

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'.
 
B

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.
 
Ad

Advertisements

G

Granny Spitz via AccessMonster.com

mike said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top