EndOfRecordset

[nrm3]

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?

 

[JamesDSM50328]

Are you opening a recordset?

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

 

[nrm3]

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?

 

[nrm3]

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.

 

[nrm3]

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