Solved

Opening a second recordset in the same procedure

Posted on 2002-06-01
20
153 Views
Last Modified: 2013-11-26
'This recordset contains all details of competitors and their scores

    Dim strFilePath As String: strFilePath = App.Path & "\MayDay.mdb"
   
    With AdoShots
        .ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
            "Data Source=" & strFilePath & ";"
        .RecordSource = "SELECT * FROM [MayDay] " & _
                        "ORDER BY [Shot] asc, [ID] asc"
        .Refresh
    End With

'--------------------------------------------------------

   'In the same procedure (ie without putting in an extra command button)
   'I want the program to go on to show and calculate the Pairs competition
   'from the same database, but using and displaying only four of the fields.
   'I have set up an AdoDC called AdoShots on the form, as well as a DataGrid
   'which is fed by AdoShots.
   
   'This is what I want to say, but VB6 is not happy about it.
   'What crucial code have I omitted?

   
    AdoShots.Recordset.Close
    With AdoPairs
        .ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
            "Data Source=" & strFilePath & ";"
        .RecordSource = "SELECT Shot, PairLetter, PairScore, ABCDEF FROM [MayDay] " & _
              "WHERE PairLetter <> null " & _
              "(left(PairLetter,1)) >='A' and " & _
              "left(PairLetter,1)) <='Z') or " & _
              "(left(PairLetter,1)) >='a' and " & _
              "left(PairLetter,1)) <='z') " & _
              "ORDER BY left(PairLetter,1), Shot"
    End With

   'If I use "With AdoShots" for the Pairs calculations instead of
   '"With AdoPairs", the calculations which follow are carried out perfectly.  
   'But at the end the datagrid is shown as a single empty row and and
   '26 empty columns (instead of 147 filled rows by 26 columns).

Regards,

GeoffO
0
Comment
Question by:GeoffO
  • 11
  • 5
  • 2
  • +1
20 Comments
 
LVL 4

Expert Comment

by:mcoop
Comment Utility
first trick

you can't test null with relational operators
you have to use something like...

  WHERE PairLetter IS NOT null " & _
0
 

Author Comment

by:GeoffO
Comment Utility
Thanks for the suggestion, mcoop, but the SQL has worked well enough using the original AdoShots Recordset.

Marvellous, isn’t it, that we don’t have the same syntax in VB and SQL, eg ‘Not IsNull(PairLetter)’ and ‘PairLetter Is Not Null’.  Why do they make life unnecessarily complicated?

I am getting “Run-time error 3709: Operation is not allowed on an object referencing a closed or invalid connection”.

So it’s the syntax for setting up the second (AdoPairs) recordset that I really need.

GeoffO
0
 
LVL 3

Expert Comment

by:MCummings111400
Comment Utility
Is the connection string that you are using for ADOPairs identical to that of ADOShots?
0
 
LVL 4

Expert Comment

by:mcoop
Comment Utility
I must admit also that I gave up using the data controls about 15 minutes after first starting to look at them, as I always found that they painted me into a corner somewhere later in the code.

Declare, open and use your own adodb.connection and recordsets - it is MUCH more flexible, and you always know where the data 'is, was and will be' !

Good luck

0
 

Author Comment

by:GeoffO
Comment Utility
Good morning mcoop and MCummings.

mcoop: OK, I’ll go with that general principle if you will now give me the syntax specific to this case.  Please write all the necessary lines of code to enable me to do the job.

MCummings: I believe the code in my question shows that the connection string is identical in both, eg

    .ConnectionString = _
           "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Persist Security Info=False;" & _
           "Data Source=" & strFilePath & ";"

Are you saying that they should be the same or not?

Regards,

GeoffO
0
 
LVL 1

Expert Comment

by:crw030
Comment Utility
Geoff.  Couple things.  I setup a working example 2 different ways.  When I did the first I uncovered an SQL error (I think) because you have way to many left-parens in the SQL statement you posted.

Here's what I did.
---Created form with 6 controls.  adoDC (ADODC Control), adoDC2 (ADODC control), adoShots (DataGrid), adoPairs (Datagrid), and 2 Command buttons (just to test separate ways, you would only need to pick the 1 you prefer)

Here's the code:

Private Sub Command1_Click()

   Dim strFilePath As String: strFilePath = App.Path & "\MayDay.mdb"

    With adoDC
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Persist Security Info=False;" & _
           "Data Source=" & strFilePath & ";"
        .RecordSource = "SELECT * FROM [MayDay] " & _
                       "ORDER BY [Shot] asc, [ID] asc"
        .Refresh
    End With
 
  With adoDC2
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Persist Security Info=False;" & _
           "Data Source=" & strFilePath & ";"
        .RecordSource = "SELECT Shot, PairLetter, PairScore, ABCDEF FROM [MayDay] " & _
             "WHERE PairLetter <> null and" & _
             "(left(PairLetter,1) >='A' and " & _
             "left(PairLetter,1) <='Z') or " & _
             "(left(PairLetter,1) >='a' and " & _
             "left(PairLetter,1) <='z') " & _
             "ORDER BY left(PairLetter,1), Shot"
       .Refresh
  End With
End Sub

Private Sub Command2_Click()
    Dim strFilePath As String
   Dim cn1 As ADODB.Connection
   Dim rs1 As ADODB.Recordset
   Dim rs2 As ADODB.Recordset
   
   
   strFilePath = App.Path & "\MayDay.mdb"
   Set cn1 = New ADODB.Connection
   With cn1
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
           "Data Source=" & strFilePath & ";"
    .CursorLocation = adUseClient
    .Open
   End With
   
   Set rs1 = New ADODB.Recordset
   With rs1
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .ActiveConnection = cn1
    .Source = "SELECT * FROM [MayDay] " & _
                       "ORDER BY [Shot] asc, [ID] asc"
    .Open
    .ActiveConnection = Nothing
   End With
   
   Set rs2 = New ADODB.Recordset
   With rs2
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .ActiveConnection = cn1
    .Source = "SELECT Shot, PairLetter, PairScore, ABCDEF FROM [MayDay] " & _
             "WHERE PairLetter <> null and" & _
             "(left(PairLetter,1) >='A' and " & _
             "left(PairLetter,1) <='Z') or " & _
             "(left(PairLetter,1) >='a' and " & _
             "left(PairLetter,1) <='z') " & _
             "ORDER BY left(PairLetter,1), Shot"
    .Open
    .ActiveConnection = Nothing
   End With
   
   Set adoDC.Recordset = rs1
   Set adoDC2.Recordset = rs2
   
   
    Set rs1 = Nothing
    Set rs2 = Nothing


End Sub

Notice I changed your SQL slightly to correct the number of parens.  (You'll have to check that it does what you want)

Command1--Causes the ADODC, ADODC2 controls to open connections to the database and I can scroll through the records using the appropriate control (ADODC scrolls the adoShots, ADODC2 scrolls the adoPairs)
**Note** I think using the adoDC controls uses more resources (2 connections per control I think) than doing it way #2.

Command2-- Opens a connection and 2 recordsets with the appropriate commands.  I chose STATIC recordsets, which I then disconnected from the database by setting the ActiveConnection=Nothing.  This may or may not work for your application (you didnt post whether updating was required etc)  I set the ADODC, ADODC2 controls recordsets equal to the disconnected recordsets and I can scroll just like with Command1.

There are alot smarter guys on this board, and there may be some pitfalls to doing it these ways I don't know about.  But I think the 2nd method doesn't reqiure any permanent connections to the database which may be a performance "good thing" depending on your application.
0
 

Author Comment

by:GeoffO
Comment Utility
Hello crw030,

That looks interesting.  I shall give it a try.

Regards,

GeoffO
0
 

Author Comment

by:GeoffO
Comment Utility
crw030, thanks for your code.  

Because I bring out VB only about once every six months there is a great deal I dont know about all the little bits and pieces that seem to be needed for setting up recordsets in code, rather than setting it up more or less visually on the form (which my how-to-do-it books told me to do).

This is my interpretation of what you gave me.  It comes unstuck when I try to use the first ADODC to update the database  says it cant do it (error message shown below in the code).

The overall scheme of the program is that people shoot three cards at 50 metres and three at 100 yards.  The results of these six cards are processed to give results for individuals in four classes, for pairs, teams of four, teams of six, ladies, juniors, veterans etc.

Private Sub cmdCalculate_Click()
   
    Dim A, B, C, D, E, F, ABC, DEF, AD, BCEF, ABCDEF As Integer
    Dim PairLetter, PairLetterCalculated, PairShot1, PairShot2, PairsCalculated As String
    Dim PairScore, PairScoreTotal, PairMember1Score, PairMember2Score As Integer
    Dim Shot, CompletedPairLetters As String
    Dim strFilePath As String: strFilePath = App.Path & "\MayDay.mdb"
    Dim cn1 As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset

    Set cn1 = New ADODB.Connection
    With cn1
        .ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
            "Data Source=" & strFilePath & ";"
        .CursorLocation = adUseClient
        .Open
    End With
   
    Set rs1 = New ADODB.Recordset
    With rs1
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
        .ActiveConnection = cn1
        .Source = "SELECT * FROM [MayDay] " & _
                  "ORDER BY [Shot] asc, [ID] asc"
        .Open
        .ActiveConnection = Nothing
    End With
   
    Set rs2 = New ADODB.Recordset
    With rs2
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .ActiveConnection = cn1
        .Source = "SELECT Shot, PairLetter, PairScore, ABCDEF FROM [MayDay] " & _
                  "WHERE (asc(PairLetter) >=65 and " & _
                         "asc(PairLetter) <=90) or " & _
                        "(asc(PairLetter) >=97 and " & _
                         "asc(PairLetter) <=122) " & _
                  "ORDER BY PairLetter, Shot"
        .Open
        .ActiveConnection = Nothing
    End With
                 
    Set AdoShots.Recordset = rs1
    Set AdoPairs.Recordset = rs2
   
    Set rs1 = Nothing
    Set rs2 = Nothing
   
    AdoShots.Recordset.MoveFirst
    Do Until AdoShots.Recordset.EOF
        A = Val(AdoShots.Recordset.Fields.Item("A:50m1"))
        If A = 0 And AdoShots.Recordset.Fields.Item("A:50m1") <> "0" Then
            A = 100
        End If
        B = Val(AdoShots.Recordset.Fields.Item("B:50m2"))
        If B = 0 And AdoShots.Recordset.Fields.Item("B:50m2") <> "0" Then
            B = 100
        End If
        C = Val(AdoShots.Recordset.Fields.Item("C:50m3"))
        If C = 0 And AdoShots.Recordset.Fields.Item("C:50m3") <> "0" Then
            C = 100
        End If
        D = Val(AdoShots.Recordset.Fields.Item("D:100yd1"))
        If D = 0 And AdoShots.Recordset.Fields.Item("D:100yd1") <> "0" Then
            D = 100
        End If
        E = Val(AdoShots.Recordset.Fields.Item("E:100yd2"))
        If E = 0 And AdoShots.Recordset.Fields.Item("E:100yd2") <> "0" Then
            E = 100
        End If
        F = Val(AdoShots.Recordset.Fields.Item("F:100yd3"))
        If F = 0 And AdoShots.Recordset.Fields.Item("F:100yd3") <> "0" Then
            F = 100
        End If

        ABCDEF = A + B + C + D + E + F
        AdoShots.Recordset.Update "ABCDEF", ABCDEF
       
        'At this point we get:
        'Run-time error '3251':
        'Object or provider is not capable of performing requested operation
        'ie it cant update the database.
       
        ABC = A + B + C
        AdoShots.Recordset.Update "ABC", ABC
        DEF = D + E + F
        AdoShots.Recordset.Update "DEF", DEF
        AD = A + D
        AdoShots.Recordset.Update "AD", AD
        BCEF = B + C + E + F
        AdoShots.Recordset.Update "BCEF", BCEF
       
        AdoShots.Recordset.MoveNext
    Loop
    AdoShots.Recordset.MoveFirst
   
   'Calculate Pairs
    Do Until AdoPairs.Recordset.EOF
       'Here we go into extracting and combining the scores of people with the same PairLetter, using the second ADODC.

Can you spot where I have gone adrift?

Regards,

GeoffO
0
 
LVL 1

Expert Comment

by:crw030
Comment Utility
Geoff.

I'm sorry. You won't be able to use  a disconnected recordset if you need to update the recordset.  You have 2 choices as I see it: 1. Create a third updateable recordset (not so great), or 2. Don't disconnect the recordsets until you have updated them.

Also.  I don't think you can set rs1 & rs2 Equal to Nothing so early in the code.  If I'm not mistaken when you perform the assignments:
   Set AdoShots.Recordset = rs1
   Set AdoPairs.Recordset = rs2
Your just sharing the reference of the two recordsets (not actually copying them) So when you SET rs1,rs2=Nothing you will lose your recordsets.  (At least thats how it works if you Set rsA=rs1 then set rs1=Nothing.  Not 100% sure on the ADODC)

Remove the references where .ActiveConnection=Nothing

let me know.
0
 

Author Comment

by:GeoffO
Comment Utility
Good morning crw030,

I have remmed out both instances of .ActiveConnection=Nothing, but unfortunately am still getting “Run-time error '3251': Object or provider is not capable of performing requested operation”.

Regards,

Geoff
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Expert Comment

by:MCummings111400
Comment Utility
...
                 
   Set AdoShots.Recordset = rs1
   Set AdoPairs.Recordset = rs2

   Set rs1 = Nothing
   Set rs2 = Nothing
   
   AdoShots.Recordset.MoveFirst
   Do Until AdoShots.Recordset.EOF
...

Change to

...
                 
   Set AdoShots.Recordset = rs1
   Set AdoPairs.Recordset = rs2

   'Set rs1 = Nothing
   'Set rs2 = Nothing
   
   AdoShots.Recordset.MoveFirst
   Do Until AdoShots.Recordset.EOF
...
0
 

Author Comment

by:GeoffO
Comment Utility
Thanks, MCummings, have tried that but still get run-time error 3251.

The bit of code now reads:

    Dim strFilePath As String: strFilePath = App.Path & "\MayDay.mdb"
    Dim cn1 As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset

    Set cn1 = New ADODB.Connection
    With cn1
        .ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
            "Data Source=" & strFilePath & ";"
        .CursorLocation = adUseClient
        .Open
    End With
   
    Set rs1 = New ADODB.Recordset
    With rs1
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
        .ActiveConnection = cn1
        .Source = "SELECT * FROM [MayDay] " & _
                  "ORDER BY [Shot] asc, [ID] asc"
        .Open
'        .ActiveConnection = Nothing
    End With
   
    Set rs2 = New ADODB.Recordset
    With rs2
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .ActiveConnection = cn1
        .Source = "SELECT Shot, PairLetter, PairScore, ABCDEF FROM [MayDay] " & _
                  "WHERE (asc(PairLetter) >=65 and " & _
                         "asc(PairLetter) <=90) or " & _
                        "(asc(PairLetter) >=97 and " & _
                         "asc(PairLetter) <=122) " & _
                  "ORDER BY PairLetter, Shot"
        .Open
'        .ActiveConnection = Nothing
    End With
                 
    Set AdoShots.Recordset = rs1
    Set AdoPairs.Recordset = rs2
   
'    Set rs1 = Nothing
'    Set rs2 = Nothing
   
    AdoShots.Recordset.MoveFirst
    Do Until AdoShots.Recordset.EOF

Regards,

Geoff
0
 
LVL 1

Expert Comment

by:crw030
Comment Utility
GeoffO.

I will analyze tonight from home (gotta do work).  But can you post some sample data from each table so we can all experiment?
0
 

Author Comment

by:GeoffO
Comment Utility
Hello there crw030 and thanks for sticking with it.

Everything is in one table (MayDay) and I hope to be able to create separate recordsets to process each competition.

For example, in addition to the Pairs recordset (results shown here as if calculated, but actually put in by hand!), the results for Juniors, Ladies, Veterans, Teams of 4, County teams of 6 etc have to be extracted from their scores on three cards at 50 metres and three at 100 yards.

Here we go with 12 sets of results reading downwards from field name (it may look a complete mess, having typed it here in mono-spaced Courier and then having it converted into proportionally-spaced Arial, but if it does, hold on and I will set it out again in a revised comment):

ID    Shot                 Club                   A:50m1  B:50m2  C:50m3  D:100yd1  E:100yd2  F:100yd3
 26   Brotherton, V   Sidbury                  0            1             2             3                 4              5
127  Beer, L             Tavistock               1            2             3             4                 5              6
277  Abbey, C          Fonthill                  2            2             3             5                 6               7
 74   Bowyer, P        Tavistock               3            2             1             8                 7              9
 50   Abbot, S           Cheddar Valley     3            4             5             9                 10            11
138  Bamford, S       Paignton               4             5            6              7                 8              9
 94   Tucker, T          City of Wells         4             8           10            17                12            9
311  Abbot, T           Cheddar Valley     4             5            6             11                12           13
119  Bowman, A       Braunton              5             4            3               7                13             9
 25   Blewett, B         Tiverton                5             6            7              9                 10            12
279  Bryant, K           Exeter                  5             0            0              7                  8              9
132  Averett, J          Manaton               5             6            7             10                11            12

---- continuing with same 12 people, next set of fields ---

ABCDEF  ABC  DEF  AD  BCEF  Avge  Class  Fem  Jun  Nov  Vet  PairLetter PairScore  
    15           3      12      3     12        95       B        y                                  A              A36
    21           6      15      5      16       95       B                        y                  A              A36
    25           7      18      7      18       95       B                                 y         B              B55
    30           6      24      11    19       96       B                                 y         B              B55
    42          12     30      12    30       96.2    B                        y                  C              C81
    39          15     24      11    28       94       C                                            C              C81
    60          22     38      21    39       93.1    C                 y                         D              D111
    51          15     36      15    36       88       D                                            D              D111
    41          12     29      12    29       96       B        y                                   E              E90
    49          18     31      14    35       93       C                                            E               E90
    29           5      24      12    17       96       B                                 y          F              F80
    51           18    33      15    36       93       C                                            F               F80  

---- continuing with same 12 people, next set of fields ---

ClubTeam   OpenTeam  County       DupShot
      A                               Devon        Brotherton, V
      A                               Devon        Beer, L
      C                               Cornwall    Abbey, C
      A                                                 Bowyer, P
      B                                Somerset  Abbot, S
      B                                Somerset  Bamford, S
      A                                                 Tucker, T
      B                                                 Abbot, T
      B                                                 Bowman, A
      C                                                 Blewett, B
      C                                                 Bryant, K
      C                                Cornwall   Averett, J


Regards,

Geoff
0
 

Author Comment

by:GeoffO
Comment Utility
That’s probably just about OK, but let me know if it isn’t.

Good luck, and sorry about the work!

Geoff
0
 
LVL 1

Expert Comment

by:crw030
Comment Utility
Geoff().

Think I am starting to understand the problem.  For the ABCDEF column you can get it updated with a simple SQL statement:
UPDATE MayDay SET ABCDEF = ([A]+[B]+[C]+[D]+[E]+[F]), ABC = [A]+[B]+[C], DEF = [D]+[E]+[F], AD = [A]+[D], BCEF = [B]+[C]+[E]+[F];

**NOTE** This would also work in the code as a CN.EXECUTE SQL If you wanted to do it from the code as well.

Then I did a working PAIRS loop.  I had to change the LockType for the recordset but I think you;ll get the idea.

Private Sub Command2_Click()
   Dim strFilePath As String
  Dim cn1 As ADODB.Connection
  Dim rs1 As ADODB.Recordset
  Dim rs2 As ADODB.Recordset
  Dim rsTemp As ADODB.Recordset
 
 
 
  strFilePath = App.Path & "\MayDay.mdb"
  Set cn1 = New ADODB.Connection
  With cn1
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Persist Security Info=False;" & _
          "Data Source=" & strFilePath & ";"
   .CursorLocation = adUseClient
   .Open
  End With
 
  Set rs1 = New ADODB.Recordset
  With rs1
   .CursorType = adOpenDynamic
   .LockType = adLockBatchOptimistic
   .CursorLocation = adUseClient
   .ActiveConnection = cn1
   .Source = "SELECT * FROM [MayDay] " & _
                      "ORDER BY [Shot] asc, [ID] asc"
   .Open
   '.ActiveConnection = Nothing
  End With
 
 

 
'Calculate and Update the Pairs Scores
Set rsTemp = cn1.Execute("SELECT PairLetter,PairLetter+CSTR(SUM(ABCDEF)) AS NewVal FROM MayDay GROUP BY PAIRLETTER")
  DoEvents
  rs1.MoveFirst
  Do While Not rs1.EOF
    'Check for a Pair Score
    rsTemp.MoveFirst
        Do While Not rsTemp.EOF
            If rs1("PAIRLETTER") = rsTemp("PAIRLETTER") Then
                rs1!PAIRSCORE = rsTemp("NewVal")
                Exit Do
            End If
            rsTemp.MoveNext
        Loop
    rs1.MoveNext
  Loop
  rs1.UpdateBatch
 
rsTemp.Close
 
  '***********************
  '***********************\
  '**** MORE CODE HERE FOR OTHER CALCULATIONS LIKE CLUB, ETC
  '***********************
  '***********************
 
'Get the updated totals for display in second DataGrid
  Set rs2 = New ADODB.Recordset
  With rs2
   .CursorLocation = adUseClient
   .CursorType = adOpenStatic
   .ActiveConnection = cn1
   .Source = "SELECT Shot, PairLetter, PairScore, ABCDEF FROM [MayDay] " & _
            "WHERE PairLetter <> null and" & _
            "(left(PairLetter,1) >='A' and " & _
            "left(PairLetter,1) <='Z') or " & _
            "(left(PairLetter,1) >='a' and " & _
            "left(PairLetter,1) <='z') " & _
            "ORDER BY left(PairLetter,1), Shot"
   .Open
   .ActiveConnection = Nothing
  End With
 
 
  Set Adodc.Recordset = rs1
  Set Adodc2.Recordset = rs2
 
   Set rs1 = Nothing
   Set rs2 = Nothing


End Sub



My instincts tell me there has to be a way to do this faster (and with more SQL) but it's not coming to me...so the brute force approach might solve your problem in the shortest time.  The basic setup is Create a recordset with the "new values" you have calculated (GROUPED BY whatever field CLUB, PAIRLETTER etc) then lookp through the Player list, search for the matching entry in the temporary recordset and set a column value equal to the calculated value, then do a batch update.
0
 

Author Comment

by:GeoffO
Comment Utility
crw030, this is really intriguing.  I hadn’t thought of using SQL for sums.

I now have to spend a couple of days away from this, but will return to the fray as soon as poss.

Geoff

0
 

Author Comment

by:GeoffO
Comment Utility
Here we are again, crw030.  Two days on website creation has cleared the brain.

Instead of trying to get two or more recordsets, I should have used the same one with different specs:

   'General calculations
   'using and showing all competitors    
    With AdoShots
        .ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
            "Data Source=" & strFilePath & ";"
        .RecordSource = "SELECT * FROM [MayDay]" & _
                        "ORDER BY Shot"
    End With

   'Calculate Pairs
   'using only those taking part in the Pairs competition
    AdoShots.Recordset.Close
    With AdoShots
        .ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
            "Data Source=" & strFilePath & ";"
        .RecordSource = "SELECT Shot, PairLetter, PairScore, ABCDEF FROM [MayDay] " & _
                        "WHERE PairLetter is not null and " & _
                             "(PairLetter >='A' and " & _
                              "PairLetter <='Z') or " & _
                             "(PairLetter >='a' and " & _
                              "PairLetter <='z') " & _
                        "ORDER BY left(PairLetter,1), Shot"
        .Refresh
    End With
                       
    AdoShots.Recordset.MoveFirst
    Do Until AdoShots.Recordset.EOF
        ' ... do the sums for Pairs
    Loop
        ' ... then, to see all the results again, back to

    With AdoShots
        .ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Persist Security Info=False;" & _
            "Data Source=" & strFilePath & ";"
        .RecordSource = "SELECT * FROM [MayDay]" & _
                        "ORDER BY Shot"
    End With

This works perfectly.

I propose to give you the 200 points for all the effort you have put into solving my problem ... unless anyone else feels hard done by?

Best regards,

Geoff
0
 
LVL 1

Accepted Solution

by:
crw030 earned 200 total points
Comment Utility
I'm glad you got it working.  I won't lie to you...I was having a hard time with exactly what sport you were trying to score.  But the data explained the problem a little better.  Good Luck. -out-
0
 

Author Comment

by:GeoffO
Comment Utility
Thanks crw030.  

Regards,

Geoff
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now