Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Toronto, Ontario
    Posts
    6

    Unanswered: Column Information

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: Column Information

    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.

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Column Information

    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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Sep 2003
    Location
    Toronto, Ontario
    Posts
    6
    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.

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I never heart of Phyton, is this your manner to work with ODBC, not ADO of DAO?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Sep 2003
    Location
    Toronto, Ontario
    Posts
    6
    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.

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •