ODBC Error

I get the following error (from my front-end Access app AND Query Analyzer)
when accessing a particular table. It is a rather large table, ~1,750,000
rows in it. I tried looking through "limitations" in BOL and couldn't find
anything. Is this a server setting or ODBC setting or... ?
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionChe
ckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection BrokenWhat are you trying to do with that much data? Access isn't designed
to handle scrolling through millions of rows, although you may be
having network issues, as the error message suggests. Restrict the
data fetched with a WHERE clause, or if it's a report, write a stored
procedure and call it through a pass-through query.
--mary
On Thu, 15 Apr 2004 17:03:47 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>I get the following error (from my front-end Access app AND Query Analyzer)
>when accessing a particular table. It is a rather large table, ~1,750,000
>rows in it. I tried looking through "limitations" in BOL and couldn't find
>anything. Is this a server setting or ODBC setting or... ?
>[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCh
eckForData
>(CheckforData()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation.
>Connection Broken
>|||What I'm trying to do is populate a local table (BackOrders) that's actually
used in the form from a server table (SalesDetail). There is a constraint
(WHERE Customer=x AND QtyAvailable>0) but using DAO it still tries to fetch
the entire table. Here is the original SQL (pure Access 97 app):
INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered,
QtyAvailable, Description) SELECT SalesDetail.InvoiceNumber,
SalesDetail.InvoiceDate, SalesDetail.Part, SalesDetail.Price,
SalesDetail.QtyOrdered, Inventory.QtyAvailable, Inventory.Description FROM
SalesDetail INNER JOIN Inventory ON SalesDetail.Part = Inventory.Part WHERE
SalesDetail.Customer=x AND SalesDetail.QtyOrdered > SalesDetail.QtyShipped
AND Inventory.Qty - Inventory.QtyCommitted > 0 AND SalesDetail.BackOrder > 0
ORDER BY SalesDetail.invoiceDate DESC
Even if this worked as-is with SQL Server (it doesn't - gives the same
error), it would be horribly slow, so I wanted to optimize it by creating a
View of the server tables. But I don't know the value of 'x' (Customer)
until run time. So I used the following DDL to create the View on SQL
Server:
USE Prototype
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vBackOrder')
DROP VIEW vBackOrder
GO
CREATE VIEW vBackOrder AS
SELECT TOP 100 PERCENT SalesDetail.invoiceNumber, SalesDetail.invoiceDate,
SalesDetail.Part, SalesDetail.Price, SalesDetail.QtyOrdered, Inventory.Qty -
Inventory.QtyCommitted AS QtyAvailable, Inventory.Description
SalesDetail.Customer FROM SalesDetail INNER JOIN Inventory ON
SalesDetail.itemID = Inventory.invItemID WHERE SalesDetail.QtyOrdered >
SalesDetail.QtyShipped AND Inventory.Qty - Inventory.QtyCommitted > 0 AND
SalesDetail.BackOrder > 0 ORDER BY SalesDetail.invoiceDate DESC
GO
I linked vBackOrder in Access then used this DAO code to populate local
table BackOrders:
INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered,
QtyAvailable, Description) SELECT vBackOrder.InvoiceNumber,
vBackOrder.InvoiceDate, vBackOrder.Part, vBackOrder.Price,
vBackOrder.QtyOrdered, vBackOrder.QtyAvailable, vBackOrder.Description FROM
vBackOrder WHERE vBackOrder.Customer=x
This also gives the same error, as does attempting to open the linked
vBackOrder in Access. I then went to QA and tried just the SELECT portion of
the CREATE VIEW with the same result. To narrow it down, I tried SELECTing *
FROM Inventory - a little slow but no problem. I then tried the same thing
with SalesDetail and again get the same error. I have >300 server tables of
varying sizes in this app. SalesDetail is by far the largest, and it is the
*only* one I get the error on. So I'm assuming it has something to do with
the size.
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:pflv701qqge68qf7u94r6bnetfkb1iuc53@.
4ax.com...
> What are you trying to do with that much data? Access isn't designed
> to handle scrolling through millions of rows, although you may be
> having network issues, as the error message suggests. Restrict the
> data fetched with a WHERE clause, or if it's a report, write a stored
> procedure and call it through a pass-through query.
> --mary
> On Thu, 15 Apr 2004 17:03:47 -0700, "Ron Hinds"
> <__NoSpam@.__NoSpamramac.com> wrote:
>
Analyzer)[vbcol=seagreen]
find[vbcol=seagreen]
>|||DAO is the problem. You're loading the Jet engine and using it for SQL
Server data operations, something it was never designed or optimized
to do. Create a stored procedure instead of a buinch of views. Stored
procedures support parameters and complex logic, and return a
read-only result set which you can use to populate your local table.
Call the stored procedure from a pass-through query where you set the
SQL syntax to something like this in your code (you can use DAO to set
properties of a QueryDef object and execute it):
qdef.SQL = "EXEC myproc 'paramvalue1', val2" etc.
qdef.Execute
Pass-through queries bypass the Jet engine when they're executed and
are the most efficient way of getting back large result sets since all
of the processing takes place on the server, not in Jet. You then
create either an Insert or Update query that selects from your
pass-through query into the local table. When you call the
insert/update query it will automatically execute the pass-through
query to get the records.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 16 Apr 2004 17:09:15 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>What I'm trying to do is populate a local table (BackOrders) that's actuall
y
>used in the form from a server table (SalesDetail). There is a constraint
>(WHERE Customer=x AND QtyAvailable>0) but using DAO it still tries to fetch
>the entire table. Here is the original SQL (pure Access 97 app):
>INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered
,
>QtyAvailable, Description) SELECT SalesDetail.InvoiceNumber,
>SalesDetail.InvoiceDate, SalesDetail.Part, SalesDetail.Price,
>SalesDetail.QtyOrdered, Inventory.QtyAvailable, Inventory.Description FROM
>SalesDetail INNER JOIN Inventory ON SalesDetail.Part = Inventory.Part WHERE
>SalesDetail.Customer=x AND SalesDetail.QtyOrdered > SalesDetail.QtyShipped
>AND Inventory.Qty - Inventory.QtyCommitted > 0 AND SalesDetail.BackOrder >
0
>ORDER BY SalesDetail.invoiceDate DESC
>Even if this worked as-is with SQL Server (it doesn't - gives the same
>error), it would be horribly slow, so I wanted to optimize it by creating a
>View of the server tables. But I don't know the value of 'x' (Customer)
>until run time. So I used the following DDL to create the View on SQL
>Server:
>USE Prototype
>GO
>IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
> WHERE TABLE_NAME = 'vBackOrder')
> DROP VIEW vBackOrder
>GO
>CREATE VIEW vBackOrder AS
>SELECT TOP 100 PERCENT SalesDetail.invoiceNumber, SalesDetail.invoiceDate,
>SalesDetail.Part, SalesDetail.Price, SalesDetail.QtyOrdered, Inventory.Qty
-
>Inventory.QtyCommitted AS QtyAvailable, Inventory.Description
>SalesDetail.Customer FROM SalesDetail INNER JOIN Inventory ON
>SalesDetail.itemID = Inventory.invItemID WHERE SalesDetail.QtyOrdered >
>SalesDetail.QtyShipped AND Inventory.Qty - Inventory.QtyCommitted > 0 AND
>SalesDetail.BackOrder > 0 ORDER BY SalesDetail.invoiceDate DESC
>GO
>I linked vBackOrder in Access then used this DAO code to populate local
>table BackOrders:
>INSERT INTO BackOrders (InvoiceNumber, InvoiceDate, Part, Price, QtyOrdered
,
>QtyAvailable, Description) SELECT vBackOrder.InvoiceNumber,
>vBackOrder.InvoiceDate, vBackOrder.Part, vBackOrder.Price,
>vBackOrder.QtyOrdered, vBackOrder.QtyAvailable, vBackOrder.Description FROM
>vBackOrder WHERE vBackOrder.Customer=x
>This also gives the same error, as does attempting to open the linked
>vBackOrder in Access. I then went to QA and tried just the SELECT portion o
f
>the CREATE VIEW with the same result. To narrow it down, I tried SELECTing
*
>FROM Inventory - a little slow but no problem. I then tried the same thing
>with SalesDetail and again get the same error. I have >300 server tables of
>varying sizes in this app. SalesDetail is by far the largest, and it is the
>*only* one I get the error on. So I'm assuming it has something to do with
>the size.
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:pflv701qqge68qf7u94r6bnetfkb1iuc53@.
4ax.com...
>Analyzer)
>find
>|||Hi Mary,
BTW I have your book and it is very good. Unfortunately, for this project I
am stuck with Access 97 and the book seems to reference Access 2000 +.
Thanks for your help and that not only works but it is *much* faster! Looks
like I'll be retrofitting all of those Views I created! Thanks again!
Regards,
Ron Hinds
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:c7d280ledkacc75n7ugk5jgd8v7bibuepa@.
4ax.com...
> DAO is the problem. You're loading the Jet engine and using it for SQL
> Server data operations, something it was never designed or optimized
> to do. Create a stored procedure instead of a buinch of views. Stored
> procedures support parameters and complex logic, and return a
> read-only result set which you can use to populate your local table.
> Call the stored procedure from a pass-through query where you set the
> SQL syntax to something like this in your code (you can use DAO to set
> properties of a QueryDef object and execute it):
> qdef.SQL = "EXEC myproc 'paramvalue1', val2" etc.
> qdef.Execute
> Pass-through queries bypass the Jet engine when they're executed and
> are the most efficient way of getting back large result sets since all
> of the processing takes place on the server, not in Jet. You then
> create either an Insert or Update query that selects from your
> pass-through query into the local table. When you call the
> insert/update query it will automatically execute the pass-through
> query to get the records.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
> On Fri, 16 Apr 2004 17:09:15 -0700, "Ron Hinds"
> <__NoSpam@.__NoSpamramac.com> wrote:
>
actually[vbcol=seagreen]
fetch[vbcol=seagreen]
QtyOrdered,[vbcol=seagreen]
FROM[vbcol=seagreen]
WHERE[vbcol=seagreen]
SalesDetail.QtyShipped[vbcol=seagreen]
> 0
a[vbcol=seagreen]
SalesDetail.invoiceDate,[vbcol=seagreen]
Inventory.Qty -[vbcol=seagreen]
QtyOrdered,[vbcol=seagreen]
FROM[vbcol=seagreen]
of[vbcol=seagreen]
SELECTing *[vbcol=seagreen]
thing[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
with[vbcol=seagreen]
~1,750,000[vbcol=seagreen]
Sockets]ConnectionCheckForData[vbcol=sea
green]
>|||Although the book is for a newer version, the basic concepts remain
the same, and always will, which are: fetch only needed data and
perform as much data processing on the back end. Let the FE do
presentation tasks like formatting, etc. All of the code in the
chapters for linked tables will work pretty much as-is in Access 97.
--Mary
On Mon, 19 Apr 2004 14:08:04 -0700, "Ron Hinds"
<__NoSpam@.__NoSpamramac.com> wrote:

>Hi Mary,
>BTW I have your book and it is very good. Unfortunately, for this project I
>am stuck with Access 97 and the book seems to reference Access 2000 +.
>Thanks for your help and that not only works but it is *much* faster! Looks
>like I'll be retrofitting all of those Views I created! Thanks again!
>Regards,
>Ron Hinds
>
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:c7d280ledkacc75n7ugk5jgd8v7bibuepa@.
4ax.com...
>actually
>fetch
>QtyOrdered,
>FROM
>WHERE
>SalesDetail.QtyShipped
>a
>SalesDetail.invoiceDate,
>Inventory.Qty -
>QtyOrdered,
>FROM
>of
>SELECTing *
>thing
>of
>the
>with
>~1,750,000
>Sockets]ConnectionCheckForData
>|||Ron,
Switch to an Access 2002 data project and dump the mdb. You are looking at
a complete rewrite. Access 2002 ADPs are VERY EASY to use. Access linked t
ables create multiple connections and are prone to creating deadlocks and mu
ltitudes of other problems.
Linked tables in Access 97 worked 'OK', linked tables in 2000 and on are not
usable in a production system.
"Ron Hinds" wrote:

> I get the following error (from my front-end Access app AND Query Analyzer
)
> when accessing a particular table. It is a rather large table, ~1,750,000
> rows in it. I tried looking through "limitations" in BOL and couldn't find
> anything. Is this a server setting or ODBC setting or... ?
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionC
heckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>