HELP PLEASE! I just want Column Names from ODBC

[Robert Paresi]

Hello,

Microsoft Excel uses Microsoft Access engine. I am connecting to Microsoft
Excel via ODBC. I would like to retrieve the column names like I can with
Sybase SQL Anywhere, for example. Is there not a way through ODBC/SQL
Statement to return the column names?

Thank you.

 

[Douglas J Steele]

The only way I'm aware of is to open a recordset (you can get one with zero
rows in it), and loop through the recordset's Fields collection, looking at
the Name property for each field.

There is no system table that contains Field names, if that's what you're
hoping for.

 

[Robert Paresi]

Hello,

I am trying to do that with VB and it seems to not work as expected.

It keeps complaining.

Run-Time Error. Microsoft ODBC Excel Driver. Cannot update. Database
or object is read-only.

Here is my test code:

Private Sub Form_Load()


Set cn = CreateObject("ADODB.Connection")
cn.open ("Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;DBQ='c:\clarion6\apps\roommaster\rooming
list.xls';ReadOnly=1")

Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")

cmd.ActiveConnection = cn

cmd.CommandText = "SELECT * from SHEET1"

rs.open cmd, , 0, 1

 

[peregenem]

Microsoft Excel uses Microsoft Access engine. I am connecting to Microsoft
Excel via ODBC. I would like to retrieve the column names like I can with
Sybase SQL Anywhere, for example.

You can use ADO's OpenSchema method to get schema metadata, including
column names. However, you must use the OLE DB provider rather than
odbc e.g.

Sub test()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
.CursorLocation = 3
.Open
End With

Dim rs As Object
Set rs = con.OpenSchema(4, Array(Empty, Empty, "Sheet1$", Empty))
With rs
Dim i As Long
For i = 0 To .RecordCount - 1
Debug.Print .Fields("COLUMN_NAME").Value
.MoveNext
Next
End With

End Sub

 

[Robert Paresi]

Interesting that it works.

More interesting that it returns the column names in alphabetical order.
This doesn't help as I need to know which column in the Excel spreadsheet is
in what column. What makes it be returned in alphabetical order? Returning
the names alphabetical doesn't help.

-Robert

 

[Douglas J Steele]

Try

Debug.Print .Fields("COLUMN_NAME").Value & _
" (Pos " & .Fields("ORDINAL_POSITION").Value & ")"

ORDINAL_POSITION starts at 1.

 

[Dirk Goldgar]

Hello,

I am trying to do that with VB and it seems to not work as expected.

It keeps complaining.

Run-Time Error. Microsoft ODBC Excel Driver. Cannot update.
Database or object is read-only.

Here is my test code:

Private Sub Form_Load()


Set cn = CreateObject("ADODB.Connection")
cn.open ("Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;DBQ='c:\clarion6\apps\roommaster\rooming
list.xls';ReadOnly=1")

Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")

cmd.ActiveConnection = cn

cmd.CommandText = "SELECT * from SHEET1"

rs.open cmd, , 0, 1

This works for me:


Dim cn As Object
Dim cmd As Object
Dim rs As Object
Dim fld As Object

Set cn = CreateObject("ADODB.Connection")

cn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=c:\Temp\Temp.xls;" & _
"DefaultDir=c:\Temp"

Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")

cmd.ActiveConnection = cn

cmd.CommandText = "SELECT * from [Sheet1$]"

rs.Open cmd, , 0, 1

For Each fld In rs.Fields
Debug.Print fld.Name
Next fld

rs.Close
cn.Close

 

[Robert Paresi]

COOL!

Thanks

Try

Debug.Print .Fields("COLUMN_NAME").Value & _
" (Pos " & .Fields("ORDINAL_POSITION").Value & ")"

ORDINAL_POSITION starts at 1.

 

[peregenem]

rs.Sort = "ORDINAL_POSITION"