Accessing Access database from Visio Macro

[Mikhail]

I'm trying to pull down information from an Access database to use in my
Visio document. I'm using the following code:

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source=C:\Assets.mdb"
.Open
End With

Dim strVariable1 As String

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT FirstName FROM Employees", conn
strVariable1 = rs("FirstName")

MsgBox strVariable1
rs.Close

Set rs = Nothing

The message box is used to test what comes down from the database..
Unfortunately only the first record seems to appear. How do I remedy this
problem and get all of the records?

Thanks

 

[Cookville]

You need to iterate thru the resultset...

Do until rs.EOF 'looking for EndOfFile
MsgBox rs("FirstName") 'display in msgbox and click OK
rs.MoveNext 'move to next record
Loop

 

[Mikhail]

Worked perfectly. Thanks a lot.

You need to iterate thru the resultset...

Do until rs.EOF 'looking for EndOfFile
MsgBox rs("FirstName") 'display in msgbox and click OK
rs.MoveNext 'move to next record
Loop