<div>
follow up comment. &nbsp;The adp is bound to an mdb where the connections are.<br />
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?
</div>


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?

<div>
<div class="content wysiwyg-content">
I have an Access 2003 ADP that is linked to a .mdb. &nbsp;The sysem outputs a complex word document and does so by calling numerous sql statements to retrieve data. &nbsp;When we upgraded from SQL2000 to SQL2005 performance went downhill. &nbsp;In some cases the program can not connect to SQL server at all.<br />
So, how can I update/optimize the sql statements and the method I use to call them so they will perform at their best?<br />
<br />
Here is the connect string I am using<br />
&nbsp; &nbsp; Dim conn As ADODB.Connection<br />
&nbsp; &nbsp; Set conn = New ADODB.Connection<br />
&nbsp; &nbsp; conn.ConnectionString = &quot;Provider=Microsoft.Jet.OL<wbr />EDB.4.0;Da<wbr />ta Source=C:\SALEDB\Sale_DocB<wbr />roker.mdb;<wbr />User Id=;Password=;&quot;<br />
<br />
<br />
Here is a connect string I am trying to make work<br />
&nbsp; &nbsp; 'conn.ConnectionString = &quot;ODBC;Driver={SQL Native Client};Server=EAS_SBSERVE<wbr />R;Database<wbr />=Sale_DB;T<wbr />rusted_Con<wbr />nection=ye<wbr />s;&quot;
</div>
</div>


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;"

Access 2003 adp, mdb and SQL Server 2005 Performance Issues

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.