[dba-SQLServer] Getting Data from another Database (Speed)

Jim,

I don't think that is available.  The stored procedure is entered in the 
rowsource of the combo box.  Even in VBA there doesn't seem to be any 
settings for the rowsource property apart from the name of the stored 
procedure.

After subsequent testing it doesn't seem to be the combo box at all.  The 
delay now seems to be caused when a new record is inserted into the form 
for the first time.  The form is a continuous one.  Once it has been 
opened, and the first new record is inserted then there is no delay in 
inserting extra records.  There is also no delay in changing existing records.

The form is opened from another form and is filtered based on a field on 
the main form.  The stored procedure for the form is:

ALTER PROCEDURE spfrmCustServicePlans
         (
                 @txtMeterID Int
         )
AS

SET NOCOUNT ON

SELECT tblCustServicePlans.PlanID, tblCustServicePlans.MeterIDNo, 
tblCustServicePlans.ServiceRateIDNo,
         tblCustServicePlans.ConnectionDate, 
tblCustServicePlans.DailyDiscRate, tblCustServicePlans.Term,
         tblCustServicePlans.ExpiryDate, tblCustServicePlans.Inactive, 
tblCustServicePlans.DisconnectionDate,
         tblCustServicePlans.kWhDiscRate1, 
tblCustServicePlans.kWhDiscRate2, tblCustServicePlans.kWhDiscRate3,
         tblCustServicePlans.kWhDiscRate4, 
SQLLtdbe.dbo.tblServiceRates.DailyRate, 
SQLLtdbe.dbo.tblServiceRates.ServiceRateID,
         tblCustServicePlans.Gap
FROM SQLLtdbe.dbo.tblServiceRates INNER JOIN tblCustServicePlans ON
         SQLLtdbe.dbo.tblServiceRates.ServiceRateID = 
tblCustServicePlans.ServiceRateIDNo
WHERE tblCustServicePlans.MeterIDNo = @txtMeterID
ORDER BY tblCustServicePlans.Inactive, tblCustServicePlans.ConnectionDate DESC;

The Unique Table property of the form = tblCustServicePlans

The Resynch Command property is:

SELECT tblCustServicePlans.*, SQLLtdbe.dbo.tblServicePlans.SortOrder, 
SQLLtdbe.dbo.tblServiceRates.DailyRate,
         SQLLtdbe.dbo.tblServiceRates.ServiceRateID
FROM SQLLtdbe.dbo.tblServicePlans INNER JOIN SQLLtdbe.dbo.tblServiceRates ON
         SQLLtdbe.dbo.tblServicePlans.ServicePlanID = 
SQLLtdbe.dbo.tblServiceRates.ServicePlanIDNo INNER JOIN
         tblCustServicePlans ON SQLLtdbe.dbo.tblServiceRates.ServiceRateID 
= tblCustServicePlans.ServiceRateIDNo
WHERE tblCustServicePlans.PlanID = ?


The spfrmCustServicePlans stored procedure is also in the SQLLtdbe 
database.  When the front end is connected to this database there is no 
(well, comparatively very little) delay in adding the first new record.

Is it something to do with combining tables from different databases?

David

At 25/07/2005, you wrote:
>Hi David:
>
>My knowledge of ADP applications is non-existent. I have only worked with
>standard Access applications that attach to MS SQL/Oracle DBs. There must be
>some place in the ADP model that will allow you to set recordset parameters.
>
>Do you have access to the recordset CursorType?
>
>With rsMyRecordset
>  .CursorType = adOpenStatic
>End With
>
>I believe that it defaults to 'adOpenDynamic' and that is where your problem
>would be.
>
>HTH
>Jim