Access Runtime - Inconsistant Saving

[Nuttychick]

I have a client\server database that is used by many users via Access 2003
Runtime - the application works fine for most, however one form that saves
data to a table using a SQL Insert statement, taking info from bound fields
from a subform on the form to an existing table, appears to fail if the users
are using Access Runtime...if full Access is installed the problem is
resolved.

The data just isn't saved to the table...is this a known problem with Access
Runtime? or am I barking up the wrong tree....

 

[Tom van Stiphout]

On Mon, 22 Sep 2008 02:34:09 -0700, Nuttychick

Not a known problem.
How are you inserting? Show us the code.
Is this the only Insert statement in the entire app?

-Tom.
Microsoft Access MVP

 

[Nuttychick]

Here is the insert code...insert code is used elsewhere, I'll double check
but it appears to be working fine.

dim CreateException as string

CreateException = "Insert Into
tblexceptionReports(ExceptionStudyID,ExceptionReportCreatedBy,ExceptionReportCreationDate,CurrentVRF,CurrentLandingSlot,CurrentPlanComplete,
CurrentReqtsSolWShop,CurrentAlignmentReview,CurrentDSBBidPictureEvent,CurrentCostBenefitEvent,CurrentDSBDetailedEvent,
CurrentITExecutiveReview,
CurrentITSupplierPropIssued,CurrentSellByDate,CurrentViabilityReport,CurrentProgramBoard,
CurrentProposedImplementation,ProposedVRF,ProposedLandingSlot,ProposedPlanComplete,ProposedReqtsSolWShop,ProposedAlignmentReview,ProposedDSBBidPictureEvent,ProposedCostBenefitEvent,ProposedDSBDetailedEvent,ProposedITExecutiveReview,ProposedITSupplierPropIssued,ProposedSellByDate,ProposedViabilityReport,ProposedProgramBoard,ProposedProposedImplementation,ReasonVRF,ReasonLandingSlot,ReasonPlanComplete,ReasonReqtsSolWShop,ReasonAlignmentReview,ReasonDSBBidPictureEvent,ReasonCostBenefitEvent,ReasonDSBDetailedEvent,ReasonITExecutiveReview,ReasonITSupplierPropIssued," & _
"ReasonSellByDate," & _
"ReasonViabilityReport,ReasonProgramBoard,ReasonProposedImplementation,CurrentSpendBudget,ProposedNewBudget,BudgetApprovedByAspire,BudgetApprovedByHMRC,ReportComplete,ReportPeriod,OverallRag,ExcetionReportRagID,[RAG
Reason],CompletedVRF,CompletedLandingSlot,
CompletedPlanComplete,CompletedReqtsSolWShop,
CompletedAlignmentReview,CompletedDSBBidPictureEvent,
CompletedCostBenefitEvent, CompletedDSBDetailedEvent,
CompletedITExecutiveReview, CompletedITSupplierPropIssued,
CompletedSellByDate,CompletedViabilityReport,
CompletedProgramBoard,CompletedProposedImplementation,ReportNotes,BudgetIncreaseReason,ReportReason)" & _
"values
([forms]![frmNewExceptionREport]![ExceptionStudyID],[forms]![frmNewExceptionREport]![ExceptionReportCreatedBy],Date(),[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastVRF],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastLandingSlot]," &
"[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastplanComplete],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastReqSolWshop],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastAlignment],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastDSBBgPic],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastCostBenefitWShop],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastDSBDetail],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastExecRev],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastITSuppPropIss],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastSellByDate],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastViabilityReport],[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![LastProgramBoard]," &
"[Forms]![frmNEWExceptionReport]![frmExceptionReportStaticData]![Text48],[forms]![frmNewExceptionREport]![ProposedVRF],[forms]![frmNewExceptionREport]![ProposedLandingSlot],[forms]![frmNewExceptionREport]![ProposedPlanComplete],[forms]![frmNewExceptionREport]![ProposedReqtsSolWShop],[forms]![frmNewExceptionREport]![ProposedAlignmentReview]," &
"[forms]![frmNewExceptionREport]![ProposedDSBBidPictureEvent],[forms]![frmNewExceptionREport]![ProposedCostBenefitEvent],[forms]![frmNewExceptionREport]![ProposedDSBDetailedEvent],[forms]![frmNewExceptionREport]![ProposedITExecutiveReview],[forms]![frmNewExceptionREport]![ProposedITSupplierPropIssued],[forms]![frmNewExceptionREport]![ProposedSellByDate],[forms]![frmNewExceptionREport]![ProposedViabilityReport],[forms]![frmNewExceptionREport]![ProposedProgramBoard],[forms]![frmNewExceptionREport]![ProposedProposedImplementation],[forms]![frmNewExceptionREport]![VRFReason],[forms]![frmNewExceptionREport]![LandingSlotReason],[forms]![frmNewExceptionREport]![PlanCompleteReason],[forms]![frmNewExceptionREport]![ReqSolReason],[forms]![frmNewExceptionREport]![AlignmentReason],[forms]![frmNewExceptionREport]![DSBBigReason]," &
"[forms]![frmNewExceptionREport]![CostReason],[forms]![frmNewExceptionREport]![DSBReason],[forms]![frmNewExceptionREport]![ITExecReason],[forms]![frmNewExceptionREport]![ITSuppReason],[forms]![frmNewExceptionREport]![SellByReason],[forms]![frmNewExceptionREport]![VORReason],[forms]![frmNewExceptionREport]![ProgReason],[forms]![frmNewExceptionREport]![PropImpReason],[forms]![frmNewExceptionREport]![CurrentSpendBudget],[forms]![frmNewExceptionREport]![ProposedNewBudget],[forms]![frmNewExceptionREport]![BudgetApprovedByAspire],[forms]![frmNewExceptionREport]![BudgetApprovedByHMRC],[forms]![frmNewExceptionREport]![ReportComplete]," & _
"[forms]![frmNewExceptionREport]![ReportPeriod],[forms]![frmNewExceptionREport]![OvallRag],[forms]![frmNewExceptionREport]![ExcetionReportRagID],[forms]![frmNewExceptionREport]![RAGReason],[forms]![frmNewExceptionREport]![CompletedVRF],[forms]![frmNewExceptionREport]![CompletedLandingSlot],[forms]![frmNewExceptionREport]![CompletedPlanComplete],[forms]![frmNewExceptionREport]![CompletedReqtsSolWShop],[forms]![frmNewExceptionREport]![CompletedAlignmentReview],[forms]![frmNewExceptionREport]![CompletedDSBBidPictureEvent],[forms]![frmNewExceptionREport]![CompletedCostBenefitEvent],[forms]![frmNewExceptionREport]![CompletedDSBDetailedEvent],[forms]![frmNewExceptionREport]![CompletedITExecutiveReview],[forms]![frmNewExceptionREport]![CompletedITSupplierPropIssued],[forms]![frmNewExceptionREport]![CompletedSellByDate],[forms]![frmNewExceptionREport]![CompletedViabilityReport],[forms]![frmNewExceptionREport]![CompletedProgramBoard]," & _
"[forms]![frmNewExceptionREport]![CompletedProposedImplementation],[forms]![frmNewExceptionREport]![ReportNotes],[forms]![frmNewExceptionREport]![BudgetIncreaseReason],[forms]![frmNewExceptionREport]![ReportReason])"
DoCmd.RunSQL CreateException
DoCmd.SetWarnings True

 

[Tony Toews [MVP]]

DoCmd.RunSQL CreateException
DoCmd.SetWarnings True

You can't see any error message at the other PC with RunSQL.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/