INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

EndOfRecordset

EndOfRecordset

(OP)
I am using Attachmate (EXTRA! Basic) to connect to MS access db tables to collect and post data to and from the db tables to host 3270 screens. Thus far, I have been successful with manipulating the data.  I cannot figure out how to make an ADO connection with events enabled like EndOfRecordset etc.  When I try to Dim WithEvents
I get syntax errors. With research I have found examples of how to code it, but cannot get it to compile.

    Dim WithEvents objRecSet As ADODB.Recordset
Private Sub objRecSet_EndOfRecordset( fMoreData As Boolean, _
   adStatus As ADODB.EventStatusEnum, _
   ByVal pRecordset As ADODB.Recordset )

' place any code you desire here, for example

If fMoreData = False Then
   adStatus = adStatusCancel
End If

My scripts can not recognize EOF conditions. I use the On Error (statement)go to label (abc etc).  

  I use the same connection code for all of my scripts:
 Example :
Global variable declarations
       Global g_HostSettleTime%
       Global g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")    ' Gets the system object
    If (System is Nothing) Then
        Msgbox "Could not create the EXTRA System object.  Stopping macro playback."
        STOP
    End If
    Set Sessions = System.Sessions

    If (Sessions is Nothing) Then
        Msgbox "Could not create the Sessions collection object.  Stopping macro playback."
        STOP
    End If
'--------------------------------------------------------------------------------
'''Set Value For Pause Between Actions
' Set the default wait timeout value
    g_HostSettleTime = 1000        ' milliseconds

    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If

' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If (Sess0 is Nothing) Then
        Msgbox "Could not create the Session object.  Stopping macro playback."
        STOP
    End If
    If Not Sess0.Visible Then Sess0.Visible = TRUE
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
 'Set up Access
         Dim MyScreen As Object
         Dim MyArea As Object
         Dim Conn As Object
         Dim RS As Object
         Dim D_Base As String
         Dim strSQL As String

Set MyScreen = Sess0.Screen
         Set Conn = CreateObject("ADODB.Connection")
         Set RS = CreateObject("ADODB.Recordset")
'''LockType And CursorType Allow The Script To Write In The RecordSet
         RS.LockType = 3 '(adLockOptimistic Optimistic locking, record by record.)
         RS.CursorType = 2 '(adOpenDynamic, dynamic cursor)
 
 '''Path And Name Of Database
         
         D_Base = "D:\Documents and Settings\yxmnwkl\My Documents\User.mdb"
 '''Create Connection with Access
         Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & D_Base


Does anybody have insight on how to create an ADO connection with events enabled?
 

RE: EndOfRecordset

Are you opening a recordset?

CODE

Set Conn = CreateObject("ADODB.Connection")
         Set RS = CreateObject("ADODB.Recordset")
'''LockType And CursorType Allow The Script To Write In The RecordSet
        'Set Conn = CreateObject("ADODB.Connection") setting this as part of the recordset
        'Set RS = CreateObject("ADODB.Recordset") setting this as part of the recordset

 '''Path And Name Of Database
         
         D_Base = "D:\Documents and Settings\yxmnwkl\My Documents\User.mdb"
 '''Create Connection with Access
         Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & D_Base
         RS.Open "Select * from table", Conn, adOpenDynamic, adLockOptimistic
         If RS.BOF = True And RS.EOF = True Then Exit Sub 'exit if no records
        
         Do While RS.EOF = False
            'do stuff
            '...
         Loop

RE: EndOfRecordset

(OP)
Thanks for the reply. Yes the script posted does open a recordset. Everything functions except EOF events.I can write,read and delete records and fields.Any suggestions?

RE: EndOfRecordset

(OP)
JamesDSM50328's  you may have solved my issue.I did not have the line "If RS.BOF = True And RS.EOF = True Then Exit Sub" in my code and my Do while statement was
Do While Not RS.EOF.  I will update my code and post back to you with the results. Sorry for not reading your reply
thoroughly.

RE: EndOfRecordset

(OP)
JamesDSM50328's My problem is solved with your good advice!!!
I added your advised code and all is well. Thanks!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close