The question is whether to design the front end in visual studio (VB6) or Access VBA. Are there any pros/cons over either choice?
Using Access -
Pros:
1. Rapid development due to Access doing a lot of the behind the scenes work for you.
2. Access does a lot of the "behind-the-scenes" work for you.
3. This goes with number one and two, but is further explanation. With Access you have the benefit of the forms and reports objects which are very quick to build and easy to run.
Cons:
1. Your users must either have Access or you have to provide them with the runtime components.
2. In order to keep your users from getting to design things you need to use several steps, depending on how much you want to lock down, to secure the program.
VB -
Pros:
1. You have the ability to utilize ActiveX controls with greater assurance that your users can use them and not suffer from reference errors since you create an install package that will install the controls for the users.
2. Unless you have a developers edition of Access (or can use the current Access 2007 tools) for a runtime install, and you have a full VB program to use, you can create an install package for your program in VB.
Cons:
1. You have to have the VB program to create the programs.
2. You have to basically do everything for creating forms and reports whereas Access does it for you.
3. Your users may not be allowed to install custom programs.
Those are just a few of the pros and cons. I'm sure I'm missing many of them on both sides.
I imagine if in Access, the code will still have to run a stored procedure on the server and return results...
This is not necessarily true. You can get better performance if you can return values based on stored procedures, but using Access you can create queries in the local front end as well.
