Re: ODBC Error

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 10:23:55 -0400

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...
>> 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
>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:
>> >>
>> >> >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]ConnectionCheckForData
>> >> >(CheckforData()).
>> >> >Server: Msg 11, Level 16, State 1, Line 0
>> >> >General network error. Check your network documentation.
>> >> >
>> >> >Connection Broken
>> >> >
>> >>
>> >
>>
>

Relevant Pages

  • Re: Updating a SQL table from a Local table - which is faster?
    ... I have a SQL table at the server that I need to update with a large ... Use an update query linking the SQL table to the local table. ... Rick Brandt, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • RE: Fulltext failure on a 2 node cluster
    ... Server full-text search resource online: "SQL Cluster Resource 'Full Text' ...
    (microsoft.public.sqlserver.clustering)
  • Re: HELP PLEASE ~ ???
    ... You mentioned that it went ahead and added a SQL ... SQL Server 2000 database for all my data. ... find the connectionString in the newly recreated SQLExpress database. ... The connection string specifies a local Sql Server Express instance ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Multi-Channel Raid VS SAN Storage
    ... A 5~6 years old server is a very old server. ... As I mentioned, the server is one node in a cluster environment, and SQL is ... We actually are running RAID 1+0 and our aplication is definately more ... needs it's own SAN device, or at least a dedicated IO channel on the SAN. ...
    (microsoft.public.sqlserver.setup)
  • Re: Multi-Channel Raid VS SAN Storage
    ... A 5~6 years old server is a very old server. ... As I mentioned, the server is one node in a cluster environment, and SQL is ... We actually are running RAID 1+0 and our aplication is definately more ... needs it's own SAN device, or at least a dedicated IO channel on the SAN. ...
    (microsoft.public.sqlserver.setup)