none
Using Database in Application Sold to Others RRS feed

  • Question

  • I'm planning to develop a shareware application that will use a database file. Currently I'm thinking a Microsoft Access file, since that would be a stand-alone file that I could (assuming there is no licensing restriction that I'm not aware of) send along with the application. Some sort of SQL Server stand-alone file would be better, but it looks to me like the Compact version is only for mobile applications. So, I'm thinking Access.

    To do that I've been experimenting with the OleDb data provider. But I've found that if I move a test app from one machine to another that it generally complains that that particular OleDb data provider isn't registered on the new machine.

    So... I need some advice as to the best way to build this app:

    1. Is Access the way to go?
    2. Any licensing problems with distributing an Access MDB file and/or a data provider to go with it in a .Net app?
    3. How do I include the OleDB provider in the installation program (I'm using Astrum InstallWizard) so that it will be available when they run the app whether they had it or not previously?

    and

    4. Am I going about this the hard way? If so, what's the easy way?

    Thanks!

    Tom Jorgenson
    Wednesday, July 22, 2009 7:36 PM

Answers

  • This is what I know with respect to Access/Jet OLE DB configurations (and some of it I'm repeating):

    Jet OLEDB is pre-installed on all Windows operating systems starting with Windows XP SP2 and Windows 2000 SP4. It's documented in the below article. If you need to support Windows XP (pre-SP2), Windows 2000 (pre-SP4), Windows ME, 98 or prior versions then Jet *may* need to be installed. Note that I said *may* because it still already may have been installed in order to support other applications. I think you will need to decide whether to support operating systems no longer supported by Microsoft. The below link should provide more detailed info concerning Jet installs:

    How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine

    MDAC and the Jet OLEDB components are separate installs. It's been that way for quite some time now and MDAC is not required to use Jet OLEDB or work with Access databases.

    There is no 64-bit version of Jet and never will be. The current OLEDB provider for Microsoft Access is called ACE. It supports Access 2007 databases. Jet does not. You can download the install for ACE from the below link and you may want to consider using this provider going forward. I can't say whether it functions properly on older unsupported versions of Windows.

    2007 Office System Driver: Data Connectivity Components

    When using Jet (or ACE) you will need to compile your application using the x86 option (32-bit). This will cause the app to run in the 64-bit WOW (32-bit Windows on Windows) for 64-bit Windows systems.

    You will not be able to install Jet on a system where the latest version is already installed, which will be most (but not all) systems still in use.

    Connection string examples can be found below:

    http://www.connectionstrings.com/access
    http://www.connectionstrings.com/access-2007

    Also remember that you may need to install the .NET Framework or at least inform the user that they must go to Windows Update and download the latest version.

    Hopefully this will address all of your questions (if it doesn't make things more confusing). :-)
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 24, 2009 3:04 PM

All replies

  • Sql Express is free and you can use the Sql Providers with .Net.
    Wednesday, July 22, 2009 8:12 PM
  • By your response I assume that SQL Express uses a stand-alone database file that doesn't require any version of SQL Server to be installed on the end-user machine, correct? ...And that there aren't any licensing restrictions about distributing this file to end-users (or any needed data providers)?

    The other part of the question is what will the end-user require in terms of data providers and how do I install them when the application is installed? It won't help me to switch to SQL Express if their machine still doesn't have the needed data provider available.

    Thanks!

    Tom Jorgenson
    Wednesday, July 22, 2009 8:21 PM
  • The "server" part is free to install. You can hook it to your install or notify your users to download it.

    Wednesday, July 22, 2009 8:33 PM
  • In a case of Access database you need to install Jet OLEDB provider or ACE OLEDB provider since they are no part of operating system. I believe this is the only part that requires additional installation in some cases. Installation for Jet OLEDB is available as JET OLEDB Service Pack installation, and ACE I think is a part of Office SDK (but not sure 100%). You cannot just install single OLEDB file and it is better to use installation from Microsoft to ensure that all the components properly installed on target PC.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, July 23, 2009 10:15 AM
    Moderator
  • You will probably encounter some problems under 64-bit versions of Windows unless you compile your app with the x86 option (32-bit). If you don't do this the app will run as a 64-bit process and fail (when using an Access database) since there currently is no 64-bit provider available for Access. Otherwise, all current systems will have the Jet OLEDB Provider installed. If you go with the newer ACE OLEDB Provider (Access 2007) then this will need to be deployed with your app.

    SQL CE was originally designed for the mobile environment but you can also use it with .NET desktop applications. There is a native .NET library available (System.Data.SqlServerCe). You will need to deploy the database engine but it's a file based system unlike SQL Server/SQL Server Express.

    How to: Deploy a SQL Server Compact Edition Database with an Application 


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, July 23, 2009 1:56 PM
  • So.. If I understand what you're saying, as long as I compile the app as an x86 app and it's run on a recent version of Windows (Windows 98? XP? Vista? Windows7?) the data provider should already be installed on the system for Access (the OleDb v4.0 provider, right?)

    Just want to be sure I have this correct before I get too far along. And thank you for your help!

    Tom Jorgenson
    Thursday, July 23, 2009 8:39 PM
  • You shouldn't have to distribute the Jet OLEDB Provider for any system running Windows XP SP2 or higher, as long as you compile for 32-bit (x86).
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, July 23, 2009 9:12 PM
  • It does not matter if it is 32 or 64-bit environment, I believe you will have issues with the Jet, because Jet is not part of operating system or MDAC and is not installed by-default and in many cases, if client does not have it, you will need to do an additional Jet installation.
    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 24, 2009 10:10 AM
    Moderator
  • I really appreciate the help that everyone has offered, but so far I don't think I've really gotten a clear answer. It might be because I haven't been specific enough - so let me restate the question this way:

    If I want to distribute a a shareware application to a variety of users using a variety of Windows versions and that application uses data stored in a Microsoft Access .mdb table, what do I have to do in order to allow it to run on most versions of Windows?

    Can someone tell me step-by-step what I need to do to satisfy this need?

    1. The connection string to use?
    2. What to install on the user's machine if anything?
    3. x86 or x86/x64?

    Obviously a lot of developers are already doing this, so it must be solvable. I realize that it may not be practical to support every version of Windows out there - but I'd like to support as many as is practical. In particular, at least Windows XP, Vista, and Windows 7 - preferably Windows 98 as well.

    The installer I use, Astrum InstallWizard, has a feature that can install MDAC components (either 2.7 or 2.8), if that helps.

    I appreciate the numerous comments on this topic already - but I really need a clear answer from someone who's "been there" and done it themself and who can tell me how they personally solved this issue in a very clear way.

    Thank you all!
    Tom Jorgenson
    Friday, July 24, 2009 1:00 PM
  • This is what I know with respect to Access/Jet OLE DB configurations (and some of it I'm repeating):

    Jet OLEDB is pre-installed on all Windows operating systems starting with Windows XP SP2 and Windows 2000 SP4. It's documented in the below article. If you need to support Windows XP (pre-SP2), Windows 2000 (pre-SP4), Windows ME, 98 or prior versions then Jet *may* need to be installed. Note that I said *may* because it still already may have been installed in order to support other applications. I think you will need to decide whether to support operating systems no longer supported by Microsoft. The below link should provide more detailed info concerning Jet installs:

    How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine

    MDAC and the Jet OLEDB components are separate installs. It's been that way for quite some time now and MDAC is not required to use Jet OLEDB or work with Access databases.

    There is no 64-bit version of Jet and never will be. The current OLEDB provider for Microsoft Access is called ACE. It supports Access 2007 databases. Jet does not. You can download the install for ACE from the below link and you may want to consider using this provider going forward. I can't say whether it functions properly on older unsupported versions of Windows.

    2007 Office System Driver: Data Connectivity Components

    When using Jet (or ACE) you will need to compile your application using the x86 option (32-bit). This will cause the app to run in the 64-bit WOW (32-bit Windows on Windows) for 64-bit Windows systems.

    You will not be able to install Jet on a system where the latest version is already installed, which will be most (but not all) systems still in use.

    Connection string examples can be found below:

    http://www.connectionstrings.com/access
    http://www.connectionstrings.com/access-2007

    Also remember that you may need to install the .NET Framework or at least inform the user that they must go to Windows Update and download the latest version.

    Hopefully this will address all of your questions (if it doesn't make things more confusing). :-)
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 24, 2009 3:04 PM
  • Thank you. That was an excellent response that tied it all together for me. ...And thank you to the others that responded as well. It all makes sense now.
    Tom Jorgenson
    Friday, August 7, 2009 12:44 PM