Write data to Access

[scottian]

Someone please help.

Im trying to connect attachmate to a database. open a table and have attachmate loop through the records, obtain some info from each account and then write it back into a field in the database. But i cant work it out. This is what ive got so far.

Sub Main()

Dim Sys As Object, Sess As Object
Dim conn As Object, rs As Object, D_Base As String, FBRef As String
Dim C_Name As String

Set Sys = CreateObject("Extra.System")
Set Sess = Sys.ActiveSession

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

Dim AddLineInfo

D_Base = "C:\E_G_U.mdb"
FBRef = "Select FBRef from tblMain;"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & D_Base

rs.Open "tblMain", conn

Do Until rs.EOF
Sess.Screen.Sendkeys("<Pf24>")
Sess.Screen.WaitHostQuiet(g_HostSettleTime)
Sess.Screen.Sendkeys("<Home>post")
Sess.Screen.PutString rs.Fields("FBRef"), 09, 23
Sess.Screen.Sendkeys("<Enter>")
rs.Fields("Name") = Sess.Screen.GetString(02,02,36)
Sess.Screen.WaitHostQuiet(g_HostSettleTime)
rs.MoveNext
Loop
rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing
Set Sess = Nothing
Set Sys = Nothing

End Sub

Im thinking that <Set rs = CreateObject("ADODB.Recordset")> is the problem and that i should be linked directly to the database.

Ambition..........If you dont use it, you wont lose it

 

[scottian]

I managed to solve it, so i thought Id put my solution here for others.

Global g_HostSettleTime%

Sub Main()
'''Declare Variables
Dim Sys As Object
Dim Sess As Object
Dim Conn As Object
Dim RS As Object
Dim D_Base As String

'''Set Objects And Sessions   
Set Sys = CreateObject("Extra.System")
Set Sess = Sys.ActiveSession
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")

'''LockType And CursorType Allow The Script To Write In The RecordSet
RS.LockType = 3
RS.CursorType = 2
'''Set Value For Pause Between Actions
g_HostSettleTime = 50

'''Path And Name Of Database
D_Base = "C:\E_G_U.mdb"

'''Create Connection With Access
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & D_Base

'''Get Field Data From Database Table And Place In Recordset
RS.Open "tblMain", conn

'''Loop Through All Records In The Table
Do Until RS.EOF

'''Actions Carried Out In MainFrame

'====================================================================

'''Return To Front Screen
Sess.Screen.Sendkeys("<Pf24>")
Sess.Screen.WaitHostQuiet(g_HostSettleTime)

'''Place Cursor In TRANSACTION CODE Field
Sess.Screen.Sendkeys("<Home>POST")
Sess.Screen.WaitHostQuiet(g_HostSettleTime)
'''Paste FBR In Account Field
Sess.Screen.PutString RS.Fields("FBR"), 09, 23
Sess.Screen.WaitHostQuiet(g_HostSettleTime)

Sess.Screen.Sendkeys("<Enter>")
Sess.Screen.WaitHostQuiet(g_HostSettleTime)

'''Copy The Required Data From The Screen Into The Corresponding RecordSet Fields
RS.Fields("CustName") = Sess.Screen.GetString(02,02,36)
RS.Fields("Address1") = Sess.Screen.GetString(03,02,36)
RS.Fields("Address2") = Sess.Screen.GetString(04,02,36)
RS.Fields("Address3") = Sess.Screen.GetString(05,02,36)

'''Move To Next Record
RS.MoveNext
Loop
msgbox "Done"

'''Close RecordSet, Connection And Session
RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
Set Sess = Nothing
Set Sys = Nothing

End Sub

Ambition..........If you dont use it, you wont lose it