| Author |
Message |
|
Gino
#1 / 13
|
 Self Joins Question Hi, I have the sql statement as below which is Self Joins Select aR.Score, bR.Score From Team as aT, Result As aR, Team As bT,
Result As bR WHERE aT.TeamID=ar.TeamID AND aR.MatchID=bR.MatchID AND
br.TeamID=bT.TeamID AND ar.TeamID=1
However, when I run it under a modules to get the recordset, it has
the error
rstResult.Open strSQLResult, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
The error message is "Method 'Open' of object'_recordset' failed"
Anyone knows why ??
Thanks
Ginola
|
| Thu, 03 Nov 2005 21:16:44 GMT |
|
 |
|
John Vinso
#2 / 13
|
Self Joins Question
Quote: >Hi, I have the sql statement as below which is Self Joins >Select aR.Score, bR.Score From Team as aT, Result As aR, Team As bT,
>Result As bR WHERE aT.TeamID=ar.TeamID AND aR.MatchID=bR.MatchID AND
>br.TeamID=bT.TeamID AND ar.TeamID=1
>However, when I run it under a modules to get the recordset, it has
>the error
>rstResult.Open strSQLResult, CurrentProject.Connection, adOpenKeyset,
>adLockOptimistic
>The error message is "Method 'Open' of object'_recordset' failed"
>Anyone knows why ??
>Thanks
Please post the actual VBA code leading up to this. If you put the SQL into a new Query, can you see the data on opening the query datasheet? John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Fri, 04 Nov 2005 03:55:33 GMT |
|
|
|
Gino
#3 / 13
|
Self Joins Question only few lines codes I can see the result by opening the query datasheet? Sub UpdateTable()
Dim rstTeam As New ADODB.Recordset
Dim rstResult As New ADODB.Recordset
Dim strSQL As String
Dim strSQLResult As String
Dim strReset As String
Dim strWhere As String
Dim i As Long
Dim lngTeamID As Long
Dim lngMatchID As Long
Dim lngResultID As Long
Dim lngWin As Long
Dim lngDraw As Long
Dim lngLoss As Long
Dim lngF As Long
Dim lngA As Long
Set rstSuppliers = New ADODB.Recordset
strSQL = "SELECT * FROM Team "
rstTeam.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Do While Not rstTeam.EOF ' For each TeamID
lngTeamID = rstTeam("TeamID")
strSQLResult = "Select aR.Score, bR.Score From Team as aT,
Result As aR, Team As bT, Result As bR WHERE aT.TeamID=ar.TeamID AND
aR.MatchID=bR.MatchID AND br.TeamID=bT.TeamID AND ar.TeamID=" &
lngTeamID
rstResult.Open strSQLResult, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
lngWin = 0
lngDraw = 0
lngLoss = 0
Do While Not rstResult.EOF ' For each Match For particular
Team
'lngF = rstTeam("aR.Score")
'lngA = rstTeam("bR.Score")
If rstTeam("aR.Score") > rstTeam("bR.Score") Then
lngWin = lngWin + 1
ElseIf rstTeam("aR.Score") = rstTeam("bR.Score") Then
lngDraw = lngDraw + 1
ElseIf rstTeam("aR.Score") < rstTeam("bR.Score") Then
lngLoss = lngLoss + 1
End If
rstResult.MoveNext
Loop
Call updateLeagueTable(lngTeamID, lngWin, lngDraw, lngLoss)
rstTeam.MoveNext
Loop
strSQL = "Update " & strTable & " SET TypeCheck=TRUE WHERE " &
strWhere
'DoCmd.RunSQL strSQL
CurrentDb.Execute strSQL
End Sub
Sub updateLeagueTable(lngTeamID As Long, lngWin As Long, lngDraw As
Long, lngLoss As Long)
Dim rstTable As New ADODB.Recordset
strSQL = "Update LeagueTable SET Win=" & lngWin & " , Draw" &
lngDraw & ", Loss=" & lngLoss & " WHERE TeamID=" & lngTeamID
'rstTable.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
CurrentDb.Execute strSQL
End Sub
On Sun, 18 May 2003 13:55:33 -0600, John Vinson
Quote:
>>Hi, I have the sql statement as below which is Self Joins
>>Select aR.Score, bR.Score From Team as aT, Result As aR, Team As bT,
>>Result As bR WHERE aT.TeamID=ar.TeamID AND aR.MatchID=bR.MatchID AND
>>br.TeamID=bT.TeamID AND ar.TeamID=1
>>However, when I run it under a modules to get the recordset, it has
>>the error
>>rstResult.Open strSQLResult, CurrentProject.Connection, adOpenKeyset,
>>adLockOptimistic
>>The error message is "Method 'Open' of object'_recordset' failed"
>>Anyone knows why ??
>>Thanks
>Please post the actual VBA code leading up to this. If you put the SQL
>into a new Query, can you see the data on opening the query datasheet?
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
Ginola
|
| Sat, 05 Nov 2005 01:05:37 GMT |
|
|
|
Gino
#4 / 13
|
Self Joins Question I am not sure if it is allowed to do attachment in here, just if you are interested, this is all my program... just 40 k after zip .. On Sun, 18 May 2003 13:55:33 -0600, John Vinson
Quote:
>>Hi, I have the sql statement as below which is Self Joins
>>Select aR.Score, bR.Score From Team as aT, Result As aR, Team As bT,
>>Result As bR WHERE aT.TeamID=ar.TeamID AND aR.MatchID=bR.MatchID AND
>>br.TeamID=bT.TeamID AND ar.TeamID=1
>>However, when I run it under a modules to get the recordset, it has
>>the error
>>rstResult.Open strSQLResult, CurrentProject.Connection, adOpenKeyset,
>>adLockOptimistic
>>The error message is "Method 'Open' of object'_recordset' failed"
>>Anyone knows why ??
>>Thanks
>Please post the actual VBA code leading up to this. If you put the SQL
>into a new Query, can you see the data on opening the query datasheet?
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
Ginola
|
| Sat, 05 Nov 2005 01:06:40 GMT |
|
|
|
John Vinso
#5 / 13
|
Self Joins Question
Quote: >I am not sure if it is allowed to do attachment in here, just if you
>are interested, this is all my program...
>just 40 k after zip ..
Sorry, no. Attachments are very much frowned upon around here; I'm really reluctant to open a binary file from an unvouched source; and it would be more work to open and clean up your database than I can justify as an unpaid volunteer. John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Sat, 05 Nov 2005 12:55:21 GMT |
|
|
|
Brian Gallaghe
#6 / 13
|
Self Joins Question Although I was not supposed to I downloaded your database and made the fix! Below is the code for your module - UpdateTable. I have made fixes to both
subs in there so its best you replace the code with mine!
Hope this helps.
Regards
Brian
New Code:
Option Compare Database
Option Explicit
Sub UpdateTable()
Dim rstTeam As New ADODB.Recordset
Dim rstResult As New ADODB.Recordset
Dim cnnConnection As New ADODB.Connection
Dim strSQL As String
Dim strSQLResult As String
Dim strReset As String
Dim strWhere As String
Dim i As Long
Dim lngTeamID As Long
Dim lngMatchID As Long
Dim lngResultID As Long
Dim lngWin As Long
Dim lngDraw As Long
Dim lngLoss As Long
Dim lngF As Long
Dim lngA As Long
Set cnnConnection = CurrentProject.Connection
strSQL = "SELECT * FROM Team "
rstTeam.Open strSQL, cnnConnection, adOpenKeyset, adLockOptimistic
Do While Not rstTeam.EOF ' For each TeamID
lngTeamID = rstTeam("TeamID")
strSQLResult = "SELECT Result.Score AS AR, Result_1.Score AS BR FROM
((Team INNER JOIN Result ON Team.TeamID = Result.TeamID) INNER JOIN Result
AS Result_1 ON Result.ResultID = Result_1.ResultID) INNER JOIN Team AS
Team_1 ON Result_1.TeamID = Team_1.TeamID WHERE Result.TeamID = " &
lngTeamID
rstResult.Open strSQLResult, cnnConnection, adOpenKeyset,
adLockOptimistic
lngWin = 0
lngDraw = 0
lngLoss = 0
Do While Not rstResult.EOF ' For each Match For particular Team
'lngF = rstTeam("aR.Score")
'lngA = rstTeam("bR.Score")
If rstResult.Fields("aR") > rstResult("bR") Then
lngWin = lngWin + 1
ElseIf rstResult("aR") = rstResult("bR") Then
lngDraw = lngDraw + 1
ElseIf rstResult("aR") < rstResult("bR") Then
lngLoss = lngLoss + 1
End If
rstResult.MoveNext
Loop
rstResult.Close
Call updateLeagueTable(lngTeamID, lngWin, lngDraw, lngLoss)
rstTeam.MoveNext
Loop
'** TAKEN OUT AS NOT SURE WHAT IT DOES!
' strSQL = "Update " & strTable & " SET TypeCheck=TRUE WHERE " & strWhere
'DoCmd.RunSQL strSQL
' CurrentDb.Execute strSQL
End Sub
Sub updateLeagueTable(lngTeamID As Long, lngWin As Long, lngDraw As Long,
lngLoss As Long)
Dim rstTable As New ADODB.Recordset
Dim strSQL As String
strSQL = "Update LeagueTable SET Win=" & lngWin & " , Draw=" & lngDraw &
", Loss=" & lngLoss & " WHERE TeamID=" & lngTeamID
'rstTable.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
CurrentDb.Execute strSQL
End Sub
|
| Sun, 06 Nov 2005 09:40:02 GMT |
|
|
|
Gino
#7 / 13
|
Self Joins Question sorry about that... I will not attach any thing in the future ... thanks
On Mon, 19 May 2003 22:55:21 -0600, John Vinson
Quote:
>>I am not sure if it is allowed to do attachment in here, just if you
>>are interested, this is all my program...
>>just 40 k after zip ..
>Sorry, no. Attachments are very much frowned upon around here; I'm
>really reluctant to open a binary file from an unvouched source; and
>it would be more work to open and clean up your database than I can
>justify as an unpaid volunteer.
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
Ginola
|
| Wed, 09 Nov 2005 11:29:32 GMT |
|
|
|
Gino
#8 / 13
|
Self Joins Question Thanks for your help, and I am sorry to post the attachment since I do not know the rule...sorry : ) On Wed, 21 May 2003 02:40:02 +0100, "Brian Gallagher"
Quote:
>Although I was not supposed to I downloaded your database and made the fix! >Below is the code for your module - UpdateTable. I have made fixes to both
>subs in there so its best you replace the code with mine!
>Hope this helps.
>Regards
>Brian
>New Code:
>Option Compare Database
>Option Explicit
>Sub UpdateTable()
> Dim rstTeam As New ADODB.Recordset
> Dim rstResult As New ADODB.Recordset
> Dim cnnConnection As New ADODB.Connection
> Dim strSQL As String
> Dim strSQLResult As String
> Dim strReset As String
> Dim strWhere As String
> Dim i As Long
> Dim lngTeamID As Long
> Dim lngMatchID As Long
> Dim lngResultID As Long
> Dim lngWin As Long
> Dim lngDraw As Long
> Dim lngLoss As Long
> Dim lngF As Long
> Dim lngA As Long
> Set cnnConnection = CurrentProject.Connection
> strSQL = "SELECT * FROM Team "
> rstTeam.Open strSQL, cnnConnection, adOpenKeyset, adLockOptimistic
> Do While Not rstTeam.EOF ' For each TeamID
> lngTeamID = rstTeam("TeamID")
> strSQLResult = "SELECT Result.Score AS AR, Result_1.Score AS BR FROM
>((Team INNER JOIN Result ON Team.TeamID = Result.TeamID) INNER JOIN Result
>AS Result_1 ON Result.ResultID = Result_1.ResultID) INNER JOIN Team AS
>Team_1 ON Result_1.TeamID = Team_1.TeamID WHERE Result.TeamID = " &
>lngTeamID
> rstResult.Open strSQLResult, cnnConnection, adOpenKeyset,
>adLockOptimistic
> lngWin = 0
> lngDraw = 0
> lngLoss = 0
> Do While Not rstResult.EOF ' For each Match For particular Team
> 'lngF = rstTeam("aR.Score")
> 'lngA = rstTeam("bR.Score")
> If rstResult.Fields("aR") > rstResult("bR") Then
> lngWin = lngWin + 1
> ElseIf rstResult("aR") = rstResult("bR") Then
> lngDraw = lngDraw + 1
> ElseIf rstResult("aR") < rstResult("bR") Then
> lngLoss = lngLoss + 1
> End If
> rstResult.MoveNext
> Loop
> rstResult.Close
> Call updateLeagueTable(lngTeamID, lngWin, lngDraw, lngLoss)
> rstTeam.MoveNext
> Loop
>'** TAKEN OUT AS NOT SURE WHAT IT DOES!
>' strSQL = "Update " & strTable & " SET TypeCheck=TRUE WHERE " & strWhere
> 'DoCmd.RunSQL strSQL
>' CurrentDb.Execute strSQL
>End Sub
>Sub updateLeagueTable(lngTeamID As Long, lngWin As Long, lngDraw As Long,
>lngLoss As Long)
> Dim rstTable As New ADODB.Recordset
> Dim strSQL As String
> strSQL = "Update LeagueTable SET Win=" & lngWin & " , Draw=" & lngDraw &
>", Loss=" & lngLoss & " WHERE TeamID=" & lngTeamID
> 'rstTable.Open strSQL, CurrentProject.Connection, adOpenKeyset,
>adLockOptimistic
> CurrentDb.Execute strSQL
>End Sub
Ginola
|
| Wed, 09 Nov 2005 11:30:36 GMT |
|
|
|
Brian Gallaghe
#9 / 13
|
Self Joins Question did it work well for you?
Quote: > Thanks for your help, > and I am sorry to post the attachment since I do not know the > rule...sorry : ) > On Wed, 21 May 2003 02:40:02 +0100, "Brian Gallagher"
> >Although I was not supposed to I downloaded your database and made the
fix!
> >Below is the code for your module - UpdateTable. I have made fixes to
both
> >subs in there so its best you replace the code with mine!
> >Hope this helps.
> >Regards
> >Brian
> >New Code:
> >Option Compare Database
> >Option Explicit
> >Sub UpdateTable()
> > Dim rstTeam As New ADODB.Recordset
> > Dim rstResult As New ADODB.Recordset
> > Dim cnnConnection As New ADODB.Connection
> > Dim strSQL As String
> > Dim strSQLResult As String
> > Dim strReset As String
> > Dim strWhere As String
> > Dim i As Long
> > Dim lngTeamID As Long
> > Dim lngMatchID As Long
> > Dim lngResultID As Long
> > Dim lngWin As Long
> > Dim lngDraw As Long
> > Dim lngLoss As Long
> > Dim lngF As Long
> > Dim lngA As Long
> > Set cnnConnection = CurrentProject.Connection
> > strSQL = "SELECT * FROM Team "
> > rstTeam.Open strSQL, cnnConnection, adOpenKeyset, adLockOptimistic
> > Do While Not rstTeam.EOF ' For each TeamID
> > lngTeamID = rstTeam("TeamID")
> > strSQLResult = "SELECT Result.Score AS AR, Result_1.Score AS BR
FROM
> >((Team INNER JOIN Result ON Team.TeamID = Result.TeamID) INNER JOIN
Result
> >AS Result_1 ON Result.ResultID = Result_1.ResultID) INNER JOIN Team AS
> >Team_1 ON Result_1.TeamID = Team_1.TeamID WHERE Result.TeamID = " &
> >lngTeamID
> > rstResult.Open strSQLResult, cnnConnection, adOpenKeyset,
> >adLockOptimistic
> > lngWin = 0
> > lngDraw = 0
> > lngLoss = 0
> > Do While Not rstResult.EOF ' For each Match For particular Team
> > 'lngF = rstTeam("aR.Score")
> > 'lngA = rstTeam("bR.Score")
> > If rstResult.Fields("aR") > rstResult("bR") Then
> > lngWin = lngWin + 1
> > ElseIf rstResult("aR") = rstResult("bR") Then
> > lngDraw = lngDraw + 1
> > ElseIf rstResult("aR") < rstResult("bR") Then
> > lngLoss = lngLoss + 1
> > End If
> > rstResult.MoveNext
> > Loop
> > rstResult.Close
> > Call updateLeagueTable(lngTeamID, lngWin, lngDraw, lngLoss)
> > rstTeam.MoveNext
> > Loop
> >'** TAKEN OUT AS NOT SURE WHAT IT DOES!
> >' strSQL = "Update " & strTable & " SET TypeCheck=TRUE WHERE " &
strWhere
> > 'DoCmd.RunSQL strSQL
> >' CurrentDb.Execute strSQL
> >End Sub
> >Sub updateLeagueTable(lngTeamID As Long, lngWin As Long, lngDraw As Long,
> >lngLoss As Long)
> > Dim rstTable As New ADODB.Recordset
> > Dim strSQL As String
> > strSQL = "Update LeagueTable SET Win=" & lngWin & " , Draw=" &
lngDraw &
> >", Loss=" & lngLoss & " WHERE TeamID=" & lngTeamID
> > 'rstTable.Open strSQL, CurrentProject.Connection, adOpenKeyset,
> >adLockOptimistic
> > CurrentDb.Execute strSQL
> >End Sub
> Ginola
|
| Wed, 09 Nov 2005 18:04:08 GMT |
|
| |
|
