By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,398 Members | 1,029 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,398 IT Pros & Developers. It's quick & easy.

Continuous Forms with an Oracle ADO recordset

P: 1
I have code as follows:

Expand|Select|Wrap|Line Numbers
  1. Dim conn As New ADODB.Connection
  2. Dim rst As New ADODB.Recordset
  3.  
  4. conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
  5. conn.Open
  6.  
  7. rst.Open "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status", conn
  8.  
  9. Do Until rst.EOF
  10.   For i = 0 To rst.Fields.Count - 1
  11.      --this is where I need help!
  12.   Next
  13.   rst.MoveNext
  14. Loop
  15.  
  16. rst.Close
  17. conn.Close
In the middle of that loop I've had this sort of thing:

Expand|Select|Wrap|Line Numbers
  1. Me!Text1 = rst!sid
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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!
Aug 31 '07 #1
Share this Question
Share on Google+
5 Replies


JConsulting
Expert 100+
P: 603
I have code as follows:

Expand|Select|Wrap|Line Numbers
  1. Dim conn As New ADODB.Connection
  2. Dim rst As New ADODB.Recordset
  3.  
  4. conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
  5. conn.Open
  6.  
  7. rst.Open "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status", conn
  8.  
  9. Do Until rst.EOF
  10.   For i = 0 To rst.Fields.Count - 1
  11.      --this is where I need help!
  12.   Next
  13.   rst.MoveNext
  14. Loop
  15.  
  16. rst.Close
  17. conn.Close
In the middle of that loop I've had this sort of thing:

Expand|Select|Wrap|Line Numbers
  1. Me!Text1 = rst!sid
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 31 '07 #2

Jim Doherty
Expert 100+
P: 897
I have code as follows:

Expand|Select|Wrap|Line Numbers
  1. Dim conn As New ADODB.Connection
  2. Dim rst As New ADODB.Recordset
  3.  
  4. conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
  5. conn.Open
  6.  
  7. rst.Open "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status", conn
  8.  
  9. Do Until rst.EOF
  10.   For i = 0 To rst.Fields.Count - 1
  11.      --this is where I need help!
  12.   Next
  13.   rst.MoveNext
  14. Loop
  15.  
  16. rst.Close
  17. conn.Close
In the middle of that loop I've had this sort of thing:

Expand|Select|Wrap|Line Numbers
  1. Me!Text1 = rst!sid
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 31 '07 #3

ADezii
Expert 5K+
P: 8,669
I have code as follows:

Expand|Select|Wrap|Line Numbers
  1. Dim conn As New ADODB.Connection
  2. Dim rst As New ADODB.Recordset
  3.  
  4. conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
  5. conn.Open
  6.  
  7. rst.Open "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status", conn
  8.  
  9. Do Until rst.EOF
  10.   For i = 0 To rst.Fields.Count - 1
  11.      --this is where I need help!
  12.   Next
  13.   rst.MoveNext
  14. Loop
  15.  
  16. rst.Close
  17. conn.Close
In the middle of that loop I've had this sort of thing:

Expand|Select|Wrap|Line Numbers
  1. Me!Text1 = rst!sid
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 1 '07 #4

P: 2
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.
Sep 1 '07 #5

P: 2
  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:

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim conn As New ADODB.Connection
  3. Dim qdf As QueryDef
  4. Dim rst As Recordset
  5.  
  6. conn.ConnectionString = "Provider=MSDAORA;Password=tr7025ds;User ID=system;Data Source=WIN10"
  7.  
  8. Set qdf = CurrentDb.CreateQueryDef("")
  9. qdf.Connect = "ODBC;DSN=WIN10;UID=SYSTEM;PWD=tr7025ds"
  10. qdf.SQL = "select sid, serial#, username, program, machine, status from v$session where serial#<>1 order by status"
  11. qdf.ReturnsRecords = True
  12. Set rst = qdf.OpenRecordset
  13.  
  14. Me!Sessions.RowSource = ""
  15.  
  16. Do Until rst.EOF
  17.   For i = 0 To rst.Fields.Count - 1
  18.      Me!Sessions.RowSource = Me!Sessions.RowSource & rst.Fields(i) & ";"
  19.   Next
  20.   rst.MoveNext
  21. Loop
  22.  
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.
Sep 1 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.