ListBox MegaList.xls to Word ADO 2.1 
Author Message
 ListBox MegaList.xls to Word ADO 2.1

Hi Peter,

The below coding absolutely worked fine for me.
I referenced in Word VBA to ActiveX Data Objects 2.0 Lib
and believe this to part of Office 97 ...
This was printed in Immediate window after initializing the UserForm:
 2547          254

Siuation: UserForm containing a 254 columned ListBox, named: ListBox1.
Excel workbook: MegaList.xls in c\Temp
Named range: WholeThing (covering the entire data region)

And the rest is coded below ...
Again, worked perfectly.

I believe you can copy/paste below code into your project, noticing
that you will only have to adjust the pathname leading to the target XLS.
Make sure your project is pointing to ADO 2.0 at least
Better 2.1 or 2.5 ...

If still having problems, repost ...
Not giving up, never !

Krgrds,
Perry

Here goes the code:

Private Sub UserForm_Initialize()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim x As Long
    Dim dummy As Variant

    Set conn = New ADODB.Connection
    With conn
        .ConnectionString = "Provider=MSDASQL;" & _
                "Driver={Microsoft Excel Driver (*.xls)};" & _
                "DBQ=c:\temp\MegaList.xls"
        .Open
    End With

    Set rs = New ADODB.Recordset

    rs.Open "SELECT * FROM WholeThing", conn, _
            adOpenDynamic, _
            adLockBatchOptimistic, _
            adCmdText
    rs.MoveFirst

    dummy = rs.GetRows(rs.RecordCount)
    ListBox1.Column = dummy

    Debug.Print ListBox1.ListCount, ListBox1.ColumnCount

    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

Immediate window:
 2547          254
I got it all ...



Sun, 07 Sep 2003 01:33:40 GMT  
 ListBox MegaList.xls to Word ADO 2.1

Hi Perry,

So sad to report: No joy in Mudville.

 - It does in fact load ALMOST the entire database, except for the
   last 4 entries - coincidentally (I hope!) the Z's.  I could almost
   live with that, except that:

 - It reads many records incorrectly.  For example, the database has
   only one record for Millview, but the listbox shows 8 of them.
   This happens throughout the list (starting in the A's), with
   adjoining records getting lost in matching numbers.  For example,
   there are 4 records in the database for Millbrae and 3 for Milpitas
   (above and below Millview), none of which appear in the listbox.
   Further, the form's displaying textboxes which, under the old
   method, properly displayed other fields from each record (phone,
   address, etc.) now give the wrong fields in many (even most) cases.

I evidently have ADO 2.0, 2.1, and 2.5 on this machine.  I tried it
with each one.

I can send you the userform if you like.  But please don't feel held
to your "Never give up!" motto, because this effort seems like it may
be, if not doomed, at least not all it's cracked up to be SPEED-wise.
That is, I've noticed, on all of these last several attempts, that the
ADO method, whether it works or not, is actually SLOWER for this file!

That's a bit of a puzzle, to be sure, since my little phone list in the
earlier userform (same kind of task - populating a listbox) went from
a 3- or 4-second load using Excel Automation to a nearly instantaneous
load when switched to DAO.  But this 2500-line file generally takes at
least 7-8 seconds to get listboxified using ADO, but never more than 5
seconds the old way, via Excel automation.

Pant, wheeze, etc.

Peter

Quote:

> Hi Peter,

> The below coding absolutely worked fine for me.
> I referenced in Word VBA to ActiveX Data Objects 2.0 Lib
> and believe this to part of Office 97 ...
> This was printed in Immediate window after initializing the UserForm:
>  2547          254

> Siuation: UserForm containing a 254 columned ListBox, named: ListBox1.
> Excel workbook: MegaList.xls in c\Temp
> Named range: WholeThing (covering the entire data region)

> And the rest is coded below ...
> Again, worked perfectly.

> I believe you can copy/paste below code into your project, noticing
> that you will only have to adjust the pathname leading to the target XLS.
> Make sure your project is pointing to ADO 2.0 at least
> Better 2.1 or 2.5 ...

> If still having problems, repost ...
> Not giving up, never !

> Krgrds,
> Perry

> Here goes the code:

> Private Sub UserForm_Initialize()
>     Dim conn As ADODB.Connection
>     Dim rs As ADODB.Recordset
>     Dim x As Long
>     Dim dummy As Variant

>     Set conn = New ADODB.Connection
>     With conn
>         .ConnectionString = "Provider=MSDASQL;" & _
>                 "Driver={Microsoft Excel Driver (*.xls)};" & _
>                 "DBQ=c:\temp\MegaList.xls"
>         .Open
>     End With

>     Set rs = New ADODB.Recordset

>     rs.Open "SELECT * FROM WholeThing", conn, _
>             adOpenDynamic, _
>             adLockBatchOptimistic, _
>             adCmdText
>     rs.MoveFirst

>     dummy = rs.GetRows(rs.RecordCount)
>     ListBox1.Column = dummy

>     Debug.Print ListBox1.ListCount, ListBox1.ColumnCount

>     rs.Close
>     Set rs = Nothing
>     conn.Close
>     Set conn = Nothing
> End Sub

> Immediate window:
>  2547          254
> I got it all ...



Sun, 07 Sep 2003 12:50:20 GMT  
 ListBox MegaList.xls to Word ADO 2.1
Quote:
> and adding a field (column A)

With a unique ID number (1 through to 2548)

Krgrds,
Per(severe)



Quote:
> Hi Peter,

> Ok, I'll have a look at the ADO/Excel way. Presume that ADO is giving
> you troubles because there's no uniqie field in the database.

> These are the debug print results, making the ListBox a 35 column listbox
> and adding a field (column A) in the Excel Worksheet, adjusting the named
> range to fit the new database. The code remains as is, except I had
> GetTickCount
> print the required time for reading ... A mere 1.3 second action ....
> ADO 2.5, on a PIII/550 machine, mind you

> Results:

> Populating listbox executed in: (milliseconds)
>  1342
> *************
> Last 4 entries, column 2, and 3
> Zone 7 Water Agency         Vincent Wong
> Zone 7 Water Agency         Dennis H. Gambs
> Zone 7 Water Agency         Dale Myers
> Zahn Group    Elyce Zahn
> *************
> All entries Alameda
> Alameda       City Manager
> Alameda       Director of Public Works
> Alameda       Finance Director
> *************

> This was the code I added to produce above results:
>     Debug.Print "Last 4 entries, column 2, and 3"
>     iList = ListBox1.ListCount
>     Do Until x = 4
>         iList = iList - 1
>         Debug.Print ListBox1.List(iList, 1), ListBox1.List(iList, 2)
>         x = x + 1
>     Loop
>     Debug.Print "*************"

>     Debug.Print "All entries Alameda"
>     For x = 0 To ListBox1.ListCount - 1
>         If ListBox1.List(x, 1) = "Alameda" Then
>             Debug.Print ListBox1.List(x, 1), ListBox1.List(x, 3)
>             If ListBox1.List(x + 1, 1) <> "Alameda" Then Exit For
>         End If
>     Next
>     Debug.Print "*************"

> Krgrds,
> Per(severe)



> > Hi Perry,

> > So sad to report: No joy in Mudville.

> >  - It does in fact load ALMOST the entire database, except for the
> >    last 4 entries - coincidentally (I hope!) the Z's.  I could almost
> >    live with that, except that:

> >  - It reads many records incorrectly.  For example, the database has
> >    only one record for Millview, but the listbox shows 8 of them.
> >    This happens throughout the list (starting in the A's), with
> >    adjoining records getting lost in matching numbers.  For example,
> >    there are 4 records in the database for Millbrae and 3 for Milpitas
> >    (above and below Millview), none of which appear in the listbox.
> >    Further, the form's displaying textboxes which, under the old
> >    method, properly displayed other fields from each record (phone,
> >    address, etc.) now give the wrong fields in many (even most) cases.

> > I evidently have ADO 2.0, 2.1, and 2.5 on this machine.  I tried it
> > with each one.

> > I can send you the userform if you like.  But please don't feel held
> > to your "Never give up!" motto, because this effort seems like it may
> > be, if not doomed, at least not all it's cracked up to be SPEED-wise.
> > That is, I've noticed, on all of these last several attempts, that the
> > ADO method, whether it works or not, is actually SLOWER for this file!

> > That's a bit of a puzzle, to be sure, since my little phone list in the
> > earlier userform (same kind of task - populating a listbox) went from
> > a 3- or 4-second load using Excel Automation to a nearly instantaneous
> > load when switched to DAO.  But this 2500-line file generally takes at
> > least 7-8 seconds to get listboxified using ADO, but never more than 5
> > seconds the old way, via Excel automation.

> > Pant, wheeze, etc.

> > Peter


> > > Hi Peter,

> > > The below coding absolutely worked fine for me.
> > > I referenced in Word VBA to ActiveX Data Objects 2.0 Lib
> > > and believe this to part of Office 97 ...
> > > This was printed in Immediate window after initializing the UserForm:
> > >  2547          254

> > > Siuation: UserForm containing a 254 columned ListBox, named: ListBox1.
> > > Excel workbook: MegaList.xls in c\Temp
> > > Named range: WholeThing (covering the entire data region)

> > > And the rest is coded below ...
> > > Again, worked perfectly.

> > > I believe you can copy/paste below code into your project, noticing
> > > that you will only have to adjust the pathname leading to the target
> XLS.
> > > Make sure your project is pointing to ADO 2.0 at least
> > > Better 2.1 or 2.5 ...

> > > If still having problems, repost ...
> > > Not giving up, never !

> > > Krgrds,
> > > Perry

> > > Here goes the code:

> > > Private Sub UserForm_Initialize()
> > >     Dim conn As ADODB.Connection
> > >     Dim rs As ADODB.Recordset
> > >     Dim x As Long
> > >     Dim dummy As Variant

> > >     Set conn = New ADODB.Connection
> > >     With conn
> > >         .ConnectionString = "Provider=MSDASQL;" & _
> > >                 "Driver={Microsoft Excel Driver (*.xls)};" & _
> > >                 "DBQ=c:\temp\MegaList.xls"
> > >         .Open
> > >     End With

> > >     Set rs = New ADODB.Recordset

> > >     rs.Open "SELECT * FROM WholeThing", conn, _
> > >             adOpenDynamic, _
> > >             adLockBatchOptimistic, _
> > >             adCmdText
> > >     rs.MoveFirst

> > >     dummy = rs.GetRows(rs.RecordCount)
> > >     ListBox1.Column = dummy

> > >     Debug.Print ListBox1.ListCount, ListBox1.ColumnCount

> > >     rs.Close
> > >     Set rs = Nothing
> > >     conn.Close
> > >     Set conn = Nothing
> > > End Sub

> > > Immediate window:
> > >  2547          254
> > > I got it all ...



Sun, 07 Sep 2003 14:51:04 GMT  
 ListBox MegaList.xls to Word ADO 2.1
Hi Peter,

Ok, I'll have a look at the ADO/Excel way. Presume that ADO is giving
you troubles because there's no uniqie field in the database.

These are the debug print results, making the ListBox a 35 column listbox
and adding a field (column A) in the Excel Worksheet, adjusting the named
range to fit the new database. The code remains as is, except I had
GetTickCount
print the required time for reading ... A mere 1.3 second action ....
ADO 2.5, on a PIII/550 machine, mind you

Results:

Populating listbox executed in: (milliseconds)
 1342
*************
Last 4 entries, column 2, and 3
Zone 7 Water Agency         Vincent Wong
Zone 7 Water Agency         Dennis H. Gambs
Zone 7 Water Agency         Dale Myers
Zahn Group    Elyce Zahn
*************
All entries Alameda
Alameda       City Manager
Alameda       Director of Public Works
Alameda       Finance Director
*************

This was the code I added to produce above results:
    Debug.Print "Last 4 entries, column 2, and 3"
    iList = ListBox1.ListCount
    Do Until x = 4
        iList = iList - 1
        Debug.Print ListBox1.List(iList, 1), ListBox1.List(iList, 2)
        x = x + 1
    Loop
    Debug.Print "*************"

    Debug.Print "All entries Alameda"
    For x = 0 To ListBox1.ListCount - 1
        If ListBox1.List(x, 1) = "Alameda" Then
            Debug.Print ListBox1.List(x, 1), ListBox1.List(x, 3)
            If ListBox1.List(x + 1, 1) <> "Alameda" Then Exit For
        End If
    Next
    Debug.Print "*************"

Krgrds,
Per(severe)



Quote:

> Hi Perry,

> So sad to report: No joy in Mudville.

>  - It does in fact load ALMOST the entire database, except for the
>    last 4 entries - coincidentally (I hope!) the Z's.  I could almost
>    live with that, except that:

>  - It reads many records incorrectly.  For example, the database has
>    only one record for Millview, but the listbox shows 8 of them.
>    This happens throughout the list (starting in the A's), with
>    adjoining records getting lost in matching numbers.  For example,
>    there are 4 records in the database for Millbrae and 3 for Milpitas
>    (above and below Millview), none of which appear in the listbox.
>    Further, the form's displaying textboxes which, under the old
>    method, properly displayed other fields from each record (phone,
>    address, etc.) now give the wrong fields in many (even most) cases.

> I evidently have ADO 2.0, 2.1, and 2.5 on this machine.  I tried it
> with each one.

> I can send you the userform if you like.  But please don't feel held
> to your "Never give up!" motto, because this effort seems like it may
> be, if not doomed, at least not all it's cracked up to be SPEED-wise.
> That is, I've noticed, on all of these last several attempts, that the
> ADO method, whether it works or not, is actually SLOWER for this file!

> That's a bit of a puzzle, to be sure, since my little phone list in the
> earlier userform (same kind of task - populating a listbox) went from
> a 3- or 4-second load using Excel Automation to a nearly instantaneous
> load when switched to DAO.  But this 2500-line file generally takes at
> least 7-8 seconds to get listboxified using ADO, but never more than 5
> seconds the old way, via Excel automation.

> Pant, wheeze, etc.

> Peter


> > Hi Peter,

> > The below coding absolutely worked fine for me.
> > I referenced in Word VBA to ActiveX Data Objects 2.0 Lib
> > and believe this to part of Office 97 ...
> > This was printed in Immediate window after initializing the UserForm:
> >  2547          254

> > Siuation: UserForm containing a 254 columned ListBox, named: ListBox1.
> > Excel workbook: MegaList.xls in c\Temp
> > Named range: WholeThing (covering the entire data region)

> > And the rest is coded below ...
> > Again, worked perfectly.

> > I believe you can copy/paste below code into your project, noticing
> > that you will only have to adjust the pathname leading to the target
XLS.
> > Make sure your project is pointing to ADO 2.0 at least
> > Better 2.1 or 2.5 ...

> > If still having problems, repost ...
> > Not giving up, never !

> > Krgrds,
> > Perry

> > Here goes the code:

> > Private Sub UserForm_Initialize()
> >     Dim conn As ADODB.Connection
> >     Dim rs As ADODB.Recordset
> >     Dim x As Long
> >     Dim dummy As Variant

> >     Set conn = New ADODB.Connection
> >     With conn
> >         .ConnectionString = "Provider=MSDASQL;" & _
> >                 "Driver={Microsoft Excel Driver (*.xls)};" & _
> >                 "DBQ=c:\temp\MegaList.xls"
> >         .Open
> >     End With

> >     Set rs = New ADODB.Recordset

> >     rs.Open "SELECT * FROM WholeThing", conn, _
> >             adOpenDynamic, _
> >             adLockBatchOptimistic, _
> >             adCmdText
> >     rs.MoveFirst

> >     dummy = rs.GetRows(rs.RecordCount)
> >     ListBox1.Column = dummy

> >     Debug.Print ListBox1.ListCount, ListBox1.ColumnCount

> >     rs.Close
> >     Set rs = Nothing
> >     conn.Close
> >     Set conn = Nothing
> > End Sub

> > Immediate window:
> >  2547          254
> > I got it all ...



Sun, 07 Sep 2003 14:48:13 GMT  
 ListBox MegaList.xls to Word ADO 2.1

Perry,

No real change.  I still don't get the Z's as the last 4 entries in the
immediates window.  (I get 4 duplicate entries for Yuima - there's just
one in the database - in both the listbox and the immediates window.)

I added the extra column A with the unique IDs & readjusted the range.
(Aside: Is the unique ID per record typical/required for ADO to work?  
If so, could it go at the far right instead?  I dread recoding all the
fields that the other textboxes display.)

Even the Immediates window often lies - but not consistently.  Using
your exact Debug code but changing the text to search for:

  Adelanto: 4 entries in listbox, 4 in immediates, 3 actual in DB
  Millview: 8 entries in listbox, 8 in immediates, 1 actual in DB
  Mission Viejo: 7 entries in listbox, 7 in immediates, 3 actual in DB

In most cases, the number of extra bogus entries in the listbox and
(often) the immediates window is related to the number of adjacent
records (in the DB) that have non-null Field 6's (now 7) (as discussed)
(the green column), OR other similar "interruptions" in columns of
cells.  [God, my head hurts.]   By "interruptions" I mean a null cell
in a column where that cell has been non-null for the past several
records, OR vice versa.  But it isn't always the adjacent records below
or always those above.  For Millview, it's both!  Auuugh.

We could stop now, ;-)  unless you really do need to nail this and/or
maybe want to check the userform code.

Peter

Quote:

> > and adding a field (column A)
> With a unique ID number (1 through to 2548)

> Krgrds,
> Per(severe)



> > Hi Peter,

> > Ok, I'll have a look at the ADO/Excel way. Presume that ADO is giving
> > you troubles because there's no uniqie field in the database.

> > These are the debug print results, making the ListBox a 35 column listbox
> > and adding a field (column A) in the Excel Worksheet, adjusting the named
> > range to fit the new database. The code remains as is, except I had
> > GetTickCount
> > print the required time for reading ... A mere 1.3 second action ....
> > ADO 2.5, on a PIII/550 machine, mind you

> > Results:

> > Populating listbox executed in: (milliseconds)
> >  1342
> > *************
> > Last 4 entries, column 2, and 3
> > Zone 7 Water Agency         Vincent Wong
> > Zone 7 Water Agency         Dennis H. Gambs
> > Zone 7 Water Agency         Dale Myers
> > Zahn Group    Elyce Zahn
> > *************
> > All entries Alameda
> > Alameda       City Manager
> > Alameda       Director of Public Works
> > Alameda       Finance Director
> > *************

> > This was the code I added to produce above results:
> >     Debug.Print "Last 4 entries, column 2, and 3"
> >     iList = ListBox1.ListCount
> >     Do Until x = 4
> >         iList = iList - 1
> >         Debug.Print ListBox1.List(iList, 1), ListBox1.List(iList, 2)
> >         x = x + 1
> >     Loop
> >     Debug.Print "*************"

> >     Debug.Print "All entries Alameda"
> >     For x = 0 To ListBox1.ListCount - 1
> >         If ListBox1.List(x, 1) = "Alameda" Then
> >             Debug.Print ListBox1.List(x, 1), ListBox1.List(x, 3)
> >             If ListBox1.List(x + 1, 1) <> "Alameda" Then Exit For
> >         End If
> >     Next
> >     Debug.Print "*************"

> > Krgrds,
> > Per(severe)



> > > Hi Perry,

> > > So sad to report: No joy in Mudville.

> > >  - It does in fact load ALMOST the entire database, except for the
> > >    last 4 entries - coincidentally (I hope!) the Z's.  I could almost
> > >    live with that, except that:

> > >  - It reads many records incorrectly.  For example, the database has
> > >    only one record for Millview, but the listbox shows 8 of them.
> > >    This happens throughout the list (starting in the A's), with
> > >    adjoining records getting lost in matching numbers.  For example,
> > >    there are 4 records in the database for Millbrae and 3 for Milpitas
> > >    (above and below Millview), none of which appear in the listbox.
> > >    Further, the form's displaying textboxes which, under the old
> > >    method, properly displayed other fields from each record (phone,
> > >    address, etc.) now give the wrong fields in many (even most) cases.

> > > I evidently have ADO 2.0, 2.1, and 2.5 on this machine.  I tried it
> > > with each one.

> > > I can send you the userform if you like.  But please don't feel held
> > > to your "Never give up!" motto, because this effort seems like it may
> > > be, if not doomed, at least not all it's cracked up to be SPEED-wise.
> > > That is, I've noticed, on all of these last several attempts, that the
> > > ADO method, whether it works or not, is actually SLOWER for this file!

> > > That's a bit of a puzzle, to be sure, since my little phone list in the
> > > earlier userform (same kind of task - populating a listbox) went from
> > > a 3- or 4-second load using Excel Automation to a nearly instantaneous
> > > load when switched to DAO.  But this 2500-line file generally takes at
> > > least 7-8 seconds to get listboxified using ADO, but never more than 5
> > > seconds the old way, via Excel automation.

> > > Pant, wheeze, etc.

> > > Peter


> > > > Hi Peter,

> > > > The below coding absolutely worked fine for me.
> > > > I referenced in Word VBA to ActiveX Data Objects 2.0 Lib
> > > > and believe this to part of Office 97 ...
> > > > This was printed in Immediate window after initializing the UserForm:
> > > >  2547          254

> > > > Siuation: UserForm containing a 254 columned ListBox, named: ListBox1.
> > > > Excel workbook: MegaList.xls in c\Temp
> > > > Named range: WholeThing (covering the entire data region)

> > > > And the rest is coded below ...
> > > > Again, worked perfectly.

> > > > I believe you can copy/paste below code into your project, noticing
> > > > that you will only have to adjust the pathname leading to the target
> > XLS.
> > > > Make sure your project is pointing to ADO 2.0 at least
> > > > Better 2.1 or 2.5 ...

> > > > If still having problems, repost ...
> > > > Not giving up, never !

> > > > Krgrds,
> > > > Perry

> > > > Here goes the code:

> > > > Private Sub UserForm_Initialize()
> > > >     Dim conn As ADODB.Connection
> > > >     Dim rs As ADODB.Recordset
> > > >     Dim x As Long
> > > >     Dim dummy As Variant

> > > >     Set conn = New ADODB.Connection
> > > >     With conn
> > > >         .ConnectionString = "Provider=MSDASQL;" & _
> > > >                 "Driver={Microsoft Excel Driver (*.xls)};" & _
> > > >                 "DBQ=c:\temp\MegaList.xls"
> > > >         .Open
> > > >     End With

> > > >     Set rs = New ADODB.Recordset

> > > >     rs.Open "SELECT * FROM WholeThing", conn, _
> > > >             adOpenDynamic, _
> > > >             adLockBatchOptimistic, _
> > > >             adCmdText
> > > >     rs.MoveFirst

> > > >     dummy = rs.GetRows(rs.RecordCount)
> > > >     ListBox1.Column = dummy

> > > >     Debug.Print ListBox1.ListCount, ListBox1.ColumnCount

> > > >     rs.Close
> > > >     Set rs = Nothing
> > > >     conn.Close
> > > >     Set conn = Nothing
> > > > End Sub

> > > > Immediate window:
> > > >  2547          254
> > > > I got it all ...



Sun, 07 Sep 2003 18:26:13 GMT  
 ListBox MegaList.xls to Word ADO 2.1
Hi Peter,

At least we tried ;-))

But oke, I won't make another attempt here, sorry pal ...
Yet, it puzzles me why the 'whole thing' works perfectly here
and when you try to simulate, it fails.
Must be that dam..d phantom ....

I'd stick to the automation method until you've figured it out.

Quote:
> I added the extra column A with the unique IDs & readjusted the range.
> (Aside: Is the unique ID per record typical/required for ADO to work?

I've succeeded in populating the listbox without, so from that point of view
it appears not to be required.
Yet, don't think you can't take this for granted.

Quote:
> Is the unique ID per record typical/required for ADO to work?

I'll run through the books to get this statement supported ... or not

Krgrds,
Perry



Quote:

> Perry,

> No real change.  I still don't get the Z's as the last 4 entries in the
> immediates window.  (I get 4 duplicate entries for Yuima - there's just
> one in the database - in both the listbox and the immediates window.)

> I added the extra column A with the unique IDs & readjusted the range.
> (Aside: Is the unique ID per record typical/required for ADO to work?
> If so, could it go at the far right instead?  I dread recoding all the
> fields that the other textboxes display.)

> Even the Immediates window often lies - but not consistently.  Using
> your exact Debug code but changing the text to search for:

>   Adelanto: 4 entries in listbox, 4 in immediates, 3 actual in DB
>   Millview: 8 entries in listbox, 8 in immediates, 1 actual in DB
>   Mission Viejo: 7 entries in listbox, 7 in immediates, 3 actual in DB

> In most cases, the number of extra bogus entries in the listbox and
> (often) the immediates window is related to the number of adjacent
> records (in the DB) that have non-null Field 6's (now 7) (as discussed)
> (the green column), OR other similar "interruptions" in columns of
> cells.  [God, my head hurts.]   By "interruptions" I mean a null cell
> in a column where that cell has been non-null for the past several
> records, OR vice versa.  But it isn't always the adjacent records below
> or always those above.  For Millview, it's both!  Auuugh.

> We could stop now, ;-)  unless you really do need to nail this and/or
> maybe want to check the userform code.

> Peter


> > > and adding a field (column A)
> > With a unique ID number (1 through to 2548)

> > Krgrds,
> > Per(severe)



> > > Hi Peter,

> > > Ok, I'll have a look at the ADO/Excel way. Presume that ADO is giving
> > > you troubles because there's no uniqie field in the database.

> > > These are the debug print results, making the ListBox a 35 column
listbox
> > > and adding a field (column A) in the Excel Worksheet, adjusting the
named
> > > range to fit the new database. The code remains as is, except I had
> > > GetTickCount
> > > print the required time for reading ... A mere 1.3 second action ....
> > > ADO 2.5, on a PIII/550 machine, mind you

> > > Results:

> > > Populating listbox executed in: (milliseconds)
> > >  1342
> > > *************
> > > Last 4 entries, column 2, and 3
> > > Zone 7 Water Agency         Vincent Wong
> > > Zone 7 Water Agency         Dennis H. Gambs
> > > Zone 7 Water Agency         Dale Myers
> > > Zahn Group    Elyce Zahn
> > > *************
> > > All entries Alameda
> > > Alameda       City Manager
> > > Alameda       Director of Public Works
> > > Alameda       Finance Director
> > > *************

> > > This was the code I added to produce above results:
> > >     Debug.Print "Last 4 entries, column 2, and 3"
> > >     iList = ListBox1.ListCount
> > >     Do Until x = 4
> > >         iList = iList - 1
> > >         Debug.Print ListBox1.List(iList, 1), ListBox1.List(iList, 2)
> > >         x = x + 1
> > >     Loop
> > >     Debug.Print "*************"

> > >     Debug.Print "All entries Alameda"
> > >     For x = 0 To ListBox1.ListCount - 1
> > >         If ListBox1.List(x, 1) = "Alameda" Then
> > >             Debug.Print ListBox1.List(x, 1), ListBox1.List(x, 3)
> > >             If ListBox1.List(x + 1, 1) <> "Alameda" Then Exit For
> > >         End If
> > >     Next
> > >     Debug.Print "*************"

> > > Krgrds,
> > > Per(severe)



> > > > Hi Perry,

> > > > So sad to report: No joy in Mudville.

> > > >  - It does in fact load ALMOST the entire database, except for the
> > > >    last 4 entries - coincidentally (I hope!) the Z's.  I could
almost
> > > >    live with that, except that:

> > > >  - It reads many records incorrectly.  For example, the database has
> > > >    only one record for Millview, but the listbox shows 8 of them.
> > > >    This happens throughout the list (starting in the A's), with
> > > >    adjoining records getting lost in matching numbers.  For example,
> > > >    there are 4 records in the database for Millbrae and 3 for
Milpitas
> > > >    (above and below Millview), none of which appear in the listbox.
> > > >    Further, the form's displaying textboxes which, under the old
> > > >    method, properly displayed other fields from each record (phone,
> > > >    address, etc.) now give the wrong fields in many (even most)
cases.

> > > > I evidently have ADO 2.0, 2.1, and 2.5 on this machine.  I tried it
> > > > with each one.

> > > > I can send you the userform if you like.  But please don't feel held
> > > > to your "Never give up!" motto, because this effort seems like it
may
> > > > be, if not doomed, at least not all it's cracked up to be
SPEED-wise.
> > > > That is, I've noticed, on all of these last several attempts, that
the
> > > > ADO method, whether it works or not, is actually SLOWER for this
file!

> > > > That's a bit of a puzzle, to be sure, since my little phone list in
the
> > > > earlier userform (same kind of task - populating a listbox) went
from
> > > > a 3- or 4-second load using Excel Automation to a nearly
instantaneous
> > > > load when switched to DAO.  But this 2500-line file generally takes
at
> > > > least 7-8 seconds to get listboxified using ADO, but never more than
5
> > > > seconds the old way, via Excel automation.

> > > > Pant, wheeze, etc.

> > > > Peter


> > > > > Hi Peter,

> > > > > The below coding absolutely worked fine for me.
> > > > > I referenced in Word VBA to ActiveX Data Objects 2.0 Lib
> > > > > and believe this to part of Office 97 ...
> > > > > This was printed in Immediate window after initializing the
UserForm:
> > > > >  2547          254

> > > > > Siuation: UserForm containing a 254 columned ListBox, named:
ListBox1.
> > > > > Excel workbook: MegaList.xls in c\Temp
> > > > > Named range: WholeThing (covering the entire data region)

> > > > > And the rest is coded below ...
> > > > > Again, worked perfectly.

> > > > > I believe you can copy/paste below code into your project,
noticing
> > > > > that you will only have to adjust the pathname leading to the
target
> > > XLS.
> > > > > Make sure your project is pointing to ADO 2.0 at least
> > > > > Better 2.1 or 2.5 ...

> > > > > If still having problems, repost ...
> > > > > Not giving up, never !

> > > > > Krgrds,
> > > > > Perry

> > > > > Here goes the code:

> > > > > Private Sub UserForm_Initialize()
> > > > >     Dim conn As ADODB.Connection
> > > > >     Dim rs As ADODB.Recordset
> > > > >     Dim x As Long
> > > > >     Dim dummy As Variant

> > > > >     Set conn = New ADODB.Connection
> > > > >     With conn
> > > > >         .ConnectionString = "Provider=MSDASQL;" & _
> > > > >                 "Driver={Microsoft Excel Driver (*.xls)};" & _
> > > > >                 "DBQ=c:\temp\MegaList.xls"
> > > > >         .Open
> > > > >     End With

> > > > >     Set rs = New ADODB.Recordset

> > > > >     rs.Open "SELECT * FROM WholeThing", conn, _
> > > > >             adOpenDynamic, _
> > > > >             adLockBatchOptimistic, _
> > > > >             adCmdText
> > > > >     rs.MoveFirst

> > > > >     dummy = rs.GetRows(rs.RecordCount)
> > > > >     ListBox1.Column = dummy

> > > > >     Debug.Print ListBox1.ListCount, ListBox1.ColumnCount

> > > > >     rs.Close
> > > > >     Set rs = Nothing
> > > > >     conn.Close
> > > > >     Set conn = Nothing
> > > > > End Sub

> > > > > Immediate window:
> > > > >  2547          254
> > > > > I got it all ...



Mon, 08 Sep 2003 03:04:16 GMT  
 ListBox MegaList.xls to Word ADO 2.1

Hi Perry,

Quote:
> But oke, I won't make another attempt here, sorry pal ...
> Yet, it puzzles me why the 'whole thing' works perfectly here
> and when you try to simulate, it fails.

Puzzles me too.  Again, if you like I can send you the userform and
you can see it malfunction exactly (one would hope!) as described.
But only if you want.

Thanks for all the effort.  I did learn a lot, regardless.

Peter



Mon, 08 Sep 2003 16:21:34 GMT  
 ListBox MegaList.xls to Word ADO 2.1
Hi Peter,

Let's give it a shot ...
Mail it to me

Krgrds,
Perry



Quote:

> Hi Perry,

> > But oke, I won't make another attempt here, sorry pal ...
> > Yet, it puzzles me why the 'whole thing' works perfectly here
> > and when you try to simulate, it fails.

> Puzzles me too.  Again, if you like I can send you the userform and
> you can see it malfunction exactly (one would hope!) as described.
> But only if you want.

> Thanks for all the effort.  I did learn a lot, regardless.

> Peter



Tue, 09 Sep 2003 03:08:08 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. ADO Control against ADO 2.1 and ADO 2.0

2. Does ADO 2.5 or up backward compatible with ADO 2.1

3. ADO 2.5 and ADO 2.1 compatibility

4. Upgrading from ADO 2.1 to ADO 2.5

5. ADO data control 6.0 and ADO data object 2.1 conflict

6. ADO Date Fields (ADO 2.1, VB 6, Oracle 8)

7. Problems with ado 2.1 module

8. VB6, ADO 2.1 and MSDE (Access 2000)

9. ADO 2.1, JET & pessimistic locking

10. ADO 2.1 Access 97 and AutoNumber

11. Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

12. Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

 

 
Powered by phpBB® Forum Software