Hi all,
this might be a broad topic, but I feel like I need some insights.
I want to create a small company database using MS Access as a front end and SQL Server Express as a back end. I know my way around Access and VBA quite well, and I just read this huge book about SQL Server (https://www.ebay.com/itm/284288727407), so I think I have a good foundation for my project.
I was also able to connect Access to the server using an ODBC connection, I can run pass-through queries, link tables and views and so on.
What I'm looking for in this topic is this: WHICH SQL SERVER OBJECTS ARE BEST FOR WHAT TASKS?
The application will have all the common functionalities:
- Continuous forms (order list, customer list, invoice list, employee list...), including calculated valus like order totals... Read only
- Single forms with Subforms (order details, customer details...), including calculated values... Read only
- Data entry forms - basically all data will be inserted and manipulated via these forms (for example when I'm on a read only order details form, I press the EDIT ORDER button and the data entry form appears, or when I am on the order list form and I press the NEW ORDER button)
And there are so many ways to access this data with SQL Server... There are:
- Linked tables/views
- Pass-through queries
- Stored Procedures / Functions
- I also wanna use triggers and so on
So for read only forms, do I use linked views? Or a pass-through query that runs the T-SQL statement I want?
For data entry forms, do I use linked tables? Will I be able to work with subforms like that?
What about concurrency? Do I run transactions when I press SAVE ORDER / CANCEL ORDER buttons? Or do I let Access handle this?
Is it a good practice to create stored procedures for all tasks and then execute them via a pass-through query? (because of the lack of formatting of pass-through queries)
Should I not use Access queries at all?
Like I said, I know this is not an easy question, and there's not a single correct answer, but I am just looking for some kind of a concept as of I should approach things. And your personal experience maybe.
Thanks a lot for explanations :-)
Best regards,
Tomas