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

Thanks for reaching out.

You’re correct to focus on |DataDirectory|—the key limitation is that it’s global to the AppDomain, so once you set it, every connection string using |DataDirectory| resolves to the same folder. That’s why you can’t have common.mdf in the EXE folder and variable.mdf in a user-chosen folder using two different substitution tokens. [stackoverflow.com], [dori-uw-1.kuma-moon.com]

Good news: using “dynamic connection strings” does not mean you must stop using TableAdapters or rewrite CRUD SQL. TableAdapters already expose a Connection object, and you can simply set TableAdapter.Connection.ConnectionString (or TableAdapterManager.Connection.ConnectionString) at runtime and continue to use the existing Fill/Update/Insert/Delete methods as-is. [stackoverflow.com], [Directly a...soft Learn | dori-uw-1.kuma-moon.com], [Update dat...soft Learn | dori-uw-1.kuma-moon.com], [The New Ta...soft Learn | dori-uw-1.kuma-moon.com]

Example (VB.NET) – keep TableAdapters, point each DB to its correct folder

Imports System.IO

Dim commonDbPath As String = Path.Combine(Application.StartupPath, "common.mdf")

Dim variableDbPath As String = Path.Combine(userSelectedFolder, "variable.mdf")

Dim commonConnStr As String =

    $"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={commonDbPath};Integrated Security=True;Connect Timeout=30"

Dim variableConnStr As String =

    $"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={variableDbPath};Integrated Security=True;Connect Timeout=30"

' Common DB adapters

Me.CommonTableAdapter.Connection.ConnectionString = commonConnStr

' Variable DB adapters

Me.VariableTableAdapter.Connection.ConnectionString = variableConnStr

About “external config per client”

.NET does support external config fragments for <connectionStrings> using configSource. However, configSource has a restriction: it must refer to a file in the same directory or a subdirectory of the main app config—so it won’t directly point to an arbitrary user-selected client folder. [dori-uw-1.kuma-moon.com] [stackoverflow.com]

So the practical model is either:

1. Store the per-client connection string in a small XML/settings file in the client folder, read it at startup, and assign it to the variable TableAdapters (same as above), or
2. Keep the connection strings in app settings and just update the variable connection string at runtime.

Let me know if you need any further help with this. I will be happy to assist. If you find this helpful, Kindly mark the provided solution as "Accept Answer", so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

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