Access 2003 adp, mdb and SQL Server 2005 Performance Issues

Posted on 2008-11-08
Last Modified: 2013-12-05
I have an Access 2003 ADP that is linked to a .mdb.  The sysem outputs a complex word document and does so by calling numerous sql statements to retrieve data.  When we upgraded from SQL2000 to SQL2005 performance went downhill.  In some cases the program can not connect to SQL server at all.
So, how can I update/optimize the sql statements and the method I use to call them so they will perform at their best?

Here is the connect string I am using
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SALEDB\Sale_DocBroker.mdb;User Id=;Password=;"

Here is a connect string I am trying to make work
    'conn.ConnectionString = "ODBC;Driver={SQL Native Client};Server=EAS_SBSERVER;Database=Sale_DB;Trusted_Connection=yes;"
Question by:KeithMcElroy

    Author Comment

    follow up comment.  The adp is bound to an mdb where the connections are.
    is there a way of setting the connectin one time for all of the functions instead of opening and closiing on each call to a function?
    LVL 17

    Accepted Solution

    For connection string options this is the bible:
    I suggest:
    Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;

    Author Closing Comment

    worked like a charm

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now