Access 2003 adp, mdb and SQL Server 2005 Performance Issues

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;"
KeithMcElroyAsked:
Who is Participating?
 
HoggZillaConnect With a Mentor Commented:
For connection string options this is the bible:
 http://www.connectionstrings.com/?carrier=sqlserver2005
I suggest:
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
0
 
KeithMcElroyAuthor Commented:
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?
0
 
KeithMcElroyAuthor Commented:
worked like a charm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.