How do I use a SQL Server Express database (.mdf) file like I would an Access (.mdb) file in my VB.NET project?

Posted on 2009-12-21
Medium Priority
Last Modified: 2013-11-26
In the past I have used Access .mdb files with my VB6 applications. I include the .mdb file in my setup package and my code looks for the database file in the user's program data directory.

Now I am working in VB.NET with Visual Studio 2008, and I would keep using Access database files except that Microsoft is not making a 64-bit version of the JET engine. They are still supporting it in 32-but mode, but I suppose that that will not last forever. I would like to use a more current database engine that will continue to be supported for years to come.

It seems that SQL Server Compact is only available in 32-bit, as well, so I am going with SQL Server Express. What I don't understand is how to include the .mdf file in my application and how to connect to it.

If I use the "Add New Data Source" functionality in Visual Studio, it creates connection strings for me that include the server name that I don't think will exist on my client's machines. This is exam software that I will sell to students who are preparing to take an exam, such as an SAT exam. They students have no database software on their systems, so no SQL Server. I understand that I can use SQL Server Express 2005 in my project somehow. I just don't get how to do it.

If I had to "do it all myself", maybe I would include the .mdf file as a "content file", and let the setup program copy the file somewhere, like the user's program data directory. Then, I guess I would have to attach to the database file. But I don't understand how I fire up SQL Server and attach the database so I can use it.

I would like to let Visual Studio handle as much of that as possible, but I find that if I use the built-in functionality, I have to specify the server and stuff that is specific to my system.

So, what do I do?

I need to be able to install this on Windows XP, Windows Vista, and Windows 7, all in 32-bit and 64-bit. I understand I may have to build multiple packages depending on the user's operating system, but I am hoping to have as few installation sets as possible.

Eventually I will use InstallShield, which I have purchased, but at this point I still don't understand how to integrate the database.

Please help.

Thank you.
Question by:jkurant
  • 3
LVL 10

Expert Comment

ID: 26102558
LVL 11

Expert Comment

ID: 26103245
you can attach your mdf file in your sql server and use it as database in your application.

Author Comment

ID: 26109492
Well? How do I attach my database? What do I do about the connection strings? Isnt this a common question?

Author Comment

ID: 26110023
nevermind. i figured it out myself.

Accepted Solution

jkurant earned 0 total points
ID: 26110030
I have included the .mdf file in my project and then point to it with a connection string like this:

Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename="<databasefilepath>\<databasefilename>.mdf";

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question