Passthrough query as rowsource

[MortenB]

Hi,
There is a nice feature in Oracle sql that elegantly performs a treewalk for you. I have prepared a passthrough query with such a syntax and put it as the rowsource of my form, and it works fine (read-only but that's no problem). Now I would like to parametrize my start criteria for the query, for example "start with Inventory = InvKey" instead of "start with Inventory=82". I thought the right way to do this was by using the Forms onOpen event and build a dynamic passthrough query. However, whatever I do, when I come to "Set Me.Recordset = recInv" I get an error message that tells me that I can't do it! It says, "The object you entered is not a valid recordset property". I am using DAO. What can I do? Who can tell me what I do wrong or give me a good example?

 

[MortenB]

I am rather surprised that I didn't get any replies to this interesting question yet. Maybe it is because I did not specify that my front-end is Access 2002 and my back-end is Oracle? Or am I advancing in undiscovered territory?

 

[PHV]

when I come to "Set Me.Recordset = recInv"[/b]
Any chance you could post the preceding code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MortenB]

Dim recSite As Recordset
Dim wrkODBC As Workspace
Dim conOracle As Connection
Dim strCriteria As String

strCriteria = "ODBC;DSN=......."
Set wrkODBC = CreateWorkspace("newODBCWorkspace", "usr", "pwd", dbUseODBC)
Set conOracle = wrkODBC.OpenConnection("Connection1", dbDriverNoPrompt, , strCriteria)
strCriteria = "select txtSiteCode from DB1.SITE"
Set recSite = conOracle.OpenRecordset(strCriteria)
Set Me.Recordset = recSite

 

[PHV]

You may try to replace this:
Dim recSite As Recordset
Dim conOracle As Connection
By this:
Dim recSite As DAO.Recordset
Dim conOracle As DAO.Connection

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MortenB]

PH
Unless you have a spesific reason for adding DAO, I don't thing it will change anything. I know that DAO is included in my reference list and ADO is not, so there is no possibility for conflict. I have done some more research on the subject, and my theory now is that it is simply impossible to set a Form recordsource in VB using a passthrough query. Right or wrong?
MB