Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking tables with many records (Access 2000)

    I am working on an Access database with a client and he would like to view data in Access from an existing Sybase database that is quite large. Normally he runs queries from Excel using MS Query and each query takes approximately 2 hours. He would like to view the tables and re-create his queries in Access. We set up linked tables already using ODBC, however because the Sybase database is so large, it takes a really long time to open up one table in Access to even view the data.
    Is there a way to filter information in tables before linking? I'm wondering what the best way to approach this is with the goal being having access to current data and making Access run faster.

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking tables with many records (Access 2000)

    The only way to really speed things up is from the Server side, with stored procedures. Viewing and querying very large tables through an ODBC link is a frustrating exercise and brings Access to its knees. The only things I can suggest is to make sure the Name AutoCorrect is turned off and turn off all subdatasheets in the Access database, but those will probably not be enough to give you much improvement.

    The way you "filter before linking" is with stored procedures and views in the server. One alternative is to use ADO in Access and work with recordsets rather than with tables and ODBC links. Using ADO can permit you to shove the processing to the server and let it return just the records you need, but it requires a different approach than DAO because it is not optimized for Access and the exposed methods and properties depend on the ADO provider used.
    Charlotte

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

    Re: Linking tables with many records (Access 2000)

    In addition to Charlotte's comments, which are spot on, there are a couple of other tricks you can use to speed things up. Opening tables in Access will always be slow because it requires bringing across the entire table - but something that takes 2 hours is longer than I've ever been willing to put up with. If you have a solid ODBC driver, and you are not trying to join local Access tables (or Excel worksheet data) with the Sybase tables, you can force the query to be run on the server by either using "pass through" queries or ODBC direct queries and reduce the amount of data returned to the user dramatically. It does however require that you write the SQL string in the syntax that the server expects, not what Access would create. You can also create permanent views in Sybase as Charlotte suggested, but that requires that you have access to the server or have a cooperative administrator. If you have a situation where you can constrain the data with a query in Access, you can also get lucky as the ODBC driver will create the SQL string necessary to actually execute on the server. For example you might request that the server only return the TOP 100 records based on date entered.

    There are a number of other issues such as updatability and indexing that have impact as well - if you want to explore those please post back.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables with many records (Access 2000)

    Well thank you both very much for your very helpful information. I will leave it as is for now and pass these suggestions onto the client. It's his decision as to how far he wants to take this since I am limited to providing support to Access and not the server side stuff. I'm sure this will give him lots to think about.

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

    Re: Linking tables with many records (Access 2000)

    Actually both ODBC Direct queries and pass-through queries are done on the Access side of things. The challenge is getting the client to identify the subset of information that they want to look at. It can involve building forms to let the client select the criteria, and then building a query on the fly which Access then sends to the server. It works essentially the way that linked tables work, but specifies criteria in addition.

    Other things such as views and stored procedures are indeed done on the server, and present a more complex set of challenges.
    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
  •