I am currently eperimenting with a client/server application with 
Access2000 as the front end and SQL 2000 Server as the backend.

In this experiment I have created an Access Data Project, an Access file 
with an .adp extension. The Access objects in an .adp file are slightly 
different than those in an .mdb file.

I am aware that when creating a view on the SQL Server using the 
Access2000 tools that there is a 10,000 record limit by default when 
viewing retrieved records that exceed this limit.

I am also aware that one can go into the Tools -- Options -- Advanced tab 
and change the default to 0. This is suppose to allow retrieval of all the 
records from one table or multi-table relationship.

I am trying to view a table which has over 1.5 million records in it. When 
I set the default to 0 or max records I keep getting an error. I didn't 
copy the exact error but it goes something along the lines of: "the data 
provider is not responding and servics has stop ENV_Fail". This is not 
exact but the point is that an error occurs every time I try and run this 
View. In addition, just before the error occurs I get a memory usage low 
warning.

1. How do I avoid this problem and view all the records in the table?

2. Exactly how do Views work with forms and combo boxes, et al.?

It probably simply can't handle transferring that many records at once.
That's the reason the default is set to 10G. 

Why would you want to view 1.5 million records in a table anyway?  Tables
aren't meant to be used for viewing - only storing.  In fact, in a correctly
normalised database, they *shouldn't* mean much on their own anyway - only
ID's and a few text fields.

As for your second question, I've found them to work pretty much the same..
what problems are your having?

Cheers,
Andrew

-----Original Message-----
From: Big Pappa [mailto:mdbriggs@y...]
Sent: Saturday, 21 September 2002 4:08 PM
To: Access
Subject: [access] Question about SQL Views?


I am currently eperimenting with a client/server application with 
Access2000 as the front end and SQL 2000 Server as the backend.

In this experiment I have created an Access Data Project, an Access file 
with an .adp extension. The Access objects in an .adp file are slightly 
different than those in an .mdb file.

I am aware that when creating a view on the SQL Server using the 
Access2000 tools that there is a 10,000 record limit by default when 
viewing retrieved records that exceed this limit.

I am also aware that one can go into the Tools -- Options -- Advanced tab 
and change the default to 0. This is suppose to allow retrieval of all the 
records from one table or multi-table relationship.

I am trying to view a table which has over 1.5 million records in it. When 
I set the default to 0 or max records I keep getting an error. I didn't 
copy the exact error but it goes something along the lines of: "the data 
provider is not responding and servics has stop ENV_Fail". This is not 
exact but the point is that an error occurs every time I try and run this 
View. In addition, just before the error occurs I get a memory usage low 
warning.

1. How do I avoid this problem and view all the records in the table?

2. Exactly how do Views work with forms and combo boxes, et al.?

You can link from your Access application to an SQL server view, then treat
it as a table for record source of you access objects. With a view you can
create a permanent connection while your application is in use, (just the
same as if you linked to an SQL table on startup). Processing is being done
by the server and with a little planning you can dramatically reduce network
traffic and impove performance.
Often the alternative is connecting to an SQL server Stored Procedure using
a passthrough query. A passthrough has the advantage of being able to pass
parameters to your Stored Proc, which can make a big reduction in network
traffic, but it makes a connection every time it is used - the decrease in
performance is obvious.
When you finnish making assumptions you test and evaluate how each method
works in your situation.
With regard to viewing 1.5 million records - you must appreciate that
without the error message  I can only make generalizations. A view
restricting the number of fields returned may be of help, but you are still
likely to be overloading the server, network and the memory of the client
machines. You could be overloading your users to. A human is not capable of
evaluating that number of records, I have never come accross a requirement
to do so. It is always a months transactions, a departments transactions, a
cost centres transactions, and they don't expect to see the whole table. It
might be interesting to understand your  Client's requirement there.

David
PS Quite likely with the error message I can only make generalizations too!

-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Saturday, 21 September 2002 7:50 PM
To: Access
Subject: [access] RE: Question about SQL Views?


It probably simply can't handle transferring that many records at once.
That's the reason the default is set to 10G.

Why would you want to view 1.5 million records in a table anyway?  Tables
aren't meant to be used for viewing - only storing.  In fact, in a correctly
normalised database, they *shouldn't* mean much on their own anyway - only
ID's and a few text fields.

As for your second question, I've found them to work pretty much the same..
what problems are your having?

Cheers,
Andrew

-----Original Message-----
From: Big Pappa [mailto:mdbriggs@y...]
Sent: Saturday, 21 September 2002 4:08 PM
To: Access
Subject: [access] Question about SQL Views?


I am currently eperimenting with a client/server application with
Access2000 as the front end and SQL 2000 Server as the backend.

In this experiment I have created an Access Data Project, an Access file
with an .adp extension. The Access objects in an .adp file are slightly
different than those in an .mdb file.

I am aware that when creating a view on the SQL Server using the
Access2000 tools that there is a 10,000 record limit by default when
viewing retrieved records that exceed this limit.

I am also aware that one can go into the Tools -- Options -- Advanced tab
and change the default to 0. This is suppose to allow retrieval of all the
records from one table or multi-table relationship.

I am trying to view a table which has over 1.5 million records in it. When
I set the default to 0 or max records I keep getting an error. I didn't
copy the exact error but it goes something along the lines of: "the data
provider is not responding and servics has stop ENV_Fail". This is not
exact but the point is that an error occurs every time I try and run this
View. In addition, just before the error occurs I get a memory usage low
warning.

1. How do I avoid this problem and view all the records in the table?

2. Exactly how do Views work with forms and combo boxes, et al.?

Actually this table is one huge *.csv file that is imported into one 
Access table. When linking to this file, which is in its own data .mdb 
file, and I create a query to view the records in this file it return 
every record. The problem is that it takes anywhere from 5 to 8 min. to 
process all those records. Thus my reason for trying to store this table 
on an SQL server. I am hoping it can process faster on a more powerful 
database and computer.

The reason for wanting to return that many records from time to time is 
because I am working on building a tool to do data analysis for a project 
I am working on. From time to time it is necessary to view that many 
records.

So, as I see it if I can view that many records using a slower and more 
cumbersome method I should be able to view that many records using, 
supposedly, a faster a more efficient method.

Regards
Marlon

> It probably simply can't handle transferring that many records at once.
That's the reason the default is set to 10G. 

Why would you want to view 1.5 million records in a table anyway?  Tables
aren't meant to be used for viewing - only storing.  In fact, in a 
correctly
normalised database, they *shouldn't* mean much on their own anyway - only
ID's and a few text fields.

As for your second question, I've found them to work pretty much the same..
what problems are your having?

Cheers,
Andrew

-----Original Message-----
From: Big Pappa [mailto:mdbriggs@y...]
Sent: Saturday, 21 September 2002 4:08 PM
To: Access
Subject: [access] Question about SQL Views?


I am currently eperimenting with a client/server application with 
Access2000 as the front end and SQL 2000 Server as the backend.

In this experiment I have created an Access Data Project, an Access file 
with an .adp extension. The Access objects in an .adp file are slightly 
different than those in an .mdb file.

I am aware that when creating a view on the SQL Server using the 
Access2000 tools that there is a 10,000 record limit by default when 
viewing retrieved records that exceed this limit.

I am also aware that one can go into the Tools -- Options -- Advanced tab 
and change the default to 0. This is suppose to allow retrieval of all the 
records from one table or multi-table relationship.

I am trying to view a table which has over 1.5 million records in it. When 
I set the default to 0 or max records I keep getting an error. I didn't 
copy the exact error but it goes something along the lines of: "the data 
provider is not responding and servics has stop ENV_Fail". This is not 
exact but the point is that an error occurs every time I try and run this 
View. In addition, just before the error occurs I get a memory usage low 
warning.

1. How do I avoid this problem and view all the records in the table?

2. Exactly how do Views work with forms and combo boxes, et al.?

Andrew,

Here is the error message that I keep getting:

Data provider or other service returned an E-Fail status

Marlon

> It probably simply can't handle transferring that many records at once.
That's the reason the default is set to 10G. 

Why would you want to view 1.5 million records in a table anyway?  Tables
aren't meant to be used for viewing - only storing.  In fact, in a 
correctly
normalised database, they *shouldn't* mean much on their own anyway - only
ID's and a few text fields.

As for your second question, I've found them to work pretty much the same..
what problems are your having?

Cheers,
Andrew

-----Original Message-----
From: Big Pappa [mailto:mdbriggs@y...]
Sent: Saturday, 21 September 2002 4:08 PM
To: Access
Subject: [access] Question about SQL Views?


I am currently eperimenting with a client/server application with 
Access2000 as the front end and SQL 2000 Server as the backend.

In this experiment I have created an Access Data Project, an Access file 
with an .adp extension. The Access objects in an .adp file are slightly 
different than those in an .mdb file.

I am aware that when creating a view on the SQL Server using the 
Access2000 tools that there is a 10,000 record limit by default when 
viewing retrieved records that exceed this limit.

I am also aware that one can go into the Tools -- Options -- Advanced tab 
and change the default to 0. This is suppose to allow retrieval of all the 
records from one table or multi-table relationship.

I am trying to view a table which has over 1.5 million records in it. When 
I set the default to 0 or max records I keep getting an error. I didn't 
copy the exact error but it goes something along the lines of: "the data 
provider is not responding and servics has stop ENV_Fail". This is not 
exact but the point is that an error occurs every time I try and run this 
View. In addition, just before the error occurs I get a memory usage low 
warning.

1. How do I avoid this problem and view all the records in the table?

2. Exactly how do Views work with forms and combo boxes, et al.?