• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

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;"
0
KeithMcElroy
Asked:
KeithMcElroy
  • 2
1 Solution
 
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
 
HoggZillaCommented:
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:
worked like a charm
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now