Is there a way in Access 2000 that I can get column details for a specific table through an SQL query and ODBC?

For example, with DB2 I write:

select * from syscat.columns where tabname = xxxxxx

Specifically, I need to get information on which columns are part of the primary key, which columns are not allowed to have a null value and which columns have a defined default entry. I currently have a brute force method for obtaining column names and data types and sizes, but the NotNull information is inaccurate and the rest is completely absent.

Note: the SQL does not have to be standard, as long as it will work through an ODBC connection to an Access database.

You don't say how do you open the connection with the target Access database, but you should be able to get the needed kind of information using the OpenSchema method of the ADO Connection object or using ADOX objects. If the target database is Access, it is faster to use the native OLE DB provider instead of ODBC.

Nope, you can't access system tables by using SQL. Within Access, you will have to create a VBA method, using the method of mashinovodja in case you are using ADO, or browse the TableDef collection of DAO, which is very easy to do.

Thanks for the responses.

Unfortunately, I am working in Python and need to be able to connect to any database engine, not just Access. Between this requirement and compatibility issues with existing software, I am restricted to using the standard ODBC interface to avoid breaking critical programs.

I never heart of Phyton, is this your manner to work with ODBC, not ADO of DAO?

Python is a high-level programming language.

Basically, you open an ODBC connection and then just tell it to execute an SQL statement and then to fetch the results. Python also supports other interfaces, such as ADO, but trying to use them would break some of our existing applications.

So, you don't have a DBEngine either? Then you're lost ....

However, I can't believe that it would break other application, if you use ADO. I would just take the OLE DB provider for ODBC, and use the OpenSchema method to get your meta data.