Describe table problem

[willjones]

This should be a really simple thing to do, but it isn't working.

I want to determine all the column names in a table or at least all column names with a primary key. I attempted to do this OpenRecordset("desc " & tablename & ";", dbOpenSnapShot) but I get an error. (My tablename is a linked table to an Oracle table.) I'm using Oracle 10g. When I open TOAD and type desc tablename; I get a pop-up window describing the table instead of a record set with table names in it.

I thought about just saying select * from tablename; and then for each field in rs.Fields reading the field.Name. This worked great, until I came across an empty table. Nothing is returned from select * in that case! So then I thought well I could insert a row just before the query and then delete it again, but I can't even do this, because I don't know what the primary keys are on the table!

Basically if I could just do a desc or describe on the table in code, I would be fine, but I have not been able to figure out how to do this.

Please help...

Thanks.

[tehNellie]

sub ColumnName() 
  Dim db As DAO.Database, td As DAO.TableDef, fld As DAO.Field
  Dim strTblname as String

  strTblname = Inputbox( "Enter Table Name")
  Set db = currentdb()
  Set td = db.TableDefs(strTblname)

  For Each fld In td.Fields
    msgbox fld.name
  Next fld
  
  Set td =  Nothing
  Set db = Nothing
End sub

I think should do it to return all column names in a given table

[willjones]

Thanks for that info... TableDefs works great for when you have the table defined or linked to in Access, but my tables aren't always guaranteed to be defined or even linked to in my Access mdb. When I attempted to use TableDefs on my external ODBC connection I got a run-time error '3251' "Operation is not supported for this type of object." I'm still not sure how you would do it if you just have a database connection to an external database and want to execute an OpenRecordSet query on it to return the description (or DESCRIBE) of a table. I discovered that DESC is not a standard SQL command and can't be relied upon from looking at this thread (http://root.cern.ch/root/roottalk/roottalk01/1558.html).

Fortunately for me, I finally realized that in my specific case I didn't really have to know all the columns in the table if I knew the table was empty. (This might not always be true in every case though.) So I was able to just check if the table was empty by doing a select count(*) from table; and if 0 then skip over. If it was not empty then I parsed through the result set looking at each Field.Name to determine the column names. So, really I was able to side step my problem. (If anyone else thinks of any further info regarding this though, go ahead and post it in case someone else has a problem like this in the future.) Thanks again for your input.

[tehNellie]

Your other option is to use the ODBC connection to query the Oracle Database directly. You can then use DESC and return the results to a recordset for manipulation etc.