Solved Mandatory to install Sql Management studio to have access to a Sql Azure back-end? (1 Viewer)

Etxezarreta

Member
Local time
Today, 15:02
Joined
Apr 13, 2020
Messages
175
Hello everyone,
I developped a Access FE-Sql Azure BE tool, that I have already installed on a few machines. It works well, but all of these users had already Sql Management studio installed. Now, I need to distribute on machines that do not have SSMS: is it mandatory to install SSMS, or is there an other way to connect the BE and the FE?
What are the risks linked to incompatibility between the 64/ 32 bits versions of SSMS and Access? I also guess that the windows version has to be taken into consideration, hasn't it?
Many thanks in advance.
Etxe.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:02
Joined
May 7, 2009
Messages
19,169
You dont need ssms to use sql as be.
What you need is the sql client to be installed on each machine. x32 for x32 os, x64 driver for x64 mso.
 

Etxezarreta

Member
Local time
Today, 15:02
Joined
Apr 13, 2020
Messages
175
Perfect, thank you.
I guess that Sql Express would be enough?
 

isladogs

MVP / VIP
Local time
Today, 14:02
Joined
Jan 14, 2017
Messages
18,186
You don't even need SQL Express on each workstation, just on the server.
As long as the required SQL drivers are installed on each workstation, an Access FE and SQL BE will work just fine.

I don't know whether it different for SQL Azure.
 

Minty

AWF VIP
Local time
Today, 14:02
Joined
Jul 26, 2013
Messages
10,354
As others have stated no need for SQL of any type. What you need is the SQL Server ODBC Driver. It's no different for Azure, ideally ensure everyone is using the same driver, as that makes distribution easier if you are controlling the linked table management.

On newer projects, we have moved over to using ODBC Driver version 17 from here: https://docs.microsoft.com/en-gb/sq...c-driver-for-sql-server?view=sql-server-ver15

It seems reliable and supports all of the latest features and data types.
 

Etxezarreta

Member
Local time
Today, 15:02
Joined
Apr 13, 2020
Messages
175
You don't even need SQL Express on each workstation, just on the server.
As long as the required SQL drivers are installed on each workstation, an Access FE and SQL BE will work just fine.

I don't know whether it different for SQL Azure.
Allright, thank you.
As others have stated no need for SQL of any type. What you need is the SQL Server ODBC Driver. It's no different for Azure, ideally ensure everyone is using the same driver, as that makes distribution easier if you are controlling the linked table management.

On newer projects, we have moved over to using ODBC Driver version 17 from here: https://docs.microsoft.com/en-gb/sq...c-driver-for-sql-server?view=sql-server-ver15

It seems reliable and supports all of the latest features and data types.
Hello Minty,
Thank you very much for this detailled answer, that helps me a great deal.
And thank you very much to the other contributors.
Etxe.
 

Etxezarreta

Member
Local time
Today, 15:02
Joined
Apr 13, 2020
Messages
175
Hi again,
I installed the ODBC Drivers for x64 in the new machine, and here is what appears when I use a form linked to an SqlAzure table:

1609780870468.png

If I clicl into a linked table, and try to update the link:

1609779378112.png

here is the zoomed message :
1609780741452.png


I tried to update the link, but no way to make the connection.
This is strange because I already did the same thing in another computer and it worked.
Any clue about where I should look at?
Many thanks.
Etxe.
 

Minty

AWF VIP
Local time
Today, 14:02
Joined
Jul 26, 2013
Messages
10,354
Can you connect from that machine to the Azure server at all?
How are you controlling access to the Azure server?

The actual driver won't make any difference once you have relinked the tables unless you are specifying it in code somewhere and using VBA to relink with a fixed connection string. If you are doing that the VBA connection strings obviously have to match the installed drivers.
 

Etxezarreta

Member
Local time
Today, 15:02
Joined
Apr 13, 2020
Messages
175
Can you connect from that machine to the Azure server at all?
How are you controlling access to the Azure server?

The actual driver won't make any difference once you have relinked the tables unless you are specifying it in code somewhere and using VBA to relink with a fixed connection string. If you are doing that the VBA connection strings obviously have to match the installed drivers.
Very strange, we checked again and it works now.
Maybe it takes a few hours to accept a connection from a new machine?
The firewall rule is that any IP address is accepetted from 0.0.0.0 to 255.255.255.255
 

Minty

AWF VIP
Local time
Today, 14:02
Joined
Jul 26, 2013
Messages
10,354
It normally only takes a few seconds to update an IP address in the firewall. Not sure I would open a database up completely like that unless you are also using AD security as well.
 

Users who are viewing this thread

Top Bottom