Access and SQL Server on Azure (1 Viewer)

[JohnPapa]

First step:
View attachment 108084
Then
View attachment 108085
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:
View attachment 108086
Then you get to enter your detials
View attachment 108087

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:
View attachment 108088

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

While I check if I saved the DSN with Password, please find below a link to a video showing the various actions

LinkedTable.mp4

Shared with Dropbox

www.dropbox.com

 

[JohnPapa]

Saving the Password solved part of the problem.

With the sub which I show in #17 it appears that

CurrentDb.TableDefs.Delete stLocalTableName

deletes the link.

Subsequently, the recreation of the link fails. Please see video

LinkedTable2.mp4

Shared with Dropbox

www.dropbox.com

 

[Minty]

Just to recap. If you create the link manually it works fine.
If you try and recreate it in code it fails.

The code works, it's very similar to code I use. So I suspect the problem can only be with the constructed connection string.
Can you compare the two - debug.print it in your relinking code, and compare it to the saved table def one before it's deleted.

 

[JohnPapa]

Just to recap. If you create the link manually it works fine.
If you try and recreate it in code it fails.

The code works, it's very similar to code I use. So I suspect the problem can only be with the constructed connection string.
Can you compare the two - debug.print it in your relinking code, and compare it to the saved table def one before it's deleted.

If you have a look at the following video

TL4.mp4

Shared with Dropbox

www.dropbox.com

the initial highlighted line is the Description property of the linked table in Design mode. I was hoping that if I recreated the link with the same connection string that it began with, that it would work. In other words, the code deletes the link and I try to recreate the original link, without success unfortunately. I note that the string which I copy below does not mention the Password and the Table name is dbo.tblDum1 instead of dbo_tblDum1 (period instead of underscore.) It behaves the same with a Period and an Underscore

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-KQTNJ42\SQLEXPRESS;UID=savf;Trusted_Connection=No;APP=Microsoft Office;DATABASE=VF1;;TABLE=dbo.tblDum1

 

[Minty]

I'm about to step away from the laptop however, I can tell you the following
dbo.tblDum1 is the correct syntax.

The connection string should be the something like
"DRIVER=ODBC Driver 17 for SQL Server;SERVER=YourServerName;UID=YourUser;PWD=PasswordHere;Trusted_Connection=No;APP=Microsoft Office;DATABASE=YourDatabase;"

There is no table reference when I use it.

I have a local table of tables that I loop through to save renaming everything
See if you pull the bones out of this :
sConn is set to the connection string above for live of another DB for DEV

 Set dbs = CurrentDb
    If bLive Then
        sConn = sProdConn
    Else
        sConn = sDevConn
    End If
    
    'Simply updating the connection string doesn't seem to work.
    'We apepar to have to remove it, then re-create it, easiest way is to pull all the tables into a local table and use that.
    'z_DefTables is where they need to go.
    
    'Call CreateListLinkedTables
    Set rs = CurrentDb.OpenRecordset("Select ServerTable, LocalTable FROM z_DefTables")
        
    For Each tbl In CurrentDb.TableDefs
        If Len(tbl.Connect) > 0 Then
            If Not InStr(1, tbl.Connect, "Excel") > 0 Then          'dont try and relink the excel objects
                CurrentDb.TableDefs.Delete tbl.Name
                i = i + 1
            End If
        End If
    Next
    Set tbl = Nothing
    Debug.Print "Removed " & i & " linked tables"

    i = 1
    rs.MoveFirst
    Do Until rs.EOF
        DoEvents
        Set td = dbs.CreateTableDef(rs!LocalTable, dbAttachSavePWD)
        Debug.Print td.Name
        td.Connect = sConn
        td.SourceTableName = rs!ServerTable
        dbs.TableDefs.Append td
        td.RefreshLink
        rs.MoveNext
        i = i + 1
    Loop
    Debug.Print "Relinked " & i & " tables"
    Set td = Nothing
    
    Exit Sub
    
errorhand:
    
    sErr = sErr & " " & Err.Number & " " & vbCrLf
    Resume Next

[/ICODE]

 

[JohnPapa]

Trying to simplify matters, the following

Dim td as TableDef
Dim stConnect as string


stConnect = "ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-KQTNJ42\SQLEXPRESS;UID=savf;Trusted_Connection=No;APP=Microsoft Office;DATABASE=VF1;;TABLE=dbo.tblDum1"


Set td = CurrentDb.CreateTableDef("dbo.tblDum1", dbAttachSavePWD, "dbo.tblDum1", stConnect)
'Where dbAttachSavePWD = 131072


CurrentDb.TableDefs.Append td

gives the following error

 

[GPGeorge]

Trying to simplify matters, the following

Dim td as TableDef
Dim stConnect as string


stConnect = "ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-KQTNJ42\SQLEXPRESS;UID=savf;Trusted_Connection=No;APP=Microsoft Office;DATABASE=VF1;;TABLE=dbo.tblDum1"


Set td = CurrentDb.CreateTableDef("dbo.tblDum1", dbAttachSavePWD, "dbo.tblDum1", stConnect)
'Where dbAttachSavePWD = 131072


CurrentDb.TableDefs.Append td

gives the following error

View attachment 108128

Have you verified that this table exists in this database on this SQL Server instance?

 

[JohnPapa]

Have you verified that this table exists in this database on this SQL Server instance?

I believe it does, as indicated below

 

[Minty]

The Full stop isn't valid as an access table name, that's why it replaces them with an underscore.

 

[GPGeorge]

My error. Minty is right.

I missed the problem in your first post because I only looked at the server table.

Set td = CurrentDb.CreateTableDef("dbo.tblDum1", dbAttachSavePWD, "dbo.tblDum1", stConnect)

SQL Server has schemas, which are internal containers for tables. Each database has one or more schemas. The default schema is dbo, but you can create others.

Access has no concept of schema, so when it encounters a SQL Server table with the dbo.XXXXX format, indicating table XXXXX in schema dbo, Access changes that from the . to the _ in the name.

Set td = CurrentDb.CreateTableDef("dbo_tblDum1", dbAttachSavePWD, "dbo.tblDum1", stConnect)

Or if you prefer, omit the schema
Set td = CurrentDb.CreateTableDef("tblDum1", dbAttachSavePWD, "dbo.tblDum1", stConnect)

 

[JohnPapa]

Success at last and many thanks for your input. The following works and I had to include the password as well PWD=123456

Dim td As TableDef
Dim stConnect As String
stConnect = "ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-KQTNJ42\SQLEXPRESS;UID=savf;Trusted_Connection=No;APP=Microsoft Office;DATABASE=VF1;PWD=123456;TABLE=dbo.tblDum1"
Set td = CurrentDb.CreateTableDef("tblDum1", dbAttachSavePWD, "dbo.tblDum1", stConnect)
CurrentDb.TableDefs.Append td

Is this supposed to be a DSNless connection?

I will distribute a .accde and when the relinking is done I can prompt for the password (ie not store it). In your opinion how secure is this setup, when it comes to the SQL Server password?

 

[Josef P.]

What security do you need?

If you save the password (dbAttachSavePWD), it is readable.
Even if the password is not stored, the data of the tables can be queried from outside if the ODBC connection to the SQL server is open and the user currently logged into the DB has access rights.
The latter does not bother me in my applications, because I manage the rights to the tables in the SQL server per user group.

 

[JohnPapa]

What security do you need?

If you save the password (dbAttachSavePWD), it is readable.
Even if the password is not stored, the data of the tables can be queried from outside if the ODBC connection to the SQL server is open and the user currently logged into the DB has access rights.
The latter does not bother me in my applications, because I manage the rights to the tables in the SQL server per user group.

I need to able to protect the SQL Server data, in case the SQL Server file is copied.

As I mentioned earlier, the deployment will have a FE .accde file.

When the user logs in the program will create the links and the links will be deleted once the user logs out.

I could somehow connect to the table each time I interface with every table. I use this method with my .NET applications. Linked tables make development much easier and I would hate to complicate matters.

You mention that if the password is saved (dbAttachSavePWD) it is readable. Can I have a linked table without saving the password and without recreating the table link each time I require table data.

 

[Josef P.]

I need to able to protect the SQL Server data, in case the SQL Server file is copied.

How should the file be copied?
If someone has access to the SQL server as admin, then they will also have the right to copy the database.

You mention that if the password is saved (dbAttachSavePWD) it is readable. Can I have a linked table without saving the password and without recreating the table link each time I require table data.

Try:
1. Create a linked table without saved password.
2. close frontend
3. open frontend => data are not readable
4. open a DAO-Connection to Server with password (e. g. in unnamed/unsaved Pass-Through-Querydef or DAO.Database with connectionstring to BE)
5. => data in linked tables are readable

I could somehow connect to the table each time I interface with every table.

Then the password can still be read (if dbAttachSavePWD was used) while the application is running.
=> You could use ADODB. That shields better than DAO

BTW: can you use Windows Server (AD) / Azure AD Authentification?
I avoid SQL server authentication when possible.

 

[JohnPapa]

How should the file be copied?
If someone has access to the SQL server as admin, then they will also have the right to copy the database.

If the SQL Server is at the clients server, I would like to control whether the client can open the SQL Server with SSMS

Try:
1. Create a linked table without saved password.
2. close frontend
3. open frontend => data are not readable
4. open a DAO-Connection to Server with password (e. g. in unnamed/unsaved Pass-Through-Querydef or DAO.Database with connectionstring to BE)
5. => data in linked tables are readable

Regarding 1, 2, 3 I do not know how to create a linked table without a saved pasword. Will look into it.
Regarding 4 and 5 can you please clarify.

Then the password can still be read (if dbAttachSavePWD was used) while the application is running.

Are you saying that whether or not a password is used in the linked table, the contents can be read?

=> You could use ADODB. That shields better than DAO

With ADODB in ACE, I believe you can use OLEDB instead of ODBC. You could connect with the BE and return a recordset and manipulate the recordset. I believe you cannot use a recordset for populating a report, although you could populate a subform and base the report on the contents of the subform. In this case, do you steer away from a linked table and the ease with which you could use the form wizard to create a form?

BTW: can you use Windows Server (AD) / Azure AD Authentification?
I avoid SQL server authentication when possible.

I could but it requires that AD exists. Would rather keep it simple.

 

[cheekybuddha]

I need to able to protect the SQL Server data, in case the SQL Server file is copied.

If the SQL Server is at the clients server, I would like to control whether the client can open the SQL Server with SSMS

I'm not sure you understand how SQLServer works - it is not a file like an Access file.

SSMS or Access 'connects' to the SQLServer which listens for connections on a port or socket - there is no simple file as such.

 

[JohnPapa]

I'm not sure you understand how SQLServer works - it is not a file like an Access file.

SSMS or Access 'connects' to the SQLServer which listens for connections on a port or socket - there is no simple file as such.

I can view an SQL Server and its dbs with SQL Server Management Studio. I understand there is no simple file, at least in the case of SQL Server.

 

[cheekybuddha]

I understand there is no simple file, at least in the case of SQL Server.

OK, you confused me when you said:

in case the SQL Server file is copied.

I can view an SQL Server and its dbs with SQL Server Management Studio

Yes, with your credentials.

You can create multiple credentials to connect to the server and give them fine-grained privileges as to what they can access within the server (assuming that you administer the server at your client's location).

If your app uses a set of credentials to connect then limit what it can access, or don't give those credentials to the client, just compile them within the app so that it only can connect.

 

[JohnPapa]

You are correct about my mention of "SQL Server File". It was poor wording.

I have dealt with Access, Jet & ACE and SQL Server with .Net (Visual Studio) and communication with Web services.

I have never used Access as a FE with SQL Server, hence the various questions. It is an interesting scenario and I know that for maximum performance tables should not be linked. The ideal scenario is Connect to table, Do work, Disconnect. I would like to have the versatility of using the form wizards etc. It is faster programming and easier maintenance. In the projects which I will use Access FE and SQL BE, security is not of the utmost importance. The use of SQL Server is mainly due to its scalability. I expect lots of data.

 

[JohnPapa]

A problem appeared which you may be able to shed light on.
My current setup is Access 365 with SQL Express both local. I use linked tables.
I have no problem creating a form using the form wizard, but there appears a problem when I create a Combobox on a form.
The Combobox points to the contents of a linked table. When I click on the combobox, the contents of the table appear as options and I also see selections in the Combobox which I manually entered in the table.
I cannot change the content of the Combobox.
I did some research and if it is helpful,
- The form Allow Additions property is True
- As I mentioned, I can enter new values in the table and can edit existing values
- The form Allow Filters property is True