Deploying VB application with SQL Server Express

[olmie75]

My company's users need to be able to work with a database while offline, and some of these users do not have MS Access (which we used in the past). I figure I could deploy SQL Server Express 2005 to all users since it is free and build a VB application to work with the local DB instance on the users' machine.

I have a lot experience using VBA in MS Access & database programming in general (Oracle, SQL Server, etc.), but I'm fairly new to standalone VB applications. The first thing I want to do is build a simple concept model to make sure my plan will work:

Create a installation package on a CD that will:
1) Install .NET 2.0 if necessary (I assume this is necessary if they will run SQL Server Express, right?)
2) Install SQL Server Express
3) Create the data structure in SQL Server necessary for my VB application
4) Install my VB application

Is this the best way to meet my users needs? Any other ideas?

Does anyone know of a good resource where I could get some general knowledge on deploying VB with SQL Server Express?

Can anyone provide specific information on how to execute the above tasks?

Thanks in advance.

[GaryMazzone]

There are multiple ways of doing what you want. Are you doing VB.Net? or VB6? There is an app called INNO Installer that will what you want. Also Application deployment in Visual Studio. You can write scripts that will perform the database build after you install SQL Server. You do realise that Access is not required to run a VB app with Access as the backend.

[olmie75]

I am using VB.Net. I did not realize that I can use Access backend (Jet?) if the user's machine does not have Access.

How does this work? (It seems like this would be a lot simpler than having to deal with SQL Server Express) - the database portion of my app is not all that complex (only 5-6 tables, only 3 data entry tables w/ a few hundred rows at most)

Does this mean I just create an Access database, embed it in my VB project, and when users install my app, it has the data & structure of the Access DB I created?

If the user's machine does not have Access, where is the data / structure stored?

Thanks in advance

[GaryMazzone]

You design the database on a machine with Access installed. When you develop the app you attach all the data access components in the application and the user uses the program only to access the database. You can not access anything sepecial in Access, no Querys or Reports based in Access.

[olmie75]

users need to be able to work with a database while offline, and some of these users do not have MS Access

To clarify, the users may not have Access and they will also be offline. So they will need to store data in tables located on their machine. Will what you describe work in this scenario?

[GaryMazzone]

Not a problem. The actuall MDB file will reside on their machine. The just can't open it outside the application

[olmie75]

Thanks for the help ... So now I'm trying to do a quick proof-of-concpept. I went through some online tutorials to get me familiar with interacting with the database through the forms (Access Front-Ends sure make it easy). Currently, the .mdb is in a specific location on my PC, so the connection string is: con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\MyDatabase.mdb"
How would I define the connection string for the Access DB that I will embed in my VB App?

[GaryMazzone]

That can be saved in the MySetting area. when the user first runs the application you provide a way for them to select the db location if it is not in the same place as the app.