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

[HOhrndorf]

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?

 

[Douglas J Steele]

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

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

 

[Dirk Goldgar]

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?

 

[HOhrndorf]

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

 

[Dirk Goldgar]

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).

 

[aaron.kempf]

what is oracle authentication?

like a username and password??

go the SPT (sqlpassthrough) route or linked tables

 

[Dirk Goldgar]

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.

 

[aaron.kempf]

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

 

[HOhrndorf]

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]

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