[AccessD] Linking to different instance of SQLEXPRESS

[Gustav Brock]

Hi Børge

Yes, running two (or more) instances requires individual port numbers.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af Borge Hansen
Sendt: 4. september 2023 06:45
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: [AccessD] Linking to different instance of SQLEXPRESS

So, we've installed SQLEXPRESS 2022 (16.) on a MS Windows Server 2022 (in place update from MS Windows Server 2012)  as a separate instance to the previously installed SQLEXPRESS instance (SQLEXPRESS 2014 version 12.) On the Firewall setup we set SQLEXPRESS2022 instance to listen on port 1433

>From our MS Access frontend for the 2014 version we linked using DSN less connection using [ComputerName]\SQLEXPRESS, 1433 to point to the instance holding the database

To access the restored database now living on the 2022 Express version we then for the DSN less connection used [ComputerName]\SQLEXPRESS2022, 1433

Now on the linked tables on the frontend it says we are linked to SQLEXPRESS2022, 1433 but in actual fact we are still linking to the 2014 version!!! ???

On
https://learn.microsoft.com/en-us/sql/relational-databases/lesson-2-connecting-from-another-computer?view=sql-server-ver16

I found the following:
++++++
Connecting Using the SQL Server Browser Service The SQL Server Browser service listens for incoming requests for SQL Server resources and provides information about SQL Server instances installed on the computer. When the SQL Server Browser service is running, users can connect to named instances by providing the computer name and instance name, instead of the computer name and port number. Because SQL Server Browser receives unauthenticated UDP requests, it is not always turned on during setup. For a description of the service and an explanation of when it is turned on, see SQL Server Browser Service (Database Engine and SSAS).
To use the SQL Server Browser, you must follow the same steps as before and open UDP port 1434 in the firewall.
+++++

INTERESTING IT SAYS:
When the SQL Server Browser service is running, users can connect to named instances by providing the computer name and instance name, instead of the computer name and port number.

So it appears that when using the SQLEXPRESS2022 I should use another port number, otherwise it will link to the first installed sql server instance that is listening to that port number??
Am I correct in assuming that?

As for the SQL Server Browser Service, I am not sure if this service is available by SQL Server Express.

Any ideas??
Thanks,
/borge