Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

HELP PLEASE! I just want Column Names from ODBC

Discussion in 'Information Technology' started by Robert Paresi, Nov 8, 2005.

  1. Robert Paresi

    Robert Paresi
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    General Help! Looking for a good clairvoyant/card reader......any suggestions? Please Blogs Jan 25, 2016
    Please help Questions and Answers Nov 1, 2013
    Please Help! What is the Name of the Narrator/Raagi? Gurbani Download Oct 16, 2012
    Muslim Girl-Sikh Boy Marriage Problems- Please Help! Love & Marriage Oct 12, 2012
    Help Please :/ Relationships Apr 19, 2011

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Robert Paresi" <FirstInitialLastName@innquest.com> wrote in message
    news:estSDW64FHA.3976@TK2MSFTNGP15.phx.gbl...
    > 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.
    >
    >
     
  4. Robert Paresi

    Robert Paresi
    Expand Collapse
    Guest

    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
     
  5. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Re: HELP PLEASE! I just want Column Names from ODBC

    Robert Paresi wrote:
    > 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
     
  6. Robert Paresi

    Robert Paresi
    Expand Collapse
    Guest

    Re: HELP PLEASE! I just want Column Names from ODBC

    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


    <peregenem@jetemail.net> wrote in message
    news:1131378856.186353.302270@g44g2000cwa.googlegroups.com...
    >
    > Robert Paresi wrote:
    >> 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
    >
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Re: HELP PLEASE! I just want Column Names from ODBC

    Try

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

    ORDINAL_POSITION starts at 1.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Robert Paresi" <FirstInitialLastName@innquest.com> wrote in message
    news:%23Y8zPF84FHA.1140@tk2msftngp13.phx.gbl...
    > 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
    >
    >
    > <peregenem@jetemail.net> wrote in message
    > news:1131378856.186353.302270@g44g2000cwa.googlegroups.com...
    > >
    > > Robert Paresi wrote:
    > >> 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
    > >

    >
    >
     
  8. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Robert Paresi" <FirstInitialLastName@innquest.com> wrote in message
    news:ep45dE74FHA.1956@TK2MSFTNGP09.phx.gbl
    > 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

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  9. Robert Paresi

    Robert Paresi
    Expand Collapse
    Guest

    Re: HELP PLEASE! I just want Column Names from ODBC

    COOL!

    Thanks

    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:OeLjOR84FHA.2552@TK2MSFTNGP10.phx.gbl...
    > Try
    >
    > Debug.Print .Fields("COLUMN_NAME").Value & _
    > " (Pos " & .Fields("ORDINAL_POSITION").Value & ")"
    >
    > ORDINAL_POSITION starts at 1.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Robert Paresi" <FirstInitialLastName@innquest.com> wrote in message
    > news:%23Y8zPF84FHA.1140@tk2msftngp13.phx.gbl...
    >> 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
    >>
    >>
    >> <peregenem@jetemail.net> wrote in message
    >> news:1131378856.186353.302270@g44g2000cwa.googlegroups.com...
    >> >
    >> > Robert Paresi wrote:
    >> >> 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
    >> >

    >>
    >>

    >
    >
     
  10. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Re: HELP PLEASE! I just want Column Names from ODBC

    rs.Sort = "ORDINAL_POSITION"
     

Share This Page