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

Group,

Further testing has come up with the actual(?) problem.  If the subform 
already has records in it then a new record can be added.  However, if 
there are no records in the pop up form for the record on the main form, 
then an error 0 (reserved error) pops up when the record in the pop up form 
is saved.  The entered data then disappears.  When the form is closed and 
opened again then the record appears and extra records can be added no problem.

The pop up form is opened from another form and is filtered based on a 
field on the main form.  The stored procedure for the pop up 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 pop up form = tblCustServicePlans.  This 
was entered when all the tables belonged in the same database.  I have 
moved all of the tables into another database except for the unique 
table.  Now if I look at the dropdown list in the unique table property it 
is empty.  However, deleting this property value means that I am not able 
to add any records at all.

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 issue seems to be with resynching the records after the first on has 
been entered.  If I open the form on my development computer having 
connected to the SQL database remotely on the server then I can add records 
but I get the problem mentioned at the beginning of this message.  Trying 
to run the same programme in a runtime environment directly on the server 
doesn't even let me add a first record.

Any thoughts?

David