Max RecordsetSize

[cwarner]

Do you know what the maximum number of rows
a recordset can have? Or is it limited by
the 1GB size of the Access DB? I have
successfully processed 500,000 but plan to
process more. I especially want to be able
to process 2 million +, by reading data
directly into SQL server and processing the
data via recordsets or stored procs in SQL.

Thanks!
Christy.

 

[scriverb]

I believe you are only limited by the total size of the ACCESS database. Now 1GB is the limit for A97. But, I believe 2 GB is the max for A2000+. Are you saying that you want to store 2+ million records in an access database and then process subsets to the SQL server for processing? Please confirm if this is correct.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[cwarner]

yes - but I don't want to be limited by
Access' database size at all, that is why
I am migrating to SQL Server as my back-end.
So I thought:

import any datbase directly to SQL Server,
via Access, and then process the data as
needed using recordset logic. But I don't
think I could do this of the recordset itself
has a size-limit....

thanks!
C.

 

[scriverb]

So, you have this huge set of tables and records in an access backend. Now you want to migrate to a SQL server. How many records are you dealing with. You should be able to use a series of query calls to pass the data through your network. You could link to the SQL server tables that you want to send you data to and then just run an ACCESS query that reads your current backend tables and passes the data in an append query to your SQL server tables.

This could be done in smaller subsets of total table.

Please post back if I am misinterpreting your situation.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[cwarner]

Hi again,

Same question posed a different way....
I don't want to store any data permanently
in my Access front-end (SQL Server back-end).

I want to be able to open SQL server tables
into Recordsets - but are Access recordsets
somehow limited by the 2GB db limitation?
Or are they stored in a temporary buffer and
not written permanently to the database?

Thanks for any help! I want to be able to
process millions of records in my Access
front-end w/ SQL Server back-end. I'll eventually
move to VB as the front-end, but this is an
intermediate step.

Christy.

 

[scriverb]

The problem with linking to a huge back-end database and then using an ACCESS query's recordset to manipulate the data, is that yes the access query tries to read all of the records into a temp location before beginning to process the records. It is for this reason ACCESS provides the capability to create "pass-through" queries that although housed in the ACCESS front-end they connect through your network to the backend and actually run on the SQL server. This allows for a much small recordset to actually be passed to the front-end database. Much more manageable.

I have had huge databases as you described when I linked to them through an ODBC connection, and then ran a query against the file get completely bogged down as the entire recordset is being read and passed to the local PC for execution. Definately a big problem. But, through pass-thru's and a little creativity you can design a process to handle you situation.

Post back if you have more questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[cmmrfrds]

Any limitation of moving data to the client for processing will be the same in VB as in Access. This will be limited by the OLEDB provider. Is there a reason you would not do all the processing needed on sql server?