Welcome to SPN

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

Sign Up Now!

How do I list results of OLEDB recordset (Oracle) in listbox?

Discussion in 'Information Technology' started by HOhrndorf, Jul 28, 2006.

  1. HOhrndorf

    HOhrndorf
    Expand Collapse
    Guest

    I am using MS Access 2002 SP3 and Oracle 9i.
    On an Access form, I have created a list box. It is no problem to
    populate it with the results of a SELECT on a local Access table, but
    when trying to do so with an OLEDB-connected rowset or table, nothing
    is displayed (no error, but also no results in the listbox).
    The connection to Oracle was established by the following means:

    con.Provider = "OraOLEDB.Oracle"
    con.ConnectionString = "Data Source=FDB11G_bu;OSAuthent=1;"
    con.Open

    strSQL = "SELECT * from FDBAPPS.prodmeld"
    rstProdmeld.Open strSQL, con, adOpenStatic, adLockReadOnly,
    adCmdText

    Navigating within the rows of rstProdmeld ist no problem (.MoveNext
    etc.), but when trying to set the Listbox' source to that recordset
    nothing happens:
    lstTest2.RowSource = "SELECT * FROM rstProdmeld;"

    Does anyone have an idea what's going wrong here? Do I need to use
    other parameters when using the ".open" method?
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News How The Left & Indian Nationalists Distorted The History Of Sikh Gadhar Movement - Breaking News Sep 21, 2016
    Interfaith Materialists And Its Discontents Interfaith Dialogues Jun 10, 2016
    Sikhi Quasi-Pragmatists And Quasi-Spiritualists, Care To Explain? Sikh Sikhi Sikhism Jun 6, 2016
    Cholesterol Is Finally Officially Removed From "naughty List" Health & Nutrition Feb 11, 2016
    Listening to Gurbani while driving?? Sikh Rehat Maryada Jan 13, 2016

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Create a pass-through query, and use that as the RowSource.

    The pass-through query will have the necessary connection information.

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


    "HOhrndorf" <henner.ohrndorf@franke.com> wrote in message
    news:1148998727.684852.162860@i39g2000cwa.googlegroups.com...
    > I am using MS Access 2002 SP3 and Oracle 9i.
    > On an Access form, I have created a list box. It is no problem to
    > populate it with the results of a SELECT on a local Access table, but
    > when trying to do so with an OLEDB-connected rowset or table, nothing
    > is displayed (no error, but also no results in the listbox).
    > The connection to Oracle was established by the following means:
    >
    > con.Provider = "OraOLEDB.Oracle"
    > con.ConnectionString = "Data Source=FDB11G_bu;OSAuthent=1;"
    > con.Open
    >
    > strSQL = "SELECT * from FDBAPPS.prodmeld"
    > rstProdmeld.Open strSQL, con, adOpenStatic, adLockReadOnly,
    > adCmdText
    >
    > Navigating within the rows of rstProdmeld ist no problem (.MoveNext
    > etc.), but when trying to set the Listbox' source to that recordset
    > nothing happens:
    > lstTest2.RowSource = "SELECT * FROM rstProdmeld;"
    >
    > Does anyone have an idea what's going wrong here? Do I need to use
    > other parameters when using the ".open" method?
    >
     
  4. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "HOhrndorf" <henner.ohrndorf@franke.com> wrote in message
    news:1148998727.684852.162860@i39g2000cwa.googlegroups.com
    > I am using MS Access 2002 SP3 and Oracle 9i.
    > On an Access form, I have created a list box. It is no problem to
    > populate it with the results of a SELECT on a local Access table, but
    > when trying to do so with an OLEDB-connected rowset or table, nothing
    > is displayed (no error, but also no results in the listbox).
    > The connection to Oracle was established by the following means:
    >
    > con.Provider = "OraOLEDB.Oracle"
    > con.ConnectionString = "Data Source=FDB11G_bu;OSAuthent=1;"
    > con.Open
    >
    > strSQL = "SELECT * from FDBAPPS.prodmeld"
    > rstProdmeld.Open strSQL, con, adOpenStatic, adLockReadOnly,
    > adCmdText
    >
    > Navigating within the rows of rstProdmeld ist no problem (.MoveNext
    > etc.), but when trying to set the Listbox' source to that recordset
    > nothing happens:
    > lstTest2.RowSource = "SELECT * FROM rstProdmeld;"
    >
    > Does anyone have an idea what's going wrong here? Do I need to use
    > other parameters when using the ".open" method?


    Setting the list box's RowSource to the same SQL string as you used to
    open your recordset is not the same as setting the list box's Recordset
    to that recordset. I've never done this, but you might try this (after
    opening the recordset):

    Set lstTest2.Recordset = rstProdmeld

    It may be necessary first to disconnect the recordset.

    Is there any reason you can't just have a linked table pointing to the
    Oracle table, and set your list box's RowSource to that linked table?

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

    (please reply to the newsgroup)
     
  5. HOhrndorf

    HOhrndorf
    Expand Collapse
    Guest

    Dirk,
    thanks for your answer. I have not tested yet what you have suggested.
    You asked why I am not using just a linked table. Well, the reason is
    that we would like to use Oracle's OS authentication and as far as I
    know this does not work with linked tables, does it?
    Henner
     
  6. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "HOhrndorf" <henner.ohrndorf@franke.com> wrote in message
    news:1149013926.595669.320150@i39g2000cwa.googlegroups.com
    > You asked why I am not using just a linked table. Well, the reason is
    > that we would like to use Oracle's OS authentication and as far as I
    > know this does not work with linked tables, does it?


    I've no idea whether it works with linked tables or not, since I haven't
    had occasion to work with Oracle data. But I don't see why it wouldn't
    so long as the connect string stored with the linked table specifies OS
    authentication.

    Alternatives could include using apass-through query, as Doug Steele
    suggested, or specifying the connect info in the SQL statement assigned
    to the list box's RowSource propery (using the IN clause).

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

    (please reply to the newsgroup)
     
  7. aaron.kempf@gmail.com

    aaron.kempf@gmail.com
    Expand Collapse
    Guest

    what is oracle authentication?

    like a username and password??

    go the SPT (sqlpassthrough) route or linked tables
     
  8. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    <aaron.kempf@gmail.com> wrote in message
    news:1149030938.189881.171010@l28g2000cwl.googlegroups.com
    > what is oracle authentication?
    >
    > like a username and password??
    >
    > go the SPT (sqlpassthrough) route or linked tables


    I'm guessing -- without any real knowledge -- that Oracle "OS
    Authentication" is like SQL Server's Windows Authentication: allowing
    access by network user.

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

    (please reply to the newsgroup)
     
  9. aaron.kempf@gmail.com

    aaron.kempf@gmail.com
    Expand Collapse
    Guest

    nah; i'll bet it integrates with either oracle identity_management or
    ibm or novell or something else

    maybe even that sun one what was it called again??

    -Aaron


    Dirk Goldgar wrote:
    > <aaron.kempf@gmail.com> wrote in message
    > news:1149030938.189881.171010@l28g2000cwl.googlegroups.com
    > > what is oracle authentication?
    > >
    > > like a username and password??
    > >
    > > go the SPT (sqlpassthrough) route or linked tables

    >
    > I'm guessing -- without any real knowledge -- that Oracle "OS
    > Authentication" is like SQL Server's Windows Authentication: allowing
    > access by network user.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
     
  10. HOhrndorf

    HOhrndorf
    Expand Collapse
    Guest

    Aaron,

    Oracle's OS authentication is a means to avoid submitting user
    credendtials when logging in to an Oracle database. To do this, the
    database must be configured to allow OS authentication, and then, the
    login is performed using the OS' user/password (of course the user
    needs to be known on the database side, too).
    Anyway, the solution for the initial problem was meanwhile found: Dirk
    Goldgar's idea "Set lstTest2.Recordset = rstProdmeld" does the job.

    Henner

    aaron.kempf@gmail.com schrieb:

    > nah; i'll bet it integrates with either oracle identity_management or
    > ibm or novell or something else
    >
    > maybe even that sun one what was it called again??
    >
    > -Aaron
    >
    >
    > Dirk Goldgar wrote:
    > > <aaron.kempf@gmail.com> wrote in message
    > > news:1149030938.189881.171010@l28g2000cwl.googlegroups.com
    > > > what is oracle authentication?
    > > >
    > > > like a username and password??
    > > >
    > > > go the SPT (sqlpassthrough) route or linked tables

    > >
    > > I'm guessing -- without any real knowledge -- that Oracle "OS
    > > Authentication" is like SQL Server's Windows Authentication: allowing
    > > access by network user.
    > >
    > > --
    > > Dirk Goldgar, MS Access MVP
    > > www.datagnostics.com
    > >
    > > (please reply to the newsgroup)
     
  11. aaron.kempf@gmail.com

    aaron.kempf@gmail.com
    Expand Collapse
    Guest

    WOW. Crazy.

    I had no idea that the worlds worst database could speak windows
    authentication.

    I guess I learn something new every day.

    Do they do a better job at Windows Authentication than MS does?
    MS SQL authentication sux ballz.

    Bill Gates should be in PRISON for putting our soldiers at risk.

    MS SQL can't protect against a simple dictionary attack.

    -Aaron


    HOhrndorf wrote:
    > Aaron,
    >
    > Oracle's OS authentication is a means to avoid submitting user
    > credendtials when logging in to an Oracle database. To do this, the
    > database must be configured to allow OS authentication, and then, the
    > login is performed using the OS' user/password (of course the user
    > needs to be known on the database side, too).
    > Anyway, the solution for the initial problem was meanwhile found: Dirk
    > Goldgar's idea "Set lstTest2.Recordset = rstProdmeld" does the job.
    >
    > Henner
    >
    > aaron.kempf@gmail.com schrieb:
    >
    > > nah; i'll bet it integrates with either oracle identity_management or
    > > ibm or novell or something else
    > >
    > > maybe even that sun one what was it called again??
    > >
    > > -Aaron
    > >
    > >
    > > Dirk Goldgar wrote:
    > > > <aaron.kempf@gmail.com> wrote in message
    > > > news:1149030938.189881.171010@l28g2000cwl.googlegroups.com
    > > > > what is oracle authentication?
    > > > >
    > > > > like a username and password??
    > > > >
    > > > > go the SPT (sqlpassthrough) route or linked tables
    > > >
    > > > I'm guessing -- without any real knowledge -- that Oracle "OS
    > > > Authentication" is like SQL Server's Windows Authentication: allowing
    > > > access by network user.
    > > >
    > > > --
    > > > Dirk Goldgar, MS Access MVP
    > > > www.datagnostics.com
    > > >
    > > > (please reply to the newsgroup)
     

Share This Page