List Box Not Populating From Recordset

[SlayerFreak]

Hello all,

I'm using Access 2002 and SQL server 7.
Code attached.

I am trying to populate a list box from a recordset using the box's recordset property. I have two versions of a connection string within the code; one for connecting to a Jet database, and the other for connecting to a SQL Server 7 database. ( I want to connect to the Sequal Server; the Jet connection string is just for troubleshooting/testing )

The recordset gets data successfully regardless of the connection string used.

The list box, however, only displays the recordset data for the Jet connection string. The list is blank when i use the SQL Server 7 connection string.

I've spent 2 days on this and I'm out of ideas. Any help will be greatly appreciated.

Here's the code:

Dim Source As New ADODB.Connection
Set Source = New ADODB.Connection
Dim RST As New ADODB.Recordset

'Create ADODB connection to MS Access mdb file -- RECORDSET GETS DATA AND POPULATES LIST BOX SUCCESSFULY!!!
'With Source
' .Provider = "Microsoft.Jet.OLEDB.4.0"
' .ConnectionString = "data source=h:\calendar\crs master tables.mdb"
' .Open
'End With

'Create ADODB connection to sql server -- RECORDSET WILL GET DATA BUT LIST BOX WILL BE EMPTY
With Source
.Provider = "SQLOLEDB"
.ConnectionString = "Provider=SQLOLEDB;data source=

http://www.crsdeps.com;initial

catalog=RB;User ID=Knowler;Password=thX1138;Integrated Security=SSPI;Persist Security Info=False"
.Open
End With
If Source.State <> 1 Then MsgBox &quot;Source failed&quot;

'Open recordset to populate list box
RST.Open &quot;SELECT FirmID, FirmName FROM Firm&quot;, Source, adOpenStatic, adLockReadOnly
If RST.State <> 1 Then MsgBox &quot;RST failed&quot;

'Testing to make sure there is real data in the recordset
Me!Text4 = RST![FirmID]
Me!Text6 = RST![FirmName]

'Assign recordset to list box
Set Me.List0.Recordset = RST

'Close objects
RST.Close
Source.Close


Thanks in advance!
Eric