Switching Access 2007 accdb backend to SQL server express / Best frontend options?

Question

After reading many posts on this site, I am considering replacing my accdb be (2007) with SQL Server or SQL Server Express? My fe is currently accdb (accde upon deployment). Is there any advantage to connecting to SQL using .adp as opposed to .accde? I currently link to tables in SQL using ocdb and it seams to work fine.

The only other question I have is, how much effort is it to create my fe forms using Visual Basic in Visual Studio Express? The idea of having a standalone exe makes it tempting. Am I looking at a big learning curve to go from vba to vb? I'm assuming the functionality of my code can be replicated in vb with some changes required.

Just seems like a good time to make this decision, while I'm still in the design stage.

Answer 1

I have worked with MS Access since version One and SQL Server since version 6.5. Access is indeed a relational database, and always has been. I've even seen Data Warehousing done in Access. My answer to RYK would be this: Access is an AWESOME front end for SQL Server for all the reasons that really matter. Access includes Forms, Queries, Reports, Macros and Code Design all in one package. Access has all of these features AND they are extensive. Form design is elegant. Report design is feature rich. Queries and Data Design does everything you want it to. VBA is fully functional. VB.NET/C# will work but .NET development takes longer than Access development. Access even lets you quickly test data scenarios and situations before the data is imported to SQL Server. If anyone knows of an application that has more features than Access all in one app please, please let me know what that is. Access even allows users to perform ad-hoc queries if they know how to. I should add that I am an ASP.NET VB.NET C# Developer and Crystal Reports Designer. I also design Oracle solutions. For over 10 years now if you Have MS Office and SQL Server you can do ANYTHING with data, and do it easier. (Excel Reports, Visio Data Modeling, Data Warehousing, Word Mail Merging, Full featured database apps, you name it) Visual Studio and .NET just add different flavors of icing to an already large and delicious cake.

Answer 2

It’s a big leap to go from a full access application to a .Net application with an SQL server backend. I have been going through this for 4 or 5 applications over the past year and what I did was to split the move into 2 parts.

First I migrated the data to SQL server but kept the access front end. I then look closely at the reasons why I would change the front end, for some of the applications it is just not worth the time to rewrite them when the only feature I would be gaining is an .exe extension and not a .mdb one!

Some however it was worth the time and so I have migrated them over but you have to look at what you are gaining, the Access front end / SQL server backend is a very powerful combination

Answer 3

May I suggest you download and give Visual Studio Lightswitch a go. Really fantastic for replacing/upgrading Access forms apps. You also would not need to worry about what connections to use etc. I find this tool really useful and friendly to replace/replicate access apps and spreadsheets the business was used to use.