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
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
    LVL 10

    Expert Comment

    LVL 11

    Expert Comment

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

    Author Comment

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

    Author Comment

    nevermind. i figured it out myself.

    Accepted Solution

    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";

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now