[AccessD] Inquiry System Built With Access 2007 ? PerformanceConcern - Partial Key

Hi Brad:

Pulling data from a table of only 2000 records should be almost
instantaneous. (I have pulled 100K from an MS SQL server in less than 2
seconds...)

If there are 2000 rows pull it all with no keys or restriction. The fastest
method is using a forward-only recordset...

<example>

Public Function GetMyData()

Dim objCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim gstrConnection as String

' Your ODBC connection string data
gstrConnection = "Provider=SQLOLEDB;Initial Catalog=ThePartsDatabase;
                  Data Source=TheMSSQLServerName;Integrated Security=SSPI"

' Your recordset command parameters    
Set objCmd = New ADODB.Command

With objCmd
	.ActiveConnection = gstrConnection
      .CommandText = "My MSSQL PartsTable name"
      .CommandType = adCmdTable
End With

' Open a new recordset instances and populate it.
' Notice that we use the forward only recordset and minimal lock type
' for speed    
Set adoRS = New ADODB.Recordset
adoRS.Open objCmd, , adOpenForwardOnly, adLockReadOnly

' Recordset adoRS is now filled...
With adoRS
	If .BOF = False And .EOF = False Then
...

</example>

After that you can open a blank table and fill it with the contents of the
Recordset and manipulate to your hearts content.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Saturday, February 23, 2013 3:20 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Inquiry System Built With Access 2007 ?
PerformanceConcern - Partial Key

All,

We have an Inquiry System that obtains data from a purchased package's SQL
Server database via ODBC.

The Inquiry System works nicely and response time is quite fast for all
screens.

We are not able to change any of the tables, keys, relationships, indexes,
etc. in any of the tables in the purchased system.  We can only read the
data.  
 
Recently, a request has been made where we will need to pull data that will
come from a table that has not been used by the Inquiry System before.

Here is the catch.  The new table that we now want to pull data from has a
two-part key (Part_ID, and Routing_Sequence_Number).

Currently the Inquiry System does not have the second part of the key
available.  Only the Part_ID  is available, not the Routing_Sequence_Number.
(Not all Parts have routings, all we need to discern is whether a part has a
routing or not) 

When we try to obtain data from this table with only the Part_ID, the data
is returned, but response time is slow.   This table has about 2,000 rows. 

I am considering two approaches and decided to see if anyone else has run
into something similar and has advice.

Approach-1.  Build an Access table from the SQL Server table, that has only
Part_ID for the key.

Approach-2. Use the SQL Server data to build an array when the inquiry
system is first initiated then obtain the data from the array.

I would guess that there may be other approaches that would work better.

I appreciate your assistance.

Thanks,
Brad