VBScript read from MS Access

[pgriffin]

I see the examples of VBScript reading from an Access table, but I don't need to pass any parameter to look up. I need to read from Access, row by row, and place the values in 4 different MacroScheduler variables each time. every field into a variable and, eventually, every record of the database.



thanks for any response.

[Bob Hansen]

Consider exporting an ASCII text file from Access. Could actually execute that from Macro Scheduler once it is defined as a macro in Access.

Then continue to use Macro Scheduler to read the exported file, line by line in a loop, and assign the values to Macro Scheduler variables.

[support]

There's no reason why you can't do what you want - just split the code up and have a separate function that reads next record and returns the record as a delimited string. Here's an example:

VBSTART

Dim SQLString
Dim MyDB
Dim rsCustomers

Sub OpenRecordSet
set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "DSNCUS"
SQLString = "select * from Customers"
set rsCustomers = MyDB.Execute(SQLString)
rsCustomers.MoveFirst
End Sub

Sub CloseDB
MyDB.Close
End Sub

Function GetNextRecord
If Not rsCustomers.EOF then
GetNextRecord = rsCustomers.Fields("CustomerName") & ";" & _
rsCustomers.Fields("Address1") & ";" & _
rsCustomers.Fields("Address2")
rsCustomers.MoveNext
else
GetNextRecord = 0
end if
End Function

VBEND
VBRun>OpenRecordSet
Label>ReadLoop
VBEval>GetNextRecord,record
if>record=0,doneloop
Separate>record,;,fields
MessageModal>%fields_1%, %fields_2%, %fields_3%
Goto>ReadLoop
Label>doneloop
VBRun>CloseDB

[pgriffin]

PERFECT! Can't thank you enough. All I had to do was change a few names, and voila!

once again, GREAT support.