Bound vs. UnBound Forms

[CPGDeveloper]

So I created and administer an MS Access DB -- approximately 20 end users -- they each have an .accde FE on their desktops, while the back end sits on a network drive.

The primary data entry form has approximately 70+ fields, with currently 50k plus records. There is a subform on this form as well, showing multiple 'status' for each 'id' in the primary form. End users add new records, and search through the existing records, as well as occasionally making edits to the existing records.

Currently the form is bound to a query, based on a table sitting on the back end. I maintain a persistent connection when the application opens, and currently performance is fine. I'm wondering, however, as this application grows, and in a few months when we have 100k records, how it will start to behave.

Should I consider creating an unbound form instead? I'm not sure how that would work in this scenario -- any ideas or direction would be greatly appreciated. Thanks.

 

[Micron]

Unless you have a pressing reason to use unbound forms for data, I would not. I see no performance gain; in fact, given that everything would have to be handled by code you'll likely introduce issues that you don't currently have. Validation would be one of them. Then there is the fact that Access optimizes queries, often resulting in better performance than what you would experience if it did not. AFAIK, vba written sql is not optimized. For 20 users of a split Access db (where each user has their own FE) and especially since you mention that subforms are involved that just makes unbound relationships more of a problem.

 

[CPGDeveloper]

Thanks For that Perspective -- Unbound Forms seems like far more trouble than it's worth.