From a high level overview....
I recommend you install SQL Express as a named instance. This will benefit you in the long run, trust me. By default, SQL Express already installs as a named instance ([!]SQLEXPRESS[/!]). Instead of that name, I recommend you choose something that makes sense, like your company name or your product name.
If you choose to use
AttachDbFilename, you may run in to problems. I've never used that functionality, but if I recall correctly, using that in your connection string will cause the database to attach every time you use it, which is likely to add a lot of overhead to your app (think slow performance). There are certain (rare) instances where it is appropriate to do this, but it's not recommended for long-term use. Another problem with this method is that a database can only be attached to a single instance of SQL Server. If you attempt to open multiple connections simultaneously, your second connection will fail.
So... here's what I recommend.
1. Include (as part of your installation) a silent install of SQL Server express. Your installation app should allow you to pick Client (your vb.net app), Server (SQL Server Express and your database), or Both (for installing locally on a desktop).
2. When you first run your app, you should check your configuration file for Server and Database. If those configuration entries are blank, then you should present the user with a configuration window where they can specify the server and database. Once the user has successfully connected to your database, you should store the configuration information so that it is available the next time the user runs your app.
3. You should build in to your app functionality to change the configuration. It happens to me all the time. I have a commercial app that is sold to large organizations (think 1000's of PC's, with approximately 5 of them connecting to my db with my app). Every couple months I get a phone call from the IT director informing me that they are switching to another server, and they want to know what they need to do to get the database moved. Understand that it's not the same organization every couple months, but based on my customer counts and their requirements, it happens frequently enough to become a problem. Anyway... make it easy for them to move the database to another computer. You'll be glad you did. With my app.... I tell them to pop in the installation CD and install the server component. Then I tell them to detach the database from the old computer, copy it to the new one, and reattach it. Then, to re-configure the app, they simply click a button on my login dialog and run through a wizard.
The mistake I made (years ago) was to NOT install SQL Server as a named instance. You see, on these large networks, it's not uncommon to have dozens of SQL instances running. With a named instance, it's a lot easier to identify which instance belongs to your app. Additionally, with a named instance, IT directors are more willing to allow you enough privileges to do what you need to do. Ya know. Every IT director that knows anything about SQL Server will want to lock down your privileges (no xp_cmdshell, or sp_oa functions). However, with a named instance, they will probably allow you to have SA privileges which allows you to create new database, and generally just administer your database (like making backups).
Now, while we are at it....
I recommend you install your named instance with mixed mode authentication. When you log in to a database, there are 2 methods for authenticating your login. You can use windows authentication or SQL Server authentication. To use windows authentication you need to be logged in to a domain and the group that your (windows) login is associated with must be configured on the SQL instance. This requires a bit of configuration that you may not want to get in to for a simple app. However, if you install a named instance that supports mixed mode authentication (Windows and SQL authentication), AND you have a hard coded system administrator account built in, then you can programmatically add logins and user to the database.
As you can see... this is a bit complicated, and is likely going to require some fiddling to get it right. I encourage you to download
Microsoft Virtual PC. (It's free) This will allow you to test your install program multiple times without needing another computer that you are constantly re-formatting.
I wish you luck.
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom