I’ve been having trouble with data insertion from my Access frontend to SQL Server database. The bound forms approach isn’t working well for me and I keep getting weird results after inserts. Several people told me I should switch to using stored procedures instead.
Is this really the standard way to handle inserts in this setup? Do most developers agree that stored procedures work better than bound forms when connecting Access to SQL Server?
I have some questions about making this change:
-
Related tables: How do you handle inserting records into child tables when using unbound forms? Do you create separate popup windows or put everything on the main form? This seems like it would confuse my users.
-
Code management: Right now I keep all my Access code in version control. Should I use pass-through queries to keep everything together, or is there a real advantage to putting the procedures directly in SQL Server?
-
Converting existing forms: I have about 50+ forms that would need to be changed. Is it realistic to convert all of them or should I just fix the problem ones?
Yes, stored procedures are the way to go for Access-SQL Server operations. I migrated a similar setup three years ago and saw huge performance gains. Bound forms create overhead and lock issues that stored procedures fix. For related tables, I pass the parent ID as a parameter to child insert procedures. Keeps referential integrity clean without messy form designs. Users actually liked it better since validation happens server-side. For your 50+ forms, don’t try to convert everything at once. Hit the most problematic ones first based on usage frequency. I kept critical procedures in SQL Server for speed but used pass-through queries for less frequent stuff to keep some code centralized. The upfront work pays off big time in maintainability and kills those sync headaches you’re getting with bound forms.
don’t try to do everything at once - that’s the biggest lesson i learned the hard way. start with 3-4 forms max and get those rock solid before adding more. for child tables, i disable the parent form until all the related inserts finish. keeps your data consistent and users won’t get confused. and definitely put your stored procedures in source control, not just the access stuff.
hold up - are timing issues causing this? like records not appearing right after you insert them? i’ve dealt with this tons of times on bound forms and it’s maddening. what exactly do you mean by “weird results”? that’ll help us figure out if stored procedures are actually the fix you need or if something else is broken.