Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 5th, 2003, 08:59 PM
Registered User
Join Date: Aug 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default VB module fails to work when data > 170,000 tuples

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")

record_total = rs.RecordCount

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


End Sub

can anyone help??


Old August 6th, 2003, 01:31 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts


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.
Old August 6th, 2003, 06:59 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG


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

Similar Threads
Thread Thread Starter Forum Replies Last Post
DTC fails to work with SQL Server after changing t titojermaine SQL Server 2005 0 July 24th, 2007 07:24 AM
Chapter 4 - page 170 stzd8 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 0 November 5th, 2006 12:04 AM
DTS Fails when called from VB levinll SQL Server DTS 1 November 10th, 2005 02:05 PM
VB.Net -> Filename -> DTS Package -> tempdB daniel Pro VB.NET 2002/2003 1 October 7th, 2004 01:46 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.