ADOX.Catalog 1

[baddos]

Hello Everyone,

How do I get the ADOX.Catalog object to list the columns in a table based up how they are layed out in the db? Right now, it sorts them by name.


Here is what I am doing right now:

Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\thomas\thomas.mdb"
Set objRS = Server.CreateObject("ADODB.Recordset&quot
objRS.ActiveConnection = objConn
objRS.CursorType = adOpenForwardOnly
objRS.CursorLocation = adUseServer
objRS.LockType = adLockReadOnly
Set objCatalog = Server.CreateObject("ADOX.Catalog&quot
objCatalog.ActiveConnection = objConn
Set objTable = objCatalog.Tables("addr&quot
For Each strColumn In objTable.Columns
objRS.Source = "SELECT "& strColumn.Name &" FROM addr;"
objRS.Open
Response.Write strColumn.Name & VBCrLF
objRS.Close
Next
Set objTable = Nothing
Set objRS = Nothing
Set objCatalog = Nothing
objConn.Close
Set objConn = Nothing

 

[jebenson]

You could try instead of using the Columns collection of the Table object, just select the field names into a recordset. Since you already know the table you want (addr), you could just do this:

Dim rsFields As Recordset
Dim FieldName as String

Set rsFields = New Recordset

'This will return 0 records, but you will get the fields
rsFields.Open "Select * from addr WHERE 1=0", objConn

For x = 0 to rsFields.Fields.Count - 1
FieldName = rsFields.Fields(x).Name
objRS.Source = "SELECT "& FieldName &" FROM addr;"
objRS.Open
Response.Write strColumn.Name & VBCrLF
objRS.Close
Next x

This will give you the fields in the order they are laid out in the table.

Hope this helps. Good luck.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[jebenson]

Oops, this line:

Response.Write strColumn.Name & VBCrLF

should of course be:

Response.Write FieldName & VBCrLF

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[baddos]

Thanks for the help!

This is what I ended up doing.

Star for you.

<% Dim strProperty, objConn, objRS, x, FieldName
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\somedb.mdb&quot;
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.ActiveConnection = objConn
objRS.CursorType = adOpenForwardOnly
objRS.CursorLocation = adUseServer
objRS.LockType = adLockReadOnly
objRS.Source = &quot;Select * from addr WHERE 1=0&quot;
objRS.Open
For x = 0 To objRS.Fields.Count - 1
FieldName = objRS.Fields(x).Name
Response.Write FieldName & VBCrLF
Next
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing %>