ACCESS AND ATTACHMATE VB 2

[NWildblood]

Hi, I currently successfully run various jobs in Attachmate Extra from VB code sitting in Excel. I have given an example below. My question is, how can I run similar VB directly from MS Access table instead ? Any answers *very* gratefully received... thanks

-------
Sub CertifySDH() 'to run from the sdhmatches spreadsheet produced by the cert engine
Dim System As Object, Sessions As Object, Sess As Object, CSS As Object
Dim msg_line As String

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 CSS = GetObject("C:\Documents and Settings\zeicri\Desktop\SMNXS06.edp")
Set Sess = System.ActiveSession

If (Sess Is Nothing) Then
MsgBox "Could not create the Session object. Stopping macro playback."
Stop
End If

If Not Sess.Visible Then Sess.Visible = True

msg_line = Trim(Sess.Screen.GetString(24, 1, Sess.Screen.Cols))
xlrow = 2

Sess.Screen.SendKeys ("<Home>MJB1<Enter>")
Call Wait(Sess)

Sess.Screen.SendKeys ("<Tab>")

Do While Trim(Range("A" & xlrow).Value) <> ""

Sess.Screen.SendKeys (Trim(Range("A" & xlrow).Value)) 'JOB NUMBER
Sess.Screen.SendKeys ("<Enter>")
Call Wait(Sess)
Sess.Screen.SendKeys ("<NewLine>")
Sess.Screen.SendKeys (Trim(Range("Q" & xlrow).Value)) 'APC
Sess.Screen.SendKeys ("<NewLine><NewLine><Delete><Tab><Delete><EraseEOF>")
Sess.Screen.SendKeys (Trim(Range("W" & xlrow).Value)) '1141
Sess.Screen.SendKeys ("<Enter>")
Call Wait(Sess)
Sess.Screen.SendKeys ("<Enter>")
Call Wait(Sess)
Sess.Screen.SendKeys ("<Pf16>")
Call Wait(Sess)
Sess.Screen.SendKeys (Trim(Range("Q" & xlrow).Value)) 'APC
Sess.Screen.SendKeys ("<Tab><Tab><Tab><Tab>")
Sess.Screen.SendKeys (Trim(Range("W" & xlrow).Value)) '1141
Sess.Screen.SendKeys ("<Delete><EraseEOF>")
Sess.Screen.SendKeys ("<NewLine><NewLine><NewLine><NewLine><NewLine><NewLine><NewLine>Y<Enter>")
Call Wait(Sess)
Sess.Screen.SendKeys ("<Pf5>")
Call Wait(Sess)
Sess.Screen.SendKeys ("1")
Call Wait(Sess)
Sess.Screen.SendKeys ("<Pf5>")
Call Wait(Sess)
Sess.Screen.SendKeys ("<NewLine><NewLine><Delete><EraseEOF>")
Sess.Screen.SendKeys (Trim(Range("U" & xlrow).Value)) 'ASSET ID
Sess.Screen.SendKeys ("<NewLine><Delete><EraseEOF>")
Sess.Screen.SendKeys (Trim(Range("V" & xlrow).Value)) 'ASSET DESC
Sess.Screen.SendKeys ("<Enter>")
Sess.Screen.WaitForString "TI004 - Modification Successful"

Sess.Screen.SendKeys ("<Pf3><Pf3>")
Call Wait(Sess)




xlrow = xlrow + 1

Sess.Screen.SendKeys ("<Pf9><Tab>")
Call Wait(Sess)
Loop

MsgBox "DONE", vbOKOnly, "End of Batch"
End Sub

----
Private Sub Wait(Sess As Object)
Do While Sess.Screen.OIA.XStatus <> 0
DoEvents
Loop
End Sub
----

"No-one got everything done by Friday except Robinson Crusoe...

 

[Skie]

Most of it should traslate right over. You just need to change the Extra info to Access info.

Dim oDB As Database
Dim oRS As Recordset
Dim vSomeVal

Set oDB = OpenDatabase("YourDatabase")
Set oRS = oDB.OpenRecordset("YourTable")
If Not oRS.BOF Then
   oRS.MoveFirst
   Do While Not oRS.EOF
     Sess.Screen.SendKeys Trim(oRS.Fields("JOB NUMBER").Value)
     'And so on and so forth
     oRS.MoveNext
   Loop
End If

The BOF will detect an empty table. The EOF runs until the recordset is passed the last record (the end). And MoveNext is kind of like going through rows using xlrow = xlrow + 1.

 

[NWildblood]

Skie
Many thanks, unfortunately although the code I have works in an Excel module, when I use your examples in an Access module I get "User-defined type not defined" - unfortunately I'm not coming to writing VB from a coding background, but by someone who has had an extreme dislike of manual transaction processing in Extra, and have therefore cobbled together solutions rather than had training .. sorry if my ignorance is showing, but could you show me how would this be structured to work in an Access module ? If too time consuming, please don't worry - I appreciate your already answering my enquiry.
Rgds
OS

"No-one got everything done by Friday except Robinson Crusoe...

 

[Skie]

You need to set the "YourDatabase", "YourTable", "JOB NUMBER" to the correct names. Database would be the filename (I don't remember if it requires path) of the database. Table is the name of the table you plan to access. Job number would be the column name that holds the job numbers.

Also, you still need to initiate all your Extra objects (system, sessions, sess, etc.).

 

[NWildblood]

Got that, many thanks Skie, however am getting Type mismatch (Error 13) with

Set oRS = oDB.OpenRecordset("tCERTIFICATIONREPORT")

Am I missing something ?

Thanks again.

"No-one got everything done by Friday except Robinson Crusoe...

 

[NWildblood]

Skie - it's ok, ref:

Dim oRS As Recordset

I checked out thread705-1209224 and someone else with a similar issue Dimmed X As DAO.Recordset

Seems to work now.
Thank you.

"No-one got everything done by Friday except Robinson Crusoe...

 

[NWildblood]

Skie
- Works a treat ! I'm starring you for such a quick and to the point sollution ... I am one happy bodger.

"No-one got everything done by Friday except Robinson Crusoe...

 

[IknowMe]

OutOfShape,

I'm staring you as well for searching the forums, finding your answer and taking the time to share it here for those that follow.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[NWildblood]

Thanks MrMilson! This forum has been a revelation.

(I like the Tenacious reference BTW... ;-)

"No-one got everything done by Friday except Robinson Crusoe...