Issues re developing/deploying desktop database?

I'm developing a single-user, desktop, small, consumer-oriented database application and have a few questions for the experts here; I'm not a professional programmer/IT person, so please excuse any dumb or unclear questions...

Currently I am using Access 2010 (I already have some experience with it) and currently intend to distribute the application as a split database (ie, frontend/backend) via the Access runtime.  That said, I've read quite a bit about potential problems with both the Access runtime and with using ActiveX controls (Treeview, etc) in Access applications.  Therefore, I've considered moving to VB.NET instead of Access, but to do so would require a huge time commitment, so I'm trying to fully understand all the basics before doing so...  

Unfortunately, despite spending quite a bit of time googling and buying some books on the topic, I don't understand several very basic issues:

1)  I've read about lots of problems with the Access 2010 Runtime:
     a)  presumably these problems have not, and will not be fixed?
     b)  I've read that you can't use ActiveX controls in a runtime distribution--correct?

2)  To what extent would using a third-party installer such as Sagekey for the Access runtime resolve the various runtime and ActiveX problems?  The Sagekey solution seems to get very good reviews, although I haven't found that much about it.  It's pricey, but if it will save me untold hours and brain-damage, to me it would be worth it.

3)  If I decide to move to .NET--to start with the very basics--I understand that to develop a database-centric application with .NET, the application needs to incorporate a database program (such as Acces, SQL Server, MySQL, etc.)--correct?  
     a)  Or is it possible/desirable to develop the various tables, etc. directly in .NET somehow?
     b)  I've read that including the Access runtime within a .NET application still leads to all of the runtime problems mentioned above-correct?

4)  If I decide to move to .NET, I'd prefer to use it with an Access database, since Access is more than capable of handling all of my current needs and I am fairly familiar with it.  Is there any real reason to use SQL Server Express instead of Access?  

5)  If I decide to use .NET, I understand that basically I would use .NET for the forms and for the connections with the database but don't understand what should be in Access vs .NET; for instance, which parts of my existing Access 2010 accdb database could still be used in the VB.NET application?  
     -tables?
     -queries?
     -modules (including SQL code)?
     -forms (presumably not)?
 
6)  If I decide to use .NET, my default would be to use VB.NET, because it sounds simpler and closer to VBA, which I understand a bit.  Is there any real reason to use C# (or something else) instead?
tmreiterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Lots of questions, but here is my take on the matter:

1. The ART has no more troubles than full Access - it's just a different environment, and you have to be aware of that environment. For example, you cannot open a Form or Report in Design view, and many people have code that does that - so your application would fail when that occurred.

Deploying the ART alongside full versions of Access, however, can cause issues. There is a company that can help with that (SageKey), and if you move forward with an all-Access development it would be crucial to use SageKey for your distribution.

Virtually all ActiveX controls are not compliant with the Access environment, except for the bare handful you might find from companies like FMS. Many people continue to use them, of course, and you'll get a lot of different answers on this one. In my opinion, if you're creating a database where you have control over the environment - that is, an application for yourself, or for the company you work for - then you're usually ok with it. If you're creating an application that will be deployed "in the wild", where you have no control over the environment, then you're just asking for troubles if you include ActiveX controls. MSFT is under no obligation to maintain compatibility with Access and those ActiveX controls, and as the operating systems move further away from 32 bits, you'll find yourself having a much more difficult time insuring reliability to your customers.

Of course if you use controls from companies who have guaranteed compliance with Access, like FMS, then you're probably okay. My only concern with that would be - what happens when those companies no longer decide it's worth the effort to maintain those controls, or when they find that they can no longer maintain compliance with new operating systems, or with Office? Not saying that will happen, but that would be a big concern for me.

2. As stated above, SageKey is critical if you're deploying an Access application in the wild. It will not, however, resolve any ActiveX issues. Those are issues involving the Access environment, as well as the specific OS on which your application is installed. SageKey can't help with those.

3. First note that Access is not a database engine. It's a development platform that includes a fileserver database engine know as either JET (2003 and earlier) or ACE (2007 and later).

.NET does not have a built-in database engine like Access, so you'd have to pick one and go from there. .NET is very tightly coupled with SQL Server, so that's often a good choice, but you could use any of the ODBC-compliant database engines. Note that deploying server-based database engines is a bit more tricky, since you must install and setup those engines, whereas with Access you just copy over the file (assuming the target machine has the necessary support files to work with the JET/ACE files). Once the files are copied, you're pretty much done.

You don't need to include the ART with your .NET deployment, and it would be pointless to do so. Instead, you deploy your database (the tables only) and then your .NET executable would connect to those tables.

4. SQL Server is a much more secure platform, and many would argue that it's more stable and reliable than ACE. IMO that depends more on how you're using the database and less on the particular database engine. If you're developing a single-user application, where the database will reside on a local computer and not be shared with others, then Access is a great choice. If your application will connect to a centrally hosted database, then you may find that SQL Server is a better fit (but not always). One thing to consider is that .NET is very tightly coupled with SQL Server, which generally means it's easier to code against a SQL Server database than others (including ACE). The downside to that is SQL Server takes a bit more to deploy and install, which is just one more thing you'd have to learn.

5. With a .NET program, the ONLY thing stored in your ACE database would be your tables. You'd do everything else in .NET, including Forms, Reports, etc.

6. VB.NET is more than capable of handling standard line-of-business applications. I can't tell you how many VB.NET utilities I've created and deployed over the past few years. That said, C# is certainly worth considering, as it's used much more often, therefore you'd find a lot more forum-based support for your questions and such.

Also, be aware that .NET is much different than is Access in regards to programming. Visual Studio is a much more complicated development environment, and can be very daunting at first. If you decide to move to .NET, you'll find that you have a definite learning curve. If you're very strong in VBA and SQL, and do much of your work using recordsets, loops, etc then you'll find your transition a bit simpler. If you're a drag-n-dropper, and you have a penchant for macros, you've got a lot of work ahead of you. The .NET languages are much more complex than VBA, and that takes time to learn.

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
1)  I've read about lots of problems with the Access 2010 Runtime:
     a)  presumably these problems have not, and will not be fixed?
     b)  I've read that you can't use ActiveX controls in a runtime distribution--correct?

 Not sure what your referring to in terms of problems, but anything in 2010 (or 2013) would not be fixed.  Those are older versions, and on top of that, Microsoft is really doing nothing with the desktop side of Access any more.

  If it's an out right bug, they would  fix it, but only in 2016 probably, which is just around the corner.

  The other issue is 32 vs 64 bit; the Access "runtime" is nothing more than the full version of Access with the design features disabled (you can use the full version with the /runtime switch or rename your DB to .accdr to run it in runtime mode for testing).

  Given that and that you cannot mix 32 and 64 bit apps within office (64 bit Excel with 32 bit Access), you need an isolated install of your app or you need to distribute two versions.    SageKey is the only one with an isolate install, and I don't think even they've gotten around the 64 bit problem.

  As for Active-X, yes you can use them assuming you have the license to distribute them.  It's also up to you to use an installer that places them on the target machine, takes care of registering, etc.

 In that regard, with Access you are far better off to do everything with native controls.


2)  To what extent would using a third-party installer such as Sagekey for the Access runtime resolve the various runtime and ActiveX problems?  The Sagekey solution seems to get very good reviews, although I haven't found that much about it.  It's pricey, but if it will save me untold hours and brain-damage, to me it would be worth it.

It's well worth it and basically a must for a commercial product.   Inno installer is available as well, but you do all the work yourself and that can really add up time wise.  Plus it doesn't give you that isolated install.


3)  If I decide to move to .NET--to start with the very basics--I understand that to develop a database-centric application with .NET, the application needs to incorporate a database program (such as Acces, SQL Server, MySQL, etc.)--correct?  
     a)  Or is it possible/desirable to develop the various tables, etc. directly in .NET somehow?
     b)  I've read that including the Access runtime within a .NET application still leads to all of the runtime problems mentioned above-correct?

  .Net is totally different than Access.  You could use a JET database to store the data, but that's it.  You would not have the runtime problems because you are not using Access at all in this case.


4)  If I decide to move to .NET, I'd prefer to use it with an Access database, since Access is more than capable of handling all of my current needs and I am fairly familiar with it.  Is there any real reason to use SQL Server Express instead of Access?  

  Without knowing more about the app and who might use it, the answer would be yes.  On-line backups, roll back/forward capability, and scalability are all things JET does not have.  Plus there is the stability factor and the ability to run over a WAN where as JET cannot.


5)  If I decide to use .NET, I understand that basically I would use .NET for the forms and for the connections with the database but don't understand what should be in Access vs .NET; for instance, which parts of my existing Access 2010 accdb database could still be used in the VB.NET application?

 Only the tables in the JET database would be used.  Everything else would be done in .Net.   None of your existing work could be used.


6)  If I decide to use .NET, my default would be to use VB.NET, because it sounds simpler and closer to VBA, which I understand a bit.  Is there any real reason to use C# (or something else) instead?

 Using vb.Net would be a good choice to leverage your VBA skills and it's doubtful that you'd need anything in C# to make your app work.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tmreiterAuthor Commented:
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.
0
tmreiterAuthor Commented:
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)?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

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.
0
tmreiterAuthor Commented:
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?
0
PatHartmanCommented:
I don't understand your obsession with ActiveX controls.  Which controls can you not live without?

There is at least one or possibly two, "all VBA" replacements for the TreeView control so you could use that without distribution issues.

Most Access developers don't use third-party ActiveX controls at all because of the issues already mentioned as well as the complexities involved in distribution.  SageKey does resolve that though.

Relationships are CRITICAL for the health of your database.  Referential integrity is always defined in the database that holds the data.  How else could Access databases be shared?

If you are creating apps that will be distributed in the wild, then you must use SageKey.  Nothing else even comes close and SageKey still won't solve all your problems.  If you are creating internal apps, then you have some control over your environment.

If you are not a professional programmer, then the .net environment is probably more than you should bite off.

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.  In application development, the database you use is almost irrelevant.  Every app I write that starts out with a Jet/ACE BE can be converted to SQL Server in a matter of hours because I write them that way.  I always consider client/server techniques when developing apps.  Many of my apps that started out as Jet/ACE ended up as Oracle, DB2, Sybase, etc.  Whatever my client uses as his primary BE database is what I use with Access.  It is "Access" that I love, not Jet/ACE.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
tmreiterAuthor Commented:
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?
0
tmreiterAuthor Commented:
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.
0
tmreiterAuthor Commented:
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.
0
PatHartmanCommented:
@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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
@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).
0
tmreiterAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.