N
Nate Moore
I have created a database that keeps track of our four medical clinic's
billing and receipts. There are 3 mdb files, a front-end program interface
and two back-end databases, one that has data that is shared across all
clinics (a table for doctors, for referring physicians, insurance companies,
etc.) and one that has information unique to that clinic (the
invoices/medical claims and the detail files for that clinic's claims, etc.)
I have a couple of users of the database, some who access the data locally
and some via Terminal Server. The Terminal Server users are starting to
have trouble and are getting error messages saying they are out of stack
space and/or memory.
The error most often appears when they are accessing a form to record
insurance payments. The form's record source is a query that references
relevant fields from tblInvoiceHeader, which currently has 4,000+ records.
The form includes a subform whose record source is a query that references
relevant fields from tblInvoiceDetail, which currently has 56,000+ records.
Both tblInvoiceHeader and tblInvoiceDetail have a field lngInvoice. In the
properties of the subform I have set the Link Child Fields and Link Master
Fields to lngInvoice to synchronize the tblInvoiceHeader data on the main
form with the tblInvoiceDetail on the subform.
To limit the data, when the main form opens I only show a combo box and an
exit button. The combo box has an AfterUpdate event that selects the
correct lngInvoice field from the underlying query and the rest of the form
is displayed I do not do anything to limit the data on the subform, and
can't find any documentation to explain whether the Link Child/Master Fields
functionality limits the data displayed or not.
I apologize for the long explanation, but hope the background information is
helpful. I'm considering combining the 2 spearate back-end databases into
one (there are actually multiple back-end databases, one for each clinic and
some archived data going back prior years) and upgdrading to SQL Server. I
have MSDE 2000 on my machine and am trying to decide if I should begin
preparations to migrate to SQL Server.
My question is, have I set up this form and the related subform optimally
and therefore to improve performance I should migrate to SQL Server, or are
there things I should do in an Access environment to limit the data on the
form for Terminal Services users?
Many thanks in advance.
Nate
billing and receipts. There are 3 mdb files, a front-end program interface
and two back-end databases, one that has data that is shared across all
clinics (a table for doctors, for referring physicians, insurance companies,
etc.) and one that has information unique to that clinic (the
invoices/medical claims and the detail files for that clinic's claims, etc.)
I have a couple of users of the database, some who access the data locally
and some via Terminal Server. The Terminal Server users are starting to
have trouble and are getting error messages saying they are out of stack
space and/or memory.
The error most often appears when they are accessing a form to record
insurance payments. The form's record source is a query that references
relevant fields from tblInvoiceHeader, which currently has 4,000+ records.
The form includes a subform whose record source is a query that references
relevant fields from tblInvoiceDetail, which currently has 56,000+ records.
Both tblInvoiceHeader and tblInvoiceDetail have a field lngInvoice. In the
properties of the subform I have set the Link Child Fields and Link Master
Fields to lngInvoice to synchronize the tblInvoiceHeader data on the main
form with the tblInvoiceDetail on the subform.
To limit the data, when the main form opens I only show a combo box and an
exit button. The combo box has an AfterUpdate event that selects the
correct lngInvoice field from the underlying query and the rest of the form
is displayed I do not do anything to limit the data on the subform, and
can't find any documentation to explain whether the Link Child/Master Fields
functionality limits the data displayed or not.
I apologize for the long explanation, but hope the background information is
helpful. I'm considering combining the 2 spearate back-end databases into
one (there are actually multiple back-end databases, one for each clinic and
some archived data going back prior years) and upgdrading to SQL Server. I
have MSDE 2000 on my machine and am trying to decide if I should begin
preparations to migrate to SQL Server.
My question is, have I set up this form and the related subform optimally
and therefore to improve performance I should migrate to SQL Server, or are
there things I should do in an Access environment to limit the data on the
form for Terminal Services users?
Many thanks in advance.
Nate