Store huge array into Access database

I'm trying to store a huge array (600K+ elements) into Access database. Does
anyone know how? I tried to assigned each array element into each
field but Access 2000 had a limit of 255 fields. I saved to a binary file
but the program crashed with overflow error when it reached 8192th array
element (element type is Single so 8192th is 8192 * 4 = 32,768 bytes which
is the max. limit of record length). Any help would be appreciated.

Create a table with a Long Integer field (call it MyIndex) and another field
of the same type as the array (call it MyValue).

Store each array element as a separate row: the element number as the Long
Integer field, and its value as the other field.

For lngLoop = LBound(MyArray) to UBound(MyArray)
rsMyRecordset.AddNew
rsMyRecordset!MyIndex = lngLoop
rsMyRecordset!MyValue = MyArray(lngLoop)
rsMyRecordset.Update
Next lngLoop

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


"PN" <pn66@yahoo.com> wrote in message news:3c65672d$1@10.1.10.29...
>
> I'm trying to store a huge array (600K+ elements) into Access database.
Does
> anyone know how? I tried to assigned each array element into each
> field but Access 2000 had a limit of 255 fields. I saved to a binary file
> but the program crashed with overflow error when it reached 8192th array
> element (element type is Single so 8192th is 8192 * 4 = 32,768 bytes which
> is the max. limit of record length). Any help would be appreciated.
>

Thanks Doug.
By the way, do you know how many records can an Access2K hold? If I have 1
million records, should I consider other databases such as Oracle or MSSQL
?

"Douglas J. Steele" <djsteele@canada.com> wrote in message
news:3c668e35$1@10.1.10.29...
> Create a table with a Long Integer field (call it MyIndex) and another
field
> of the same type as the array (call it MyValue).
>
> Store each array element as a separate row: the element number as the Long
> Integer field, and its value as the other field.
>
> For lngLoop = LBound(MyArray) to UBound(MyArray)
> rsMyRecordset.AddNew
> rsMyRecordset!MyIndex = lngLoop
> rsMyRecordset!MyValue = MyArray(lngLoop)
> rsMyRecordset.Update
> Next lngLoop
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
>
>
> "PN" <pn66@yahoo.com> wrote in message news:3c65672d$1@10.1.10.29...
> >
> > I'm trying to store a huge array (600K+ elements) into Access database.
> Does
> > anyone know how? I tried to assigned each array element into each
> > field but Access 2000 had a limit of 255 fields. I saved to a binary
file
> > but the program crashed with overflow error when it reached 8192th array
> > element (element type is Single so 8192th is 8192 * 4 = 32,768 bytes
which
> > is the max. limit of record length). Any help would be appreciated.
> >
>
>

There isn't a fixed number. A single MDB can be as large as 2 GB, but you
can link together multiple MDB files if you have to.

I've worked with database in excess of a million records.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


"PN" <pn66@yahoo.com> wrote in message news:3c66cb99$1@10.1.10.29...
> Thanks Doug.
> By the way, do you know how many records can an Access2K hold? If I have
1
> million records, should I consider other databases such as Oracle or
MSSQL
> ?
>
> "Douglas J. Steele" <djsteele@canada.com> wrote in message
> news:3c668e35$1@10.1.10.29...
> > Create a table with a Long Integer field (call it MyIndex) and another
> field
> > of the same type as the array (call it MyValue).
> >
> > Store each array element as a separate row: the element number as the
Long
> > Integer field, and its value as the other field.
> >
> > For lngLoop = LBound(MyArray) to UBound(MyArray)
> > rsMyRecordset.AddNew
> > rsMyRecordset!MyIndex = lngLoop
> > rsMyRecordset!MyValue = MyArray(lngLoop)
> > rsMyRecordset.Update
> > Next lngLoop
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> >
> >
> > "PN" <pn66@yahoo.com> wrote in message news:3c65672d$1@10.1.10.29...
> > >
> > > I'm trying to store a huge array (600K+ elements) into Access
database.
> > Does
> > > anyone know how? I tried to assigned each array element into each
> > > field but Access 2000 had a limit of 255 fields. I saved to a binary
> file
> > > but the program crashed with overflow error when it reached 8192th
array
> > > element (element type is Single so 8192th is 8192 * 4 = 32,768 bytes
> which
> > > is the max. limit of record length). Any help would be appreciated.
> > >
> >
> >
>
>