How to connect to two different databases in two different directories in one vb app

Thanks for detailed explanation-this is a common limitation when using DataDirectory since it supports only a single substitution value per AppDomain

Root Cause

|Data Directory| is a global setting. Once you set it (via AppDomain.CurrentDomain.SetData("DataDirectory", path), it applies to all connection string using that placeholder. That's why both .mdf files are being resolved to the same directory.

Recommended Approach

You should avoid relying on | DataDirectory| for scenarios where multiple database files reside in different locations. Instead, dynamically build your connection strings at runtime with explicit paths.

Solution 1: Build connection Strings Dynamically

At application startup:

Dim commonDbPath As String = System.IO.Path.Combine(Application.StartupPath, "common.mdf")
Dim variablePath As String = userSelectedPath 'e.g. from the folder picker
Dim commonCpnnStr As String = $"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={commonDbPath};Integrated Security=True;Connect Tomeout=30"

Dim variableConnStr As String = $"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={variableDbPath};Integrated Security=True;Connect Tomeout=30"

This ensures each database points to its correct directory independently.

Solution 2: Store Base Paths in Config (Optional)

In App.config, store partial strings:

<appSettings>
	<add key="CommonDbPath" value="common.mdf"/>
</appSettings>

Then resolve full paths in code:

Dim basePath = Application.StartupPath
Dim commonDb = Path.Combine(basePath, ConfigurationManager.AppSettings("CommonDbPath"))

What not to do:

• Avoid trying to use multiple DataDirectory values (e.g., DataDirectory1, DataDirectory2)- this is not supported
• Avoid switching DataDirectory at runtime before each connection - this is unsafe and can lead to unpredictable behavior.

if the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Use two different connection strings and avoid relying on a single |DataDirectory| value for both databases.

|DataDirectory| is a single global substitution; it cannot be different per connection. So either:

1. Put only the common DB in config, and build the variable DB connection string in code, or
2. Build both connection strings in code and skip |DataDirectory| entirely.

Example approach that matches the scenario:

1. Keep common.mdf fixed next to the EXE

<connectionStrings>
  <add name="CommonConnectionString"
       providerName="Microsoft.Data.SqlClient"
       connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\common.mdf;Integrated Security=True;Connect Timeout=30" />
</connectionStrings>

At app startup (before opening the common DB), set DataDirectory to the EXE folder:

Dim exeDir = System.IO.Path.GetDirectoryName(Application.ExecutablePath)
AppDomain.CurrentDomain.SetData("DataDirectory", exeDir)

Now CommonConnectionString will always resolve to <exeDir>\common.mdf.

2. Let the user choose the folder for variable.mdf and build that connection string in code

Dim clientFolder As String = "C:\Path\Chosen\By\User"   'your folder picker result
Dim variablePath = System.IO.Path.Combine(clientFolder, "variable.mdf")

Dim variableConnectionString As String =
    $"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={variablePath};Integrated Security=True;Connect Timeout=30"

Use ConfigurationManager.ConnectionStrings("CommonConnectionString").ConnectionString for the common DB, and variableConnectionString for the per‑client DB.

If desired, the per‑client connection strings can be stored in an external config file (for example one per client) and loaded at runtime, since external config fragments for <connectionStrings> are supported and require no special coding beyond specifying the file path.

References:

• Connection strings and configuration files
• SqlClient Support for LocalDB