Speed issues with combo box

[Ross Gilbert]

I'm having a speed issue loading a combo box will a lot of records. I'm trying
to load 360 items into it using a simple Do Until statement.
For Example o Until rs.EOF
cmbCity.AddItem rs!City
rs.MoveNext
Loop
rs.Close
I know this is a lot of items to load but this is the way the powers that
be want it. Is there a better way to do this? Thanks in advance.

Ross Gilbert

[ralph]

360 records doesn't sound like too many. Are you sure it is this code that
is slow?

1) Turn sort off.
2) Make recordset "forward-only"
3) Use the "With .. End With" block on rs
4) Not sure if the field variant rs!City is slower or not,
but might be worth a look.

Does anyone know if the 'move' method is faster on a 'forward-only' recordset.
(I have no reason to believe it is.)

Take a look at some of the 3rd party toys out there that take a straight
recordset assignment.

[Ross Gilbert]

Thanks Ralph
I didn't think it was that many records either. But even after making
the changes you suggested it takes 4.2 seconds to load. I've tried it on
other machines with the same results. Thanks for the help!

"ralph" <nt_consulting32@hotmal.com> wrote:
>
>360 records doesn't sound like too many. Are you sure it is this code that
>is slow?
>
>1) Turn sort off.
>2) Make recordset "forward-only"
>3) Use the "With .. End With" block on rs
>4) Not sure if the field variant rs!City is slower or not,
> but might be worth a look.
>
>Does anyone know if the 'move' method is faster on a 'forward-only' recordset.
>(I have no reason to believe it is.)
>
>Take a look at some of the 3rd party toys out there that take a straight
>recordset assignment.
>
>

[Matthew Solnit]

Try this:

Dim fldCity As ADODB.Field

Set fldCity = rs.Fields("City")

Do Until rs.EOF
cmbCity.AddItem fldCity.Value
rs.MoveNext
Loop

"Ross Gilbert" <rgilbert@drc.com> wrote in message
news:3993f929$1@news.devx.com...
>
> Thanks Ralph
> I didn't think it was that many records either. But even after making
> the changes you suggested it takes 4.2 seconds to load. I've tried it on
> other machines with the same results. Thanks for the help!
>
> "ralph" <nt_consulting32@hotmal.com> wrote:
> >
> >360 records doesn't sound like too many. Are you sure it is this code
that
> >is slow?
> >
> >1) Turn sort off.
> >2) Make recordset "forward-only"
> >3) Use the "With .. End With" block on rs
> >4) Not sure if the field variant rs!City is slower or not,
> > but might be worth a look.
> >
> >Does anyone know if the 'move' method is faster on a 'forward-only'
recordset.
> >(I have no reason to believe it is.)
> >
> >Take a look at some of the 3rd party toys out there that take a straight
> >recordset assignment.
> >
> >
>

[Ross Gilbert]

Thanks Matthew
I inserted your code but I'm still getting 4.2 second. It has to be something
else, maybe something in the recordset itself. I think I'm going to load
the data into an access table and see what happens. Thanks Again!

"Matthew Solnit" <nospam@nospam.com> wrote:
>Try this:
>
>Dim fldCity As ADODB.Field
>
>Set fldCity = rs.Fields("City")
>
>Do Until rs.EOF
> cmbCity.AddItem fldCity.Value
> rs.MoveNext
>Loop
>
>"Ross Gilbert" <rgilbert@drc.com> wrote in message
>news:3993f929$1@news.devx.com...
>>
>> Thanks Ralph
>> I didn't think it was that many records either. But even after making
>> the changes you suggested it takes 4.2 seconds to load. I've tried it
on
>> other machines with the same results. Thanks for the help!
>>
>> "ralph" <nt_consulting32@hotmal.com> wrote:
>> >
>> >360 records doesn't sound like too many. Are you sure it is this code
>that
>> >is slow?
>> >
>> >1) Turn sort off.
>> >2) Make recordset "forward-only"
>> >3) Use the "With .. End With" block on rs
>> >4) Not sure if the field variant rs!City is slower or not,
>> > but might be worth a look.
>> >
>> >Does anyone know if the 'move' method is faster on a 'forward-only'
>recordset.
>> >(I have no reason to believe it is.)
>> >
>> >Take a look at some of the 3rd party toys out there that take a straight
>> >recordset assignment.
>> >
>> >
>>
>
>

[Lenny Toulson]

If you're having to hit the DB server for each record as you request it, you
may not be able to squeeze much more performance from your routine. It's
just a guess, but give this a shot and let me know how it turns out:

Private Sub LoadCombo()
Dim acnDBConn As ADODB.Connection
Dim arsData As ADODB.Recordset
Dim strValue As String
Dim lngStart As Long
Dim lngStop As Long

'---------------------------
' Open DB connection
'---------------------------
Set acnDBConn = New ADODB.Connection
With acnDBConn
'---------------------------
' Make sure to use your correct values here!!!
'---------------------------
.ConnectionString = "DRIVER=SQL Server;" & _
"UID=sa;PWD=;SERVER=;DATABASE="
.CursorLocation = adUseClient
.Mode = adModeRead
.Open
End With

'---------------------------
' Get desired data
'---------------------------
Set arsData = New ADODB.Recordset
With arsData
Set .ActiveConnection = acnDBConn
.Source = "SELECT * FROM TableName"
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.Open
End With

'---------------------------
' Load into combo box
'---------------------------
Me.Combo1.Clear
With arsData
Do Until .EOF
With .Fields
strValue = .Item("FieldName")
Me.Combo1.AddItem strValue
End With

.MoveNext
Loop
End With
End Sub

I ran this sub against a remote SQL Server database over a 56K Internet
connection and it completed the routine in about 250-260 milliseconds after
opening the connection and recordset. The whole routine ran in 2.5 seconds
or less.

Good luck.

--
Lenny
__________


"Ross Gilbert" <rgilbert@drc.com> wrote in message
news:39940f7c$1@news.devx.com...

I inserted your code but I'm still getting 4.2 second. It has to be
something
else, maybe something in the recordset itself. I think I'm going to load
the data into an access table and see what happens. Thanks Again!

[Joel Ryan]

How are you opening the recordset.

rs.Open "cities"

or

rs.Open "select city_name, city_id from cities"

-- Joel

Ross Gilbert wrote:

> Thanks Matthew
> I inserted your code but I'm still getting 4.2 second. It has to be something
> else, maybe something in the recordset itself. I think I'm going to load
> the data into an access table and see what happens. Thanks Again!
>
> "Matthew Solnit" <nospam@nospam.com> wrote:
> >Try this:
> >
> >Dim fldCity As ADODB.Field
> >
> >Set fldCity = rs.Fields("City")
> >
> >Do Until rs.EOF
> > cmbCity.AddItem fldCity.Value
> > rs.MoveNext
> >Loop
> >
> >"Ross Gilbert" <rgilbert@drc.com> wrote in message
> >news:3993f929$1@news.devx.com...
> >>
> >> Thanks Ralph
> >> I didn't think it was that many records either. But even after making
> >> the changes you suggested it takes 4.2 seconds to load. I've tried it
> on
> >> other machines with the same results. Thanks for the help!
> >>
> >> "ralph" <nt_consulting32@hotmal.com> wrote:
> >> >
> >> >360 records doesn't sound like too many. Are you sure it is this code
> >that
> >> >is slow?
> >> >
> >> >1) Turn sort off.
> >> >2) Make recordset "forward-only"
> >> >3) Use the "With .. End With" block on rs
> >> >4) Not sure if the field variant rs!City is slower or not,
> >> > but might be worth a look.
> >> >
> >> >Does anyone know if the 'move' method is faster on a 'forward-only'
> >recordset.
> >> >(I have no reason to believe it is.)
> >> >
> >> >Take a look at some of the 3rd party toys out there that take a straight
> >> >recordset assignment.
> >> >
> >> >
> >>
> >
> >