sql server odbc: ODBC Error -- sql server odbc

Re: ODBC Error

Mary Chipman
4/16/2004 8:50:42 AM

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"
[quoted text, click to view]

Re: ODBC Error

Ron Hinds
4/16/2004 5:09:15 PM

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.

[quoted text, click to view]

Re: ODBC Error

Mary Chipman
4/17/2004 9:55:20 AM

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"
[quoted text, click to view]

Re: ODBC Error

Ron Hinds
4/19/2004 2:08:04 PM

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

[quoted text, click to view]

Re: ODBC Error

Mary Chipman
4/20/2004 10:23:55 AM

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"
[quoted text, click to view]

RE: ODBC Error

Ferde
6/18/2004 1:27:01 PM

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 tables create multiple connections and are prone to creating deadlocks and multitudes of other problems. Linked tables in Access 97 worked 'OK', linked tables in 2000 and on are not usable in a production system.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server odbc group.