Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2003 Linking SQL Server (2003 no service packs)

    I am running Access 2003 with no service packs applied (that's what my IS area has given me; I have no control). They are converting from Oracle to SQL Server for our large data store database. I have several Access applications that extract data from this large database. Everything I am converting from Oracle to SQL Server ran fine using Access.

    Here's my problem. Many of my queries time out. Our IS area is working on changing indexes for the SQL Server tables, and that is helping. Not enough, in my opinion, as things still run slower than they did with Oracle. In some cases, the queries do not time out. I receive Restricted Error (-7711). The only reference I can find to that error in the Knowledge Base talks about a Sybase database. Is anyone familiar with that error? Could it be a service pack issue?

    I am also running slow computers. I have a Dell 270 and IBM T40, both with 1 gig of memory. My queries frequently extract close to 500,000 records which balloons the Access databases well beyond 1 gig in size. Could that be contributing to the time outs and the error message? I am hoping those of you more familiar with this will say yes. Oh, I am having SQL Server end user stuff installed in the next couple of weeks for another application I will be running. I am guessing these computers will grind to a painful halt.

    Any recommendations?

    Thanks! And welcome back, everyone.

    Nancy

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 Linking SQL Server (2003 no service packs)

    Hi Nancy,
    I understand the "I have no control" thing, but they really should install sp2 for you. Cite security, functionality, updates, anything you can so you can get it..... <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    Also you should have at least MDAC 2.8 installed.
    I'm assuming that you're moving to SQLServer 2005 and if so, your IS Dept. should be loading sp2 with it as well. I say that and we're still stuck on sp1 with a few exceptions here.
    The size of the extracted queries will have some bearing on the speed of Access displaying the records, etc., but if it is a connection timeout, then the query hasn't even been run yet, which makes me think that your network is in a bit of disarray what with the conversion and all, but there are some things you can do that may help later. Do not use ODBC, it is very slow doing a heavy load. Put any "heavy-load" type of data manipulation on SQLServer using a stored procedures.

    I have never experienced any noticeable speed difference between Access and SQLServer/Oracle unless it had to do with network load/traffic or bandwidth available and then both apps will slow down quite a bit with Access.

    As for Restricted Error (-7711), I have never seen this and could not find it. Although it very well could be referring to SQLServer because Microsoft bought the rights to make a new product based on the Sybase engine which became ms sqlserver and there are still quite a few similarities still.

    Sorry I can't give you anymore explicit information, it's just that there are a bunch of variables determining how fast the connection is and will react.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 Linking SQL Server (2003 no service packs)

    I have two versions of all the queries right now - one that goes against Oracle, one that goes against SQL Server. The Oracle queries will run - say 2 minutes. The SQL Server query will run. Run. Run. The time out.

    I use ODBC because that's all I have. No stored procedures for desktop users (so I've been told). My Oracle stuff runs fine and has for years. Our IS area thinks my department should buy me a third party software for $300 to extract the data, then import the extract into Access. As I told one of them today, "How does that fit with our company's process improvement objective? Add another step and use a product that takes longer to run adding more time to the process." He could tell I was a bit miffed. They cited an example where they could use this third party tool to run one of my queries in five minutes. Oh, rah. I told them I ran it in Oracle in less than one and was able to respond to their e-mail before my computer clock ticked over to the next minute.

    From the first I've told him it's at least partly an indexing issue. They played with some indexes and viola, some of the queries ran. We had major issues getting Oracle indexed correctly so I'm not surprised this is taking time.

    It will be an adventure until it gets resolved, I'm sure.

    Nancy

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access 2003 Linking SQL Server (2003 no service packs)

    There are lots of possible performance issues in attaching to a server system using ODBC whether it is to an Oracle DB, a SQL Server DB, an IBM DB2 or mySQL. I agree with Gary's comments completely, but there are some things you can do that may speed the process.
    <UL><LI>First, make sure your query timeout in Access is set to either a large number or 0 so you don't get timeout errors.
    <LI>Second, make sure you aren't joining any local Access tables to the SQL Server tables - that will really slow the process.
    <LI>Third, if you can't create stored procedures or views in SQL Server, then consider using PassThrough queries - that lets the query be optimized by SQL Server and actually done on the server.
    <LI>Fourth, insist that the tables all have a Primary Key, and are properly set up with referential integrity, which will cause foreign key indexs to be created (that may be a tough thing to sell to your IS staff, as the end user isn't supposed to tell them how to do the job, but it will help substantially).
    <LI>Finally, unless there is a compelling reason to return 500,000 records to Access, I would find a way to reduce the size of the dataset being returned. One reason for processing that many records is to create statistics, but SQL Server will do that almost as well as Access by using a group by view, and you can link to a SQL Server view just like you do to a table. Using those sort of tricks, we routinely return a small subset of records from tables with 5 to 10 million rows in under a second.[/list]Hope this helps.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •