Access Visual Studio 2008 server-based database from VB 6 project

Question

Is it possible to access a Visual Studio 2008-created server-based database from a VB-6 application? If not, what is the best way to share a database between a VB-6 application and a C# application?

Answer 1

If you are referring to the SQL Server Express database that comes with Visual Studio 2008, you can access that with plain old ADO.

The connection strings for doing this are at http://connectionstrings.com/sql-server-2005. The most appropriate one is probably this one:

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;

For SQL Server Express (which is what comes with VS2008), don't miss the server name syntax Servername\SQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

The reference for programming against a SQL Server database using ADO is here: http://msdn.microsoft.com/en-us/library/aa905875(SQL.80).aspx

Example for connecting to the SQL Server database is below (untested). Note that this one uses SQL Server authentication rather than Windows Authentication. Check your connection strings on http://www.connectionstrings.com. If SQL Native Client doesn't work, try one of the others.

' Initialize variables.
Dim cn As New ADODB.Connection
Dim connectionString As String

' Specify the OLE DB provider.
cn.Provider = "{SQL Native Client}"

' Specify connection string on Open method.
connectionString = Server=myMachineName\SQLEXPRESS;Database=myDataBase; _
   Uid=myUsername;Pwd=myPassword;
cn.Open connectionString

Answer 2

I just added a "Service-based database" to a project. I go the following in my app.config, in the configuratonStrings element:

<add name="SerializeToSql.Properties.Settings.Database1ConnectionString"
    connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"
    providerName="System.Data.SqlClient" />

I don't know if ADO has user instance support. if it does, then you may be able to create an equivalent connection string for ADO to use. Otherwise, you'll need to just create a "normal" database in SQL Server Express. You should be able to use that from ADO.

Answer 3

In order for other machines to see your SQL Express instance, you need to make sure it allows remote connections, the SQL Browser service is turned on, and is allowed through the firewall.

1. Fire up the SQL Server 2005 Surface Area Configuration tool: All Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration.
2. Click Surface Area Configuration for Services and Connections
3. Click the 'Remote Connections' node and select 'Local and remote connections'. If you want to refer to the server as ServerName\MSSQLSERVER, you'll have to user named pips or TCP/IP and named pipes.
4. Click the SQL Server Browser node. Change the Startup type to Automatic and start the service.

For the firewall, make sure you make an exception for the SQL Server program (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe) and for the SQL Server Browser program (c:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe)