I’ve been working with a Microsoft Access frontend connected to a SQL Server backend and I’m having some issues with data insertion using bound forms. Someone recommended switching to stored procedures for handling inserts instead of relying on bound forms.
My main question is: what’s the standard way to implement this approach?
I’m wondering about a few things:
• Managing related tables: How do you handle inserting records into child tables when using unbound forms? Should I use separate controls on the main form or maybe popup windows?
• Code organization: Would it be better to use pass-through queries to keep everything in Access, or should I store the procedures directly in SQL Server?
• Converting existing forms: Is it worth converting a large number of existing bound forms (maybe 40-50 forms) to this new approach?
Has anyone dealt with similar challenges? What worked best for you?
yea, stored procs r gr8 but only if ur facing perf issues. if it ain’t broke, don’t fix it! also, try tackleing 1 or 2 forms that lag first b4 going all out. good luck!
I made this same switch from bound forms to stored procedures in Access-SQL Server about two years back. How much work it’ll be really depends on your performance issues and how complex your data is. For related tables, I had great luck with transaction-wrapped stored procedures that handle parent-child relationships in one call. No more wrestling with complex form coordination. For organizing your code, put the procedures directly in SQL Server instead of using pass-through queries. Your DBA can optimize performance way better, and version control is much cleaner. Don’t try converting all 40-50 forms at once - you’ll go crazy. Start with the heavy data entry forms or ones that are already slow. There’s definitely a learning curve, but the reliability boost and performance gains are worth it if these are mission-critical apps.
Interesting challenge! What performance issues are you hitting with the bound forms? Slow saves, timeouts, or something else? How’s your network between Access and SQL Server? Sometimes it’s not the forms - it’s the connection. Maybe try a hybrid approach?