|
|
Hi All,
I Have the following code, basically the code is to set the ranking number
against a set of records in a table called 'tblRANK'. The table contains
around 50,000 records and the ranking is across each country, in that the
ranking starts agian at 1 each time there is a new country, the table is
already sort by COUNTRY Asc and AMOUNT dec so the table is in the corret
order and the result is as expected.
This issue is, that the database size explodes massively and I don't
understand why and thus don't know of a solution on how to correct it. I've
tried BeginTrans and CommitTrans but that only seems to delay the increase in
the database size until the end. I'm expecting it to do something with that
I'm using simple DAO Edit and Update. Would I get the same issue if I try ADO?
Any help would be much appreciated.
TIA
KM
Dim wrkCurrent As DAO.Workspace
Dim dbCurrent As DAO.Database
Dim rstDATA As DAO.Recordset
Dim varReturn As Variant
Dim strCOUNTRY As String
Dim lngRECORD As Long
DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
Set wrkCurrent = DBEngine.Workspaces(0)
Set dbCurrent = CurrentDb
Set rstDATA = dbCurrent.OpenRecordset("tblRANK", dbOpenDynaset)
strCOUNTRY = rstDATA!COUNTRY
lngRECORD = 1
wrkCurrent.BeginTrans
Do Until rstDATA.EOF
If strCOUNTRY <> rstDATA!COUNTRY Then
strCOUNTRY = rstDATA!COUNTRY
lngRECORD = 1
End If
rstDATA.Edit
rstDATA!RANK = lngRECORD
rstDATA.Update
rstDATA.MoveNext
lngRECORD = lngRECORD + 1
Loop
wrkCurrent.CommitTrans
On Error Resume Next
rstDATA.Close
dbCurrent.Close
wrkCurrent.Close
Set rstDATA = Nothing
Set dbCurrent = Nothing
Set wrkCurrent = Nothing
|
|
Kevin McCartney wrote:
[Quoted Text] >I Have the following code, basically the code is to set the ranking number
>against a set of records in a table called 'tblRANK'. The table contains
>around 50,000 records and the ranking is across each country, in that the
>ranking starts agian at 1 each time there is a new country, the table is
>already sort by COUNTRY Asc and AMOUNT dec so the table is in the corret
>order and the result is as expected.
>
>This issue is, that the database size explodes massively and I don't
>understand why and thus don't know of a solution on how to correct it. I've
>tried BeginTrans and CommitTrans but that only seems to delay the increase in
>the database size until the end. I'm expecting it to do something with that
>I'm using simple DAO Edit and Update. Would I get the same issue if I try ADO?
>
>
> Dim wrkCurrent As DAO.Workspace
> Dim dbCurrent As DAO.Database
> Dim rstDATA As DAO.Recordset
>
> Dim varReturn As Variant
> Dim strCOUNTRY As String
> Dim lngRECORD As Long
>
> DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
> Set wrkCurrent = DBEngine.Workspaces(0)
> Set dbCurrent = CurrentDb
> Set rstDATA = dbCurrent.OpenRecordset("tblRANK", dbOpenDynaset)
>
> strCOUNTRY = rstDATA!COUNTRY
> lngRECORD = 1
>
> wrkCurrent.BeginTrans
> Do Until rstDATA.EOF
>
> If strCOUNTRY <> rstDATA!COUNTRY Then
> strCOUNTRY = rstDATA!COUNTRY
> lngRECORD = 1
> End If
>
> rstDATA.Edit
> rstDATA!RANK = lngRECORD
> rstDATA.Update
> rstDATA.MoveNext
>
> lngRECORD = lngRECORD + 1
> Loop
> wrkCurrent.CommitTrans
I suspect that the explosion is temp space need to process
such a large recordset. Besides, that's not a good way to
go about it.
The ranking could change any time a record is added or
edited, which means that you should not have a rank field in
a table and that you need to recalculate the ranking every
time you want to look at it or use it in a form or report.
This can be done by using a query with a ranking subquery:
SELECT tblRANK.*,
(SELECT Count(*) As Rank
FROM tblRANK As X
WHERE X.Country = tblRANK.Country
And X.Amount <= tblRANK.Amount) As Rank
FROM tblRANK
Then you can use this kind of query instead of using the
table in a form or report.
--
Marsh
MVP [MS Access]
|
|
Hi Marshall Barton,
Thank you very much for your reply, I tried a ranking sub query but I can't
seem to get the ranking to disregard ranking ties, in that the requirement is
to have a ranking sequential number e.g. instead of 1, 2, 2, 4, 5 or 1, 2, 2,
3, 4 I need to show 1,2,3,4,5. (I know it's an odd way of ranking but I work
with odd people!).
If it is possible to disregard ranking ties, can you show me on the example
below,
SELECT tblRANK.COUNTRY, tblRANK.AMOUNT, (SELECT Count(*) As RANK FROM
tblRANK As Z WHERE Z.COUNTRY = tblRANK.COUNTRY And Z.Amount >
tblRANK.Amount)+1 AS RANK
FROM tblRANK
ORDER BY tblRANK.COUNTRY, tblRANK.AMOUNT DESC;
Thank you in advance, have a great day and take care.
regards
KM
"Marshall Barton" wrote:
[Quoted Text] > Kevin McCartney wrote:
> >I Have the following code, basically the code is to set the ranking number
> >against a set of records in a table called 'tblRANK'. The table contains
> >around 50,000 records and the ranking is across each country, in that the
> >ranking starts agian at 1 each time there is a new country, the table is
> >already sort by COUNTRY Asc and AMOUNT dec so the table is in the corret
> >order and the result is as expected.
> >
> >This issue is, that the database size explodes massively and I don't
> >understand why and thus don't know of a solution on how to correct it. I've
> >tried BeginTrans and CommitTrans but that only seems to delay the increase in
> >the database size until the end. I'm expecting it to do something with that
> >I'm using simple DAO Edit and Update. Would I get the same issue if I try ADO?
> >
> >
> > Dim wrkCurrent As DAO.Workspace
> > Dim dbCurrent As DAO.Database
> > Dim rstDATA As DAO.Recordset
> >
> > Dim varReturn As Variant
> > Dim strCOUNTRY As String
> > Dim lngRECORD As Long
> >
> > DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
> > Set wrkCurrent = DBEngine.Workspaces(0)
> > Set dbCurrent = CurrentDb
> > Set rstDATA = dbCurrent.OpenRecordset("tblRANK", dbOpenDynaset)
> >
> > strCOUNTRY = rstDATA!COUNTRY
> > lngRECORD = 1
> >
> > wrkCurrent.BeginTrans
> > Do Until rstDATA.EOF
> >
> > If strCOUNTRY <> rstDATA!COUNTRY Then
> > strCOUNTRY = rstDATA!COUNTRY
> > lngRECORD = 1
> > End If
> >
> > rstDATA.Edit
> > rstDATA!RANK = lngRECORD
> > rstDATA.Update
> > rstDATA.MoveNext
> >
> > lngRECORD = lngRECORD + 1
> > Loop
> > wrkCurrent.CommitTrans
>
>
> I suspect that the explosion is temp space need to process
> such a large recordset. Besides, that's not a good way to
> go about it.
>
> The ranking could change any time a record is added or
> edited, which means that you should not have a rank field in
> a table and that you need to recalculate the ranking every
> time you want to look at it or use it in a form or report.
>
> This can be done by using a query with a ranking subquery:
>
> SELECT tblRANK.*,
> (SELECT Count(*) As Rank
> FROM tblRANK As X
> WHERE X.Country = tblRANK.Country
> And X.Amount <= tblRANK.Amount) As Rank
> FROM tblRANK
>
> Then you can use this kind of query instead of using the
> table in a form or report.
>
> --
> Marsh
> MVP [MS Access]
>
|
|
Kevin McCartney wrote:
[Quoted Text] >Thank you very much for your reply, I tried a ranking sub query but I can't
>seem to get the ranking to disregard ranking ties, in that the requirement is
>to have a ranking sequential number e.g. instead of 1, 2, 2, 4, 5 or 1, 2, 2,
>3, 4 I need to show 1,2,3,4,5. (I know it's an odd way of ranking but I work
>with odd people!).
>
>If it is possible to disregard ranking ties, can you show me on the example
>below,
>
>SELECT tblRANK.COUNTRY, tblRANK.AMOUNT, (SELECT Count(*) As RANK FROM
>tblRANK As Z WHERE Z.COUNTRY = tblRANK.COUNTRY And Z.Amount >
>tblRANK.Amount)+1 AS RANK
>FROM tblRANK
>ORDER BY tblRANK.COUNTRY, tblRANK.AMOUNT DESC;
If you can not specify a way to break ties, then it's
unreasonable to expect a query to do it for you ;-)
You really do have to have one or more tie breaking fields
in order to be able to specify a unique sort order (i.e.
ranking). Since a table can not be relational without a
primary key, the PK can be used as a last resort tie
breaker.
If your table has a simple, one field primary key, then the
query would be like:
SELECT COUNTRY, AMOUNT,
(SELECT Count(*) As RANK
FROM tblRANK As Z
WHERE Z.COUNTRY = tblRANK.COUNTRY
And (Z.Amount > tblRANK.Amount
Or (Z.Amount = tblRANK.Amount
And Z.pk >= tblRANK.pk))
) AS RANK
FROM tblRANK
ORDER BY COUNTRY, AMOUNT DESC
--
Marsh
MVP [MS Access]
|
|
|