Recordset for Form with a subform
in [Forms]
|
Prev: DoCmd Apply Filter
Next: Subform record validation
From: Lisa M. Fida on 27 Jan 2010 13:57 MSAccess 2003 and SQLExpress 2005 in multiple user setting I have a front end Access database that needs to retrieve data from a back end Access database and from a SQL database. The front end has the forms and VBA code, the back end just has tables and queries. So far I'm only retrieving data from the Access back end at this time. I have code in the ParentForm_Load event that pulls data from two related tables (Invoices and Invoice Details) out of the back end and populates a DAO recordset. The question: how can I populate (or bind) the Parent form with the Invoices data and the subform with the related Invoice Details data (and be able to modify, delete, insert, etc.)? I'm using recordsets because of the multiple user setting. Is there a better way to achieve the goal without sacrificing performance? I originally had all the pertinent tables linked but noticed that the application slowed greatly when there is more than one person using it. Thanks in advance, -- Lisa M. Fida
From: ruralguy via AccessMonster.com on 28 Jan 2010 10:33 Your queries should also be in the FrontEnd. Do you have a persistant connection to the BackEnd? http://www.granite.ab.ca/access/performancefaq.htm Lisa M. Fida wrote: >MSAccess 2003 and SQLExpress 2005 in multiple user setting > >I have a front end Access database that needs to retrieve data from a back >end Access database and from a SQL database. The front end has the forms and >VBA code, the back end just has tables and queries. So far I'm only >retrieving data from the Access back end at this time. I have code in the >ParentForm_Load event that pulls data from two related tables (Invoices and >Invoice Details) out of the back end and populates a DAO recordset. The >question: how can I populate (or bind) the Parent form with the Invoices data >and the subform with the related Invoice Details data (and be able to modify, >delete, insert, etc.)? > >I'm using recordsets because of the multiple user setting. Is there a >better way to achieve the goal without sacrificing performance? I originally >had all the pertinent tables linked but noticed that the application slowed >greatly when there is more than one person using it. > >Thanks in advance, > -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
From: Lisa M. Fida on 28 Jan 2010 13:02 Why do the queries need to be in the front end? I'm getting data in both datasets (the one for the main form and the one for the subform). I just can't seem to get them to link together. I have the code set the LinkMasterField and LinkChildField values. When the code gets to the second one I get an error saying Access can't find the query. Since the datasets are filled why is it trying to find the query again? -- Lisa M. Fida "ruralguy via AccessMonster.com" wrote: > Your queries should also be in the FrontEnd. Do you have a persistant > connection to the BackEnd? > http://www.granite.ab.ca/access/performancefaq.htm > > Lisa M. Fida wrote: > >MSAccess 2003 and SQLExpress 2005 in multiple user setting > > > >I have a front end Access database that needs to retrieve data from a back > >end Access database and from a SQL database. The front end has the forms and > >VBA code, the back end just has tables and queries. So far I'm only > >retrieving data from the Access back end at this time. I have code in the > >ParentForm_Load event that pulls data from two related tables (Invoices and > >Invoice Details) out of the back end and populates a DAO recordset. The > >question: how can I populate (or bind) the Parent form with the Invoices data > >and the subform with the related Invoice Details data (and be able to modify, > >delete, insert, etc.)? > > > >I'm using recordsets because of the multiple user setting. Is there a > >better way to achieve the goal without sacrificing performance? I originally > >had all the pertinent tables linked but noticed that the application slowed > >greatly when there is more than one person using it. > > > >Thanks in advance, > > > > -- > RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 > > . >
From: ruralguy via AccessMonster.com on 28 Jan 2010 15:49 http://www.accessmvp.com/TWickerath/articles/multiuser.htm Lisa M. Fida wrote: >Why do the queries need to be in the front end? I'm getting data in both >datasets (the one for the main form and the one for the subform). I just >can't seem to get them to link together. I have the code set the >LinkMasterField and LinkChildField values. When the code gets to the second >one I get an error saying Access can't find the query. Since the datasets >are filled why is it trying to find the query again? >> Your queries should also be in the FrontEnd. Do you have a persistant >> connection to the BackEnd? >[quoted text clipped - 18 lines] >> > >> >Thanks in advance, -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
From: Lisa M. Fida on 1 Feb 2010 13:21
Thank you for the link. I will thoroughly read through this. I think I may have been going at this the wrong way. Thanks again! -- Lisa M. Fida "ruralguy via AccessMonster.com" wrote: > http://www.accessmvp.com/TWickerath/articles/multiuser.htm > > > Lisa M. Fida wrote: > >Why do the queries need to be in the front end? I'm getting data in both > >datasets (the one for the main form and the one for the subform). I just > >can't seem to get them to link together. I have the code set the > >LinkMasterField and LinkChildField values. When the code gets to the second > >one I get an error saying Access can't find the query. Since the datasets > >are filled why is it trying to find the query again? > >> Your queries should also be in the FrontEnd. Do you have a persistant > >> connection to the BackEnd? > >[quoted text clipped - 18 lines] > >> > > >> >Thanks in advance, > > -- > RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 > > . > |