Access and SQL Server on Azure

[JohnPapa]

I have developed in Access with Jet and ACE and with .Net Visual Studio and SQL Server on Azure.

I have never tried Access with SQL Server on Azure. Can someone with some experience on the matter shed some light.

Many thanks in advance.

 

[GPGeorge]

It works.

What else do you need to know?

Seriously, Access with SQL Azure is similar in most important ways to Access with any SQL Server database, considering, of course, that it is a remote, or "cloud" location. All performance considerations, therefore, pertain mostly to the issues related to a remotely linked database.

For example, interface design must emphasize minimal data transfer between the Access FE and the SQL Azure BE. You can find numerous discussions here and elsewhere on this topic.

Security considerations come into play, of course, but primarily from the perspective of supporting greater data security in the SQL Server/SQL Azure database.

The two biggest problems you are likely to face are, therefore, slower performance and the need for a rock-solid internet connection. If you design appropriately, and invest in appropriate connectivity, you should be fine.

Do you have any concerns, in particular, though?

 

[JohnPapa]

It works.

What else do you need to know?

Seriously, Access with SQL Azure is similar in most important ways to Access with any SQL Server database, considering, of course, that it is a remote, or "cloud" location. All performance considerations, therefore, pertain mostly to the issues related to a remotely linked database.

For example, interface design must emphasize minimal data transfer between the Access FE and the SQL Azure BE. You can find numerous discussions here and elsewhere on this topic.

Security considerations come into play, of course, but primarily from the perspective of supporting greater data security in the SQL Server/SQL Azure database.

The two biggest problems you are likely to face are, therefore, slower performance and the need for a rock-solid internet connection. If you design appropriately, and invest in appropriate connectivity, you should be fine.

Do you have any concerns, in particular, though?

Many thanks GPGeorge for your reply.

You have provided answers to most of my questions. If something else comes up I will come back.

 

[Minty]

Probably 90% of our clients are on an Azure SQL BE and Access FE.
Designed carefully they work very reliably. It's never going to have the performance of a LAN-based local SQL server, but it's more than acceptable for most processes.

 

[JohnPapa]

Probably 90% of our clients are on an Azure SQL BE and Access FE.
Designed carefully they work very reliably. It's never going to have the performance of a LAN-based local SQL server, but it's more than acceptable for most processes.

Hi Minty and thanks for your reply.

Do you use the Access Runtime in your deployments?
Are there any 32bit 64bit conflict issues with installed Office?
Do you use ADO & OleDB, DAO & OBBC?
Do you use linked tables?
Do you have security issues?

 

[Minty]

We occasionally use runtime, but a majority of clients have 365 subscriptions, if they do use runtime we make sure we know whether they have 32 or 64-bit installed for distribution.

We use DSN-Less linked tables with ODBC connections.
We have sometimes completely disconnected the tables and only connect during start-up. It depends on the nature of the data and the customers needs/worries about data security.

Security - we have encrypted logins, and utilise the IP address whitelisting facilities in Azure.
We have built a system that allows a registered user to update their home working IP address via a secure anonymous HTTP link.

 

[JohnPapa]

We occasionally use runtime, but a majority of clients have 365 subscriptions, if they do use runtime we make sure we know whether they have 32 or 64-bit installed for distribution.

We use DSN-Less linked tables with ODBC connections.
We have sometimes completely disconnected the tables and only connect during start-up. It depends on the nature of the data and the customers needs/worries about data security.

Security - we have encrypted logins, and utilise the IP address whitelisting facilities in Azure.
We have built a system that allows a registered user to update their home working IP address via a secure anonymous HTTP link.

Regarding DSNless connections I came across in this link

how to do a DSN less connection in Access (what is Local and pubs) - Microsoft Community

the following

"... Just keep in mind that you don’t really need any VBA code at all.
If you use the linked table manager and link using a file DSN, then that connection is DSN less.
At that point you can simply distribute the front end to each workstation, and no further setup is required.
What the above means is you really don’t need any VBA code at all to create DSN-less connections. ..."

What is your view on this?

 

[Minty]

Our approach varies depending on the client, some want as much direct access to the data/table restricted and the connections hidden, some aren't bothered. I always have a file DSN for a client locally as It makes connecting to new tables a breeze and I don't need to remember a million different database passwords.

For the majority of clients (generally SME's), we simply use the linked table manager and have routines inbuilt to switch from a Production BE to Dev/Test BE databases. We remove easy access to the tables/navigation options etc. but don't try and make it like Fort Knox, as it's simply not required, most small companies trust their staff.

 

[JohnPapa]

Our approach varies depending on the client, some want as much direct access to the data/table restricted and the connections hidden, some aren't bothered. I always have a file DSN for a client locally as It makes connecting to new tables a breeze and I don't need to remember a million different database passwords.

For the majority of clients (generally SME's), we simply use the linked table manager and have routines inbuilt to switch from a Production BE to Dev/Test BE databases. We remove easy access to the tables/navigation options etc. but don't try and make it like Fort Knox, as it's simply not required, most small companies trust their staff.

Many thanks Minty for your reply.

The claim above is that once you use the file DSN once, then you do not need it again and can distribute the FE without the file DSN. IS this in line with your experience and if this is OK, how safe securitywise is this?

 

[JohnPapa]

Attempting to answer my own question... Access gets the information from the File DSN into the linked table and once you have the connection there appears to be no further reference to the file DSN.

My follow up question regarding Security still is there. Is this method secure?

 

[JohnPapa]

I tried to confirm the theory and I created a file DSN and successfully linked a table. I then deleted the DSN file. Each time I try to use the table it prompts me for the username and password of the SQL Server, as indicated in the attachment. Can we get around this?

 

[Minty]

When you link to the table you can opt to store the password, that will remove the need for it to be input.
However, the password is easily accessible if you examine the connection string for the table, so it depends on how locked up you make things as to how secure or not that is.

The alternative is you use code to connect the tables on start-up and provide the password in the code, which if you compile to an accde, is not accessible.

 

[JohnPapa]

When you link to the table you can opt to store the password, that will remove the need for it to be input.
However, the password is easily accessible if you examine the connection string for the table, so it depends on how locked up you make things as to how secure or not that is.

The alternative is you use code to connect the tables on start-up and provide the password in the code, which if you compile to an accde, is not accessible.

For the "alternative" you mention above do I still need a DSN. Better can you suggest some VBA?
Many thanks.

 

[Minty]

There are a lot of posts covering DSN-Less connectivity and web guides.

Creating and managing DSN-less connections

What is Data Source Name (DSN)? It is either a simple text file or values in registry that contains information about how to connect to a ODBC backend. By creating a DSN, you do not have to supply the information and the coding is simpler as you only need to refer to DSN to fill in the...

www.access-programmers.co.uk

Using DSN-Less Connections

Create DSN-less connection to SQL for linked table - Microsoft 365 Apps

Describes methods for how to create a DSN-less connection to SQL Server for linked tables in Microsoft Access.

learn.microsoft.com

One or all of those should get you going.

 

[JohnPapa]

Thanks again. Found also

Using DSN-Less Connections (accessmvp.com)

 

[Minty]

That's the second link from above

 

[JohnPapa]

I tried using the following (one of your suggestions)

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String


For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next


If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
        MsgBox (stConnect)
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function


AttachDSNLessTable_Err:


AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description


End Function

It found the linked table and I received the error shown in the attachment

at which point it deleted the linked table and prompted me for the username and password of my SQL Server DB on Azure

Any ideas?

 

[Minty]

Interesting error message. Not sure why it would be trying to connect to the Master database?
Let's start at the beginning.

Firstly, examine an existing connection string to ensure the driver details etc. are all as they should be for a working linked table.
Secondly, just as a check, connect to a table using the "new data source" option and save the password to ensure you still have everything as it should be.

Next, I would use a much more modern ODBC driver, bear in mind whatever driver you use will need to be installed on the end user's machine. Personally, we use Version 17 but will be moving over to Version 18 for new clients. Available from here:

Download ODBC Driver for SQL Server - ODBC Driver for SQL Server

Download the Microsoft ODBC Driver for SQL Server to develop native-code applications that connect to SQL Server and Azure SQL Database.

learn.microsoft.com

You will need to adjust the connection string to match the driver being used.

If a new driver and the correct connection string still fail report back and we'll engage the bigger thinking caps.

 

[JohnPapa]

Interesting error message. Not sure why it would be trying to connect to the Master database?
Let's start at the beginning.

Firstly, examine an existing connection string to ensure the driver details etc. are all as they should be for a working linked table.
Secondly, just as a check, connect to a table using the "new data source" option and save the password to ensure you still have everything as it should be.

Next, I would use a much more modern ODBC driver, bear in mind whatever driver you use will need to be installed on the end user's machine. Personally, we use Version 17 but will be moving over to Version 18 for new clients. Available from here:

Download ODBC Driver for SQL Server - ODBC Driver for SQL Server

Download the Microsoft ODBC Driver for SQL Server to develop native-code applications that connect to SQL Server and Azure SQL Database.

learn.microsoft.com

You will need to adjust the connection string to match the driver being used.

If a new driver and the correct connection string still fail report back and we'll engage the bigger thinking caps.

So as to be on the same track, can you please tell me which method to use to create the linked table.

 

[Minty]

First step:

Then

Then choose an existing DSN or create a new one which will prompt you for the server details. If you go New you can pick the driver to use:

Then you get to enter your detials

Hopefully from there you get a list of tables and views on your server, select one and create the link.
There is a box that says save password:

This should create a linked table and the the appropriate connection string.