UserForm Listbox with Access Database Table data

[RobLo]

Hi there,

I want to create a UserForm in Outlook containing a listbox. This
listbox should contain data stored in an access database table. I have
tried with following code, but get an error on the line with the
command to "Transpose" the array. Any ide...?

Many Thanks,
Roberto

************************************************************

Sub PopulateListBox1()

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim acc As Access.Application
Dim rcArray As Variant
Dim sSQL As String

Set acc = CreateObject("Access.Application")

'Set the location of your database, the connection string and the SQL
query
sSQL = "SELECT tbl_Outlook.* FROM tbl_Outlook;"

'Open connection to the database
cnt.Open glob_sConnect

'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows

rst.MoveFirst
MsgBox rst!UserId

'Place data in the listbox
With Me.ListBox1
.Clear
.ColumnCount = 6
.List = acc.Transpose(rcArray)
.ListIndex = -1
End With

'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

 

[RobLo]

Sorry, here's some additional info. I have defined the following
Global Constant as well as referenced the Microsoft ActiveX Data
Objects 2.7 Library.

Const glob_sdbPath = " <DATABASE PATH> "
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& glob_sdbPath & ";"

Thanks,
Roberto

 

[RobLo]

Sorry, here's some additional info. I have defined the following
Global Constant as well as referenced the Microsoft ActiveX Data
Objects 2.7 Library.

Const glob_sdbPath = " <DATABASE PATH> "
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& glob_sdbPath & ";"

Thanks,
Roberto

 

[Sue Mosher [MVP-Outlook]]

Does this statement work instead:

.Column = rcArray

For future reference, please include information on the nature of any errors, not just the fact that an error occurred.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[RobLo]

Hi Sue,
That worked just perfectly now. Many thanks!
Best regards,
Roberto