Continuous Forms with an Oracle ADO recordset

[bittman]

I have code as follows:

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
 
conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
conn.Open
 
rst.Open "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status", conn
 
Do Until rst.EOF
  For i = 0 To rst.Fields.Count - 1
     --this is where I need help!
  Next
  rst.MoveNext
Loop
 
rst.Close
conn.Close

In the middle of that loop I've had this sort of thing:

Me!Text1 = rst!sid
Me!Text3 = rst![serial#]

...and so on.

That works nicely... provided I only want to see one record! It's always the last record, too, because the MoveNext neatly traverses its way through the entire recordset, of course.

What I can't work out is how to display all records on a Continuous Forms basis. I've tried this:

Me!Text1.ControlSource = rst!sid

...but that just makes the control display '#Name?', which made me think the problem was the form is unbound to any record source. So then I tried:

Me.RecordSource = rst

...outside the loop, with the ControlSource setting inside. But that merely produces a 'type mismatch' error. And I also tried setting Me.RecordSET =rst, but that causes an 'Object variable or With block variable not set' error.

I'd appreciate any guidance on this anyone can offer!

It is possible that continuous forms would not be the most appropriate way of displaying this information anyway, so if someone could also explain how to set a list control to display all the records in my recordset, I'd be very grateful!

[JConsulting]

I have code as follows:

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
 
conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
conn.Open
 
rst.Open "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status", conn
 
Do Until rst.EOF
  For i = 0 To rst.Fields.Count - 1
     --this is where I need help!
  Next
  rst.MoveNext
Loop
 
rst.Close
conn.Close

In the middle of that loop I've had this sort of thing:

Me!Text1 = rst!sid
Me!Text3 = rst![serial#]

...and so on.

That works nicely... provided I only want to see one record! It's always the last record, too, because the MoveNext neatly traverses its way through the entire recordset, of course.

What I can't work out is how to display all records on a Continuous Forms basis. I've tried this:

Me!Text1.ControlSource = rst!sid

...but that just makes the control display '#Name?', which made me think the problem was the form is unbound to any record source. So then I tried:

Me.RecordSource = rst

...outside the loop, with the ControlSource setting inside. But that merely produces a 'type mismatch' error. And I also tried setting Me.RecordSET =rst, but that causes an 'Object variable or With block variable not set' error.

I'd appreciate any guidance on this anyone can offer!

It is possible that continuous forms would not be the most appropriate way of displaying this information anyway, so if someone could also explain how to set a list control to display all the records in my recordset, I'd be very grateful!

I believe you need to just set the form's recordsource

me.recordsource = rst

Looping through controls will only update the fields in the first record.

And I believe you'll need to add a dynaset option or cursor location. But Oracle isn't my specialty.
J

[Jim Doherty]

I have code as follows:

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
 
conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
conn.Open
 
rst.Open "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status", conn
 
Do Until rst.EOF
  For i = 0 To rst.Fields.Count - 1
     --this is where I need help!
  Next
  rst.MoveNext
Loop
 
rst.Close
conn.Close

In the middle of that loop I've had this sort of thing:

Me!Text1 = rst!sid
Me!Text3 = rst![serial#]

...and so on.

That works nicely... provided I only want to see one record! It's always the last record, too, because the MoveNext neatly traverses its way through the entire recordset, of course.

What I can't work out is how to display all records on a Continuous Forms basis. I've tried this:

Me!Text1.ControlSource = rst!sid

...but that just makes the control display '#Name?', which made me think the problem was the form is unbound to any record source. So then I tried:

Me.RecordSource = rst

...outside the loop, with the ControlSource setting inside. But that merely produces a 'type mismatch' error. And I also tried setting Me.RecordSET =rst, but that causes an 'Object variable or With block variable not set' error.

I'd appreciate any guidance on this anyone can offer!

It is possible that continuous forms would not be the most appropriate way of displaying this information anyway, so if someone could also explain how to set a list control to display all the records in my recordset, I'd be very grateful!

If you specifically desire continous form viewing have you considered early binding using linked table ODBC using oracle driver?. Controlling your rst by late binding yourself you lose that functionality..File...Get External Data... Linked Tables.

Jim

Jim

[ADezii]

I have code as follows:

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
 
conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
conn.Open
 
rst.Open "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status", conn
 
Do Until rst.EOF
  For i = 0 To rst.Fields.Count - 1
     --this is where I need help!
  Next
  rst.MoveNext
Loop
 
rst.Close
conn.Close

In the middle of that loop I've had this sort of thing:

Me!Text1 = rst!sid
Me!Text3 = rst![serial#]

...and so on.

That works nicely... provided I only want to see one record! It's always the last record, too, because the MoveNext neatly traverses its way through the entire recordset, of course.

What I can't work out is how to display all records on a Continuous Forms basis. I've tried this:

Me!Text1.ControlSource = rst!sid

...but that just makes the control display '#Name?', which made me think the problem was the form is unbound to any record source. So then I tried:

Me.RecordSource = rst

...outside the loop, with the ControlSource setting inside. But that merely produces a 'type mismatch' error. And I also tried setting Me.RecordSET =rst, but that causes an 'Object variable or With block variable not set' error.

I'd appreciate any guidance on this anyone can offer!

It is possible that continuous forms would not be the most appropriate way of displaying this information anyway, so if someone could also explain how to set a list control to display all the records in my recordset, I'd be very grateful!

1. Declare a Global Variable to reference your Open Recordset.
2. Open your Form Hidden.
3. Set the Form's Recordset Property to the Open Recordset which you created.
4. Make the Form Visible.
5. When finished with the Form, Close the Recordset and release its Object Variable Reference (Set rst = Nothing).
6. I do believe that JConsulting is correct in that your Recordset may be Forward Only/Read Only.
7. You may have to set the Cursor Location to adUseClient and the Cursor Type to adOpenKeyset.
8. I'm like JConsulting in that I'm not an Oracle expert, so you must experiment should your Form be Read Only.
9. You should be able to accomplish what you have requested with the information that I have given you.
10. Good Luck.

[bittman2]

I believe you need to just set the form's recordsource

me.recordsource = rst

Looping through controls will only update the fields in the first record.

And I believe you'll need to add a dynaset option or cursor location. But Oracle isn't my specialty.
J

Thanks, but I did mention that I'd already tried that and that it produced an error.

[bittman2]

1. Declare a Global Variable to reference your Open Recordset.
2. Open your Form Hidden.
3. Set the Form's Recordset Property to the Open Recordset which you created.
4. Make the Form Visible.
5. When finished with the Form, Close the Recordset and release its Object Variable Reference (Set rst = Nothing).
6. I do believe that JConsulting is correct in that your Recordset may be Forward Only/Read Only.
7. You may have to set the Cursor Location to adUseClient and the Cursor Type to adOpenKeyset.
8. I'm like JConsulting in that I'm not an Oracle expert, so you must experiment should your Form be Read Only.
9. You should be able to accomplish what you have requested with the information that I have given you.
10. Good Luck.

Phew! Thanks for all that. I frankly didn't understand most of it, I'm afraid. I do Oracle for a living, not VBA!

This is what I came up with and which works quite nicely for my purposes:

Dim db As Database
Dim conn As New ADODB.Connection
Dim qdf As QueryDef
Dim rst As Recordset
 
conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
 
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;DSN=WIN10;UID=SYSTEM;PWD=tr7025ds"
qdf.SQL = "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
 
Me!Sessions.RowSource = ""
 
Do Until rst.EOF
  For i = 0 To rst.Fields.Count - 1
     Me!Sessions.RowSource = Me!Sessions.RowSource & rst.Fields(i) & ";"
  Next
  rst.MoveNext
Loop
 

It's probably horrible from a professional perspective, but it gets the job done for a list box (called 'Sessions') that allows me to select the records I'm interested in and then to start drilling down into more detail on other forms.

I appreciate the assistance offered, however.