hi all,

ok this is the scenario....

I have a table containing roughly 2,500,000 records of mixed record types. e.g.

440, 123456, peoples avenue, ab12 2lx
441, abc, 19970801
451, _A, metering point, available
460, EELLC, 19970901
440, 123457, an address road, bc13 4vx
441, abd, 19980802
451, _A, metering point, occupied
460, EELLC, 19980901
440 ....... [and so on ...]


basically I have imported this file into ACCESS 2000 and need to seperate each record type (i.e. 451, 441 etc...) to their corresponding 440 record

The way I thought to doing this is to add an additional field called 'sortKey'. Whereby for every 440 record sortKey = sortKey + 1

whereby the output would look like this:

440, 123456, peoples avenue, ab12 2lx, 1 (sortKey)
441, abc, 19970801, 1
451, _A, metering point, available, 1
460, EELLC, 19970901, 1
440, 123457, an address road, bc13 4vx, 2 (sortKey + 1)
441, abd, 19980802, 2
451, _A, metering point, occupied, 2
460, EELLC, 19980901, 2


Now, I have managed to achieve this writing a Vb module within ACCESS however ACCESS will only run my Vb code providing the total number of records is no more than 170,000 otherwise I get the error 'Invalid Argument'. After some extensive testing it appears this error comes as a result of some sort of memory overload(??)

Is there a way round this???

here is the vb code I'm using:

Sub ProcessTables()

DoCmd.OpenQuery "AlterTable_sortKey", acViewNormal, acEdit 'SQL to ALTER TABLE data204 ADD sortKey varchar;

Dim db As Database
Dim rs As DAO.Recordset
Dim record_total As Long
Dim current_count As Integer

current_count = 0
record_total = 0

Set db = CurrentDb()
Set rs = db.OpenRecordset("data204")

rs.MoveFirst
record_total = rs.RecordCount

Do While Not rs.EOF
    rs.Edit
    If rs("Field1") = "440" Then
        current_count = current_count + 1
        rs("sortKey") = current_count
    Else
        rs("sortKey") = current_count
    End If

    rs.Update
    rs.MoveNext
Loop
rs.Close

End Sub

can anyone help??
thanks

Andy

Andy

First suggestion: Your variable current_count is an integer, which isn't big enough to hold the half a million record numbers you need. Change it to a long.

If this doesn't help, let me know exactly which line is giving an error and I'll look again.


Brian Skelton
Braxis Computer Services Ltd.

Andy,

Brian is correct. The Integer datatype maxes out at 32,767 whereas the Long datatype maxes out at 2,147,483,647. The advantage of using integers over longs is that each integer takes up only 2 bytes of memory (compared to the long's 4) and For/Next loops are slightly faster. The drawback is the lower max limit.

Note: if you ever use numbers that max out at 255, use the datatype Byte. Each value will only take up one byte of memory.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division