Lottery combinations

[keith1]

Hi
I am after the code to generate all the combinations of the UK lottery 6/49. Will excel hold this much data as it is nearly 14 mil combos.
Thanks

 

[Ruddles]

An hour?

Ah, I've just realised I've been saving to a network drive, so that can't have helped!

 

[al_b_cnu]

Hi Keith,

It wasnt you who won the £117m Euro lottery was it?

If so, I think you owe Ruddles a drink ;¬)

 

[johan de beer]

Hello Ruddles,

This code works brilliantly. My Access DB has become obsolute however and I have moved onto MS SQL 2012. I have tried to adapt it as a stroed procedure but I am struggling a bit. Will you please be able to help me to run the code in SQL.
Thanks
Johan

 

[Ruddles]

Sorry, I wouldn't have a clue where to start with that sort of thing, or even whether it's possible. My SQL is restricted to a few simple SELECT FROM WHERE AND ORDER BY statements.

 

[johan de beer]

No Problem, I will have to play around with it, its a pity that Access have a 2 gig limit. When I get it right I will post it here
Thanks
Johan

 

[johan de beer]

Hello all, Just hoping some one out there might know how to add the record to the table in the SQL backend, ,,,,,instead of the access db.

 

[johan de beer]

Did some reading on DAO vs ADO and figured it out, I am using Access 2010, SQL 2012 backend on the same machine, added the ado library underTools/references.

Works well with the Euro Lotto combinations, too.


Option Explicit

Public Sub Generate_6ex49()

Dim dbData As Database
Dim rsData As New ADODB.Recordset
Dim p1 As Integer
Dim p2 As Integer
Dim p3 As Integer
Dim p4 As Integer
Dim p5 As Integer
Dim p6 As Integer
Dim dtStart As Date

On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL ("DROP TABLE tblCombinations;")
DoCmd.SetWarnings True
On Error GoTo 0
DoCmd.RunSQL ("CREATE TABLE tblCombinations ( " _
& "[Ball1] Integer, " _
& "[Ball2] Integer, " _
& "[Ball3] Integer, " _
& "[Ball4] Integer, " _
& "[Ball5] Integer, " _
& "[Ball6] Integer " _
& ");")

Set dbData = CurrentDb
rsData.Open "tblCombinations", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

dtStart = Now()

For p1 = 1 To 44
For p2 = p1 + 1 To 45
For p3 = p2 + 1 To 46
For p4 = p3 + 1 To 47
For p5 = p4 + 1 To 48
For p6 = p5 + 1 To 49
With rsData
.AddNew
!Ball1 = p1
!Ball2 = p2
!Ball3 = p3
!Ball4 = p4
!Ball5 = p5
!Ball6 = p6
rsData.Update
End With
Next p6
Next p5
Next p4
Next p3
Next p2
Next p1

MsgBox Format(rsData.RecordCount, "#,###") & " combinations" & Space(10) _
& vbCrLf & vbCrLf _
& "Run time: " & Format(Now() - dtStart, "hh:nn:ss"), _
vbOKOnly + vbInformation

rsData.Close

End Sub

 

[bigroo]

I am guessing, if I want to do the same for the US Lottery which has 1 to 54 I would change this:

Set dbData = CurrentDb
rsData.Open "tblCombinations", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

dtStart = Now()

For p1 = 1 To 44
For p2 = p1 + 1 To 45
For p3 = p2 + 1 To 46
For p4 = p3 + 1 To 47
For p5 = p4 + 1 To 48
For p6 = p5 + 1 To 49

To this??

Set dbData = CurrentDb
rsData.Open "tblCombinations", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

dtStart = Now()

For p1 = 1 To 49
For p2 = p1 + 1 To 50
For p3 = p2 + 1 To 51
For p4 = p3 + 1 To 52
For p5 = p4 + 1 To 53
For p6 = p5 + 1 To 54

 

[johan de beer]

That worked for me.

 

[bigroo]

Great thanks!!