Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC Connections (MS Access 97 )

    I have been unfortunate to have taken on a job of updating a MS Access 97 database with a SQL Server backend. All the connections have been hardcoded. I have modified this in all forms and procedures so that the connection string is a global variable, because I want to be able to switch the backend database to a test database so that I can test changes I make. However there are hundreds of Passthrough queries which have a hardcoded ODBCConnectStr within the query properties, is there a quick way to update the properties on all queries, as I will need to switch them to point to different databases depending on whether I need to use the Live SQL Server database or the Test one.

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

    Re: ODBC Connections (MS Access 97 )

    We typically modify the PassThrough query on the fly when we use them, and we set the connection string to whatever database we want to run the PassThrough query against. The primary use of PassThrough queries is to force the query to be run by SQL Server rather than passing a large dataset back to the workstation. So in general you are passing a dynamic criteria as part of the query hoping to get one or at most a few records. If the PassThrough queries aren't doing that sort of thing, there is little or no benefit from using them, as simple queries - one table or two table joins - typically get passed to SQL Server by the ODBC driver. Hope this helps.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Connections (MS Access 97 )

    Here's the thing I want to be able to do.

    On start up I want to point everything to either the test or live SQL Server. The pass though queries over 80 of them run stored procedures, executing some actions as well as sometimes returning recordsets. I don't want anyone to run these against the wrong server. There are only 5 linked tables, which I need to relink as well. I just want a quick change all routine to run so that no mistakes can be made, when any of these stored procedures get called.

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

    Re: ODBC Connections (MS Access 97 )

    First of all, I presume each user has their own copy of the Access front-end installed on their workstation - if that is not the case then alternatives need to be considered.

    Relinking to the linked tables should be fairly straightforward - see Relink ODBC Tables From Code from the Access Web for sample code. The Pass Through queries will need to be dealt with in a more organized fashion - it will likely involve running a procedure that walks through every query in the front-end database and set the connection string. This MSDN Article should give you some clues as to how to proceed. It will involve opening each QueryDef and checking to see if it is the dbSQLPassThrough option set. If you have 80 of these, you may find the time required to do this is substantial, and users will find the performance less than acceptable.

    You don't indicate the version of SQL Server that you are working with, but if it is SQL Server 2000 or later, my suspicion is that a significant number of the queries could be replaced by indexed views that you attach to just as you do a table. You also don't indicate whether you are upgrading to a newer version of Access - which I would strongly recommend. Let's continue the dialog and see where it leads us.
    Wendell

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Connections (MS Access 97 )

    Each user will have their own database copy pointing to the same database on one of 3 servers depending on the users location. Currently it is set up at one site with one user. In an ideal world I agree Views would be the way to go, however I
    was trying to avoid having to make so many modifications to this very complex database. I was hoping to find a quick fix, without which I've got a serious amount to rewrting to do. If I could get a procedure running to change the connection string in all the pass through queries this only needs running once, when I move the database from test to production or across to a new site, it wouldn't matter how long that takes - within reason that is.

    Its a SQl Server 2000 database. I cannot update Access as its due for replacement in 6 months they don't want to spend any more time and effort on it than they can avoid.

    In the Ideal world companies would not let idiots design and develop databases........

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

    Re: ODBC Connections (MS Access 97 )

    It does sounds like VBA procedure to sweep through the QueryDefs collection would be feasible if it's only done at deployment time. I should think it would only take a few minutes at most to run the sweep. But I don't understand your comment about being do for replacement in 6 months. Is the SQL Server database being replaced, or are they doing some sort of different interface, or what? Things that are going to be replaced in 6 months often aren't replaced for 12 to 18 months - and sometimes never.
    Wendell

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Connections (MS Access 97 )

    All SQL Server and MS Access databases are going to be replaced by an all singing all dancing Company management system - Well they think this will happen anyway.

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

    Re: ODBC Connections (MS Access 97 )

    I've worked on projects where they were going to replace what we were doing, and in at least two cases, 3 years and several million later, our stuff is still running. Imagine that.
    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
  •