Help With List Box and Recordset Property

[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

 

[cmmrfrds]

If RST.EOF = True Then
MsgBox &quot;RST failed&quot;
else
MsgBox &quot;number of records = &quot; & RST.RecordCount
End if

' Here is a guess - Maybe referencing a record
before equating is changing some property. Comment out.
'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

 

[SlayerFreak]

Thanks for the tip.

I commented out the lines you suggested and put in the code you offered for testing the validity of the recordset.

The recordset has 3 records, but they still don't show up in the list box.

I'm not sure why this doesn't work.

 

[cmmrfrds]

Some ideas.

Since it displays correctly on Access, my guess is that it is some setting in the sql server provider.

What version of MDAC are you using. That is, what ADO library do you have checked. 2.6 or 2.7 ???

I would try positioning the cursor on the first record. SInce a server side cursor may not load the records until after the first read.
RST.MoveFirst
'Assign recordset to list box
Set Me.List0.Recordset = RST

Maybe changing the locktype by leaving it off and letting it take the default.
OR
Making the cursor location client side. TRY FIRST
Source.CursorLocation = adUseClient

I would not use the word SOURCE to define an object since it is a reserved word and could cause problems.
Could try [source] or better cnn or cn or src etc...

 

[SlayerFreak]

I'm using ADO 2.5 ( with the 2.5 recordset library, if that means anything ).

1. Changed name of 'source' object to 'CNN'.
2. CNN.CursorLocation = adUseClient causes a 'no such interface supported' error when the code hits: Set Me.List2.Recordset = RST
3. Tried RST.MoveFirst

No luck. Obviously i don't have the most recent ado. I'll find the latest/greatest and try #2 again.

Thanks again

 

[cmmrfrds]

You are ahead of me with Access 2002, I am using 2000 and only have the recordset object on the Form not on the listbox or combobox. In Access 97 there isn't even a recordset object on the Form. Although, I have used the recordset object on the Form with success in Access 2000.

Also, I am using sql server 2000 and there may be some differences on how some of the properties work - not sure.

I am using ADO 2.6, but I thought ADO 2.5 had client side cursoring. Maybe sql server 7 has some limitations - don't know.

Hope you get it.