Issues re developing/deploying desktop database?

All in all, if I were creating an application for commercial release, I would not consider Access as a platform. If I were creating an application for corporate release (i.e. in my own corporate environment), then Access would be a very viable choice in most cases.

 +1 on what Scott has said here.

Jim.

Scott, thanks very much for your thorough answer (and patience!).

It sounds like I have two general alternatives:

1) continue with Access using Sagekey and Access-compliant ActiveX (or non-ActiveX) controls; potentially easier, but $$$; or

2)  move on to some flavor of .NET...  I think I can deal with the migration to .NET, I've always written VBA code rather than relied on queries, macros, and wizards, but getting used to the syntax would certainly take time.  The point about more forum support for C# was very helpful, because fora and kindle books are my main source of info (and I would not have guessed that C# was more popular).  

Two follow-up question about moving to .NET:
     a) I read on the internet (so I know its true!) that using .NET with JET (ie, and mdb Access file) is better than using ACE (an accdb Access file), because JET is already on all Windows computers, while users would have to install ACE themselves if they don't have it already; IIRC the internet posting was several years old, so I was wondering if that is still true?  I would guess that all Windows computers sold in the last several years would include ACE?  If that's correct, do all computers still ship with JET as well?

     b)  Just to clarify, if I move to .NET, I would use ONLY the tables from the mdb or accdb file--I wouldn't need the relationships as well?  I'm guessing that I'll mainly rely on SQL statements in .NET as well, so am not sure if .NET would need the Access relationships?

Thanks very much again--you've provided more clarity than many hours of searching around in the wild.

Jim, thanks for your helpful comments as well.  Three follow-questions:

1)  You mainly referred to JET, while Scott mainly mentioned ACE--why do you suggest JET instead of ACE?

2)  If I use SQL Server Express instead of JET/ACE, would I only create the tables in SQL Server (as with JET/ACE), and then do everything else (create forms, queries, etc.) with .NET?   Or would I create views (and anything else?) in SQL Server?

3)  My inclination is to only take on one steep learning curve at a time, so to use .NET with JET/ACE, and then potentially upgrade to SQL Server when/if necessary.  While I'm sure that wouldn't be easy, is there any compelling reason to switch to SQL Server now (I saw your advantages descirbed above, but here I am referring to process issues (ie if I couldn't export JET/ACE table data to SQL Server, etc.) rather than those substantive advantages)?

1)  You mainly referred to JET, while Scott mainly mentioned ACE--why do you suggest JET instead of ACE?

  Old habits die hard<g>.   ACE is nothing more really than JET renamed.   The Office team renamed it when they took it over starting with A2007.   Is is fundamentally JET except for a couple of data types (Multi-value fields and the attachment data type).

2)  If I use SQL Server Express instead of JET/ACE, would I only create the tables in SQL Server (as with JET/ACE), and then do everything else (create forms, queries, etc.) with .NET?   Or would I create views (and anything else?) in SQL Server?

 You can, but you'll end up with a better performing app if you do as much as possible server side.  That would be true even if you were using Access for the forms and reports, and SQL Server for the data.

Views are easy, then there are Stored Procedures (T-SQL Code), and Triggers.

3)  My inclination is to only take on one steep learning curve at a time, so to use .NET with JET/ACE, and then potentially upgrade to SQL Server when/if necessary.

 Scott can probably add to this, but I would just go with SQL server right off.   It's not that much of a switch if you keep things simple.  Use views right off the bat and then if you have any long running processes after your done with the app, look at doing a stored procedure here or there to make it better.

 And the other main advantage that Scott mentioned which I did not is security.   If your doing any kind of an app that will process CC's or is related to Health Care or some such, you really want SQL and the security it offers.

Jim.

Got it, I guess you're right about SQL Server.  Don't you love it when people listen to you?

One last question:  I've got a fair bit done in Access now, and I see that there's an upsizing wizard to convert Access DBs to SQL Server--using this wizard is there any way to salvage anything other than the data tables (presumably I can keep those!) when moving to SQL Server?  The forms I guess are a scratch, and I guess the code modules are goners as well?

But if I can't keep anything, what am I really "upsizing"?  Seems like I'm just "exporting" tables?

<<But if I can't keep anything, what am I really "upsizing"?  Seems like I'm just "exporting" tables? >>

 That's it...

And use the SQL Server Migration Assistant:

http://www.microsoft.com/en-us/download/details.aspx?id=43690

Don't use the built-in stuff.  This does a much better job.

Jim.

move on to some flavor of .NET...  I think I can deal with the migration to .NET, I've always written VBA code rather than relied on queries, macros, and wizards, but getting used to the syntax would certainly take time.

 Your VBA skills will be of some use, as you'll be more comfortable writing code instead of dragging-dropping - but as I mentioned, .NET is an entirely different world than VBA, and many find the transition to be very, very difficult. I'd consider myself to be a very advanced Access programmer, and I was very well versed in the use of Class objects, unbound programming, data handling, and the concepts of Object Oriented Programming - and I was lost for several months while I kludged my way around .NET :) There are so many differences in the language that I spent a great deal of time trying to get my head around how to do basic things (like fetch data from a database, or use datasets/datables, etc). I'm not trying to dissuade you from using .NET, but rather to let you know that the whole concept is much, much different from VBA programming.

But if I can't keep anything, what am I really "upsizing"?  Seems like I'm just "exporting" tables?

You're essentially correct - upsizing to SQL will move only Tables and (optionally) Queries. Your Forms, Reports and Code Modules would not be moved up. As we've alluded to several times, Access is really more of a development platform than it is a database engine, and you really cannot compare it to SQL Server, or .NET. The equivalent of SQL Server in Access would be JET/ACE. The equivalent of .NET in Access would be the Forms/Report designers. If you combine the two, you get close to what Access provides, with some notable exceptions (subforms being one of the things I miss the most). I found that using .NET with a control suite (like DevExpress) gave me the closest experience to Access - but then you go straight back to the dependency issues. Granted DevExpress is a solid company that's been around for quite some time, and shows no signs of discontinuing support, but you'd still be at their mercy if you decide to use their control suite.

My inclination is to only take on one steep learning curve at a time, so to use .NET with JET/ACE, and then potentially upgrade to SQL Server when/if necessary.  

In my opinion, if you move to .NET you'll find working with SQL Server to be easier. As I mentioned, the only real drawback is that you must deploy and install SQL Server, but there are scripts and such that can be used to do this seamlessly. .NET includes an entire namespace that deals with SQL Server. It also has namespaces for ODBC databases, but those are necessarily generic. With the SQL namespace, the .NET framework is very specific (since MSFT controls both).

Professional Access developers would give up Jet/ACE in favor of SQL Server or other RDBMS but if they give up Access in favor of .net, they are no longer Access developers and most would be reluctant to do that which is exactly the opposite of your plan.

I don't necessarily agree that those who develop in both Access and .NET are no longer "professional" Access developers. I work in both, and my workload is about half-and-half, and I would consider myself to be a professional Access developer. My point in the whole matter is this - Access is not the best platform to use if your intent is to create a program designed for commercial "in the wild" distribution. I do agree that the .NET environment is very daunting to non-professional programmers - but it's almost a necessarily evil if your goal is to create commercial software. As you're aware, there are not very many commercial software offerings created entirely in Access.

I don't understand your obsession with ActiveX controls.  Which controls can you not live without?

Hi Pat, thanks for your comments.  What would life be without our obsessions?  Actually, the only ActiveX  control I have any interest in is the Treeview; in a different thread Scott pointed out one of the "all VBA" treeview solutions, but it doesn't have drag-and-drop, which I would have liked.  

Thanks also for your thoughts about .NET; while I'm sure I could eventually figure things out, from the books I've looked at it looks like I'd spend lots of time wrestling with various technical issues (connection strings, etc), which I don't particular enjoy.  Meanwhile, I actually kind of enjoy dealing with VBA, which is relatively simple and poses a series of "logic puzzles".

I'm not sure, though, what you meant about my "plan"--I don't really have a plan to become an Access expert, but would like to be able to create database-related applications that can be distributed; I'm just trying to figure out the best way to do that.  

Anyway, as usual with complicated topics, some contradictory recommendations, all of them with some good reasons behind them.  I've got to think a bit about what makes the most sense.  Any other words of wisdom from the gallery?

many find the transition to be very, very difficult. I'd consider myself to be a very advanced Access programmer, and I was very well versed in the use of Class objects, unbound programming, data handling, and the concepts of Object Oriented Programming - and I was lost for several months while I kludged my way around .NET :) There are so many differences in the language that I spent a great deal of time trying to get my head around how to do basic things (like fetch data from a database, or use datasets/datables, etc).

That sounds rather intimidating...gotta think about how to proceed.

I'm going on a short road-trip and will decide on a plan by the time I'm back.  In the meantime, I'll close this out.  All of you have been very helpful, but since Scott takes the credit/blame for kicking this off (in a different thread), I'll give him the bulk of the points, but with some allocated to Jim and Pat.  Thanks again for your help on this.

@Scott,
I think my point was too subtle.  I was trying to separate the people who think they know "Access" from those who actually know Access.  The former have opened a database and entered data into a table or perhaps linked to "Access" (actually Jet/ACE) tables from a different app but they don't know the difference between Jet/ACE (which are separate products) and Access, the rapid application development tool.  The later are real Access users and real Access users can use any back end database.  They are not welded to Jet/ACE because they are creating applications.

@Pat: I see what you mean, and I agree 100%. Sorry for my misunderstanding.

That sounds rather intimidating...gotta think about how to proceed.

It's realistic, at least in my case. Without knowing your skill levels/skill sets, or your application requirements, it's impossible to know what sort of turn around time you'd have. Given the questions you've asked here on EE, I'd think your level would be somewhere around a Hobbyist Developer. IMO, that would put you fairly low on the learning curve, with a lot of uphill work to do.

If your application requirements are somewhat simple - just querying a database and returning data, printing a few reports - you might find you could get up to speed in a few weeks. That's really, really hard to quantify on a web forum. In my case, the project that prompted my move to .NET was much more complex than that, which meant I had to delve into the inner bowels of the .NET languages (a dark and scary place at first).

Yeah, I'm firmly a hobbyist developer, and not on the advanced end either.  

I've decided that I will try for the next month or so to proceed with C# and SQL Server Express; if I am still banging my head against the wall at that point I can hoist the white flag and revert to VBA and Access.  I like and kind of understand VBA but it seems like kind of a dead-end if I want to distribute applications (whether freeware or commercial).  If I'm going to spend a lot of time on something, I might as well learn something useful.

It sounds like the learning curve will be steeper than I'd expected, but that said, I have a couple of things going for me:
1)  I'm only working on one application at this point, and  I don't think it is especially complicated (famous last words...);
2)  I won't have a pesky boss or client breathing down my neck; I can take as long as I want and do it any way I want.  

I'll probably start this weekend, so please stand by for some basic C# database questions!