DSN-less connection error Sharepoint 365

[supmktg]

I have uploaded my back end database to an Access App on Sharepoint 365. I have set the permissions to allow access from any location and to allow read-write. When I create a ODBC data source in the ODBC Data Source Administrator, the connection works fine.

My front end includes code to make a DSN-less connection to the tables. That code was working fine when the back end was on a "normal" SQL Server. Now, on Sharepoint, it fails with error 3151.

Here is the code I'm using:

Private Sub cmdConnect_Click()
Dim rst As DAO.Recordset
Dim strTblName As String
Dim strLocTblName As String
Dim strRemTblName As String
Dim strServer As String
Dim strDatabase As String
Dim strUser As String
Dim strPWD As String
Dim stConnect As String
Dim td As TableDef

   On Error GoTo cmdConnect_Click_Error

strServer = Me.txtServer '= xxxxxxxxxx.database.windows.net
strDatabase = Me.txtDB '= xx_xxxxxxxx_xxxx_xxxx_xxxx_xxxxxxxxxxxx
strUser = Me.txtUID '= xx_xxxxxxxx_xxxx_xxxx_xxxx_xxxxxxxxxxxx_ExternalWriter
strPWD = Me.txtPwd '= xxxxxxxxxxxxxxx

    
Set rst = CurrentDb.OpenRecordset("tblLinkTables")

    If rst.RecordCount > 0 Then
        rst.MoveLast
        rst.MoveFirst
        
        Do While Not rst.EOF
        strLocTblName = rst![TableName]
        strRemTblName = "Access." & strTblName
        
            For Each td In CurrentDb.TableDefs
                If td.Name = strLocTblName Then
                    CurrentDb.TableDefs.Delete strLocTblName
                End If
            Next
      
    stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=tcp:" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword & ";Encrypt=yes;"
    
    Set td = CurrentDb.CreateTableDef(strLocTblName, dbAttachSavePWD, strRemTblName, stConnect)
    
    CurrentDb.TableDefs.Append td
        
        rst.MoveNext
        Loop

    End If

   On Error GoTo 0
   Exit Sub

cmdConnect_Click_Error:

    MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure cmdConnect_Click of VBA Document Form_AfrmTestCode"

End Sub

the error occurs at the line:

CurrentDb.TableDefs.Append td

I've been trying to correct this for 2 solid days without success. I've tried several different styles of connection strings (all using the exact credentials that worked in the ODBC Administator). I'm at my wit's end.

Does anyone see an error in my code?

Has anyone had success making a DSN-less connection to SQL Azure on Sharepoint 365?

Thanks,
Sup

 

[supmktg]

Update: DSN-less connection error Sharepoint 365

With fresh eyes this morning and a little more research I found my problems. In the interest of saving others the wasted time and frustration that I experienced, here is what I did to make my DSN-less connections work:

1) In order to successfully link tables from an SQL Azure database on Sharepoint 365 you must use the Microsoft SQL Server Native Client 11.0, now called "ODBC Driver 11 for SQL Server". I corrected the driver name in the connection string.

2) After copying and pasting various attempts at my DSN-less code, I eventually ended up with incorrect variable names in the connection string. I corrected the variable names.

Here is the code that I finally got to work:

Private Sub cmdConnect_Click()
[COLOR="SeaGreen"]'//Connects Access front end to back end tables 
'located in an SQL Azure database
' in an Access App on Sharepoint 365[/COLOR]
Dim rst As DAO.Recordset
Dim strTblName As String
Dim strLocTblName As String
Dim strRemTblName As String
Dim strServer As String
Dim strDatabase As String
Dim strUser As String
Dim strPWD As String
Dim stConnect As String
Dim td As TableDef

   On Error GoTo cmdConnect_Click_Error

[COLOR="SeaGreen"]'This form has 4 text boxes that contain
the connection login credentials
[/COLOR]
strServer = Me.txtServer
strDatabase = Me.txtDB
strUser = Me.txtUID
strPWD = Me.txtPwd

[COLOR="SeaGreen"]'This database has a local table named "tblLinkTables"
'that has the names of all of the linked tables in a field called "TableName"
[/COLOR]
Set rst = CurrentDb.OpenRecordset("tblLinkTables")

    If rst.RecordCount > 0 Then
        rst.MoveLast
        rst.MoveFirst

        Do While Not rst.EOF
        strLocTblName = rst![TableName]
[COLOR="SeaGreen"]'On Sharepoint 365 SQL Azure created in an Access App,
'the tables are prefixed with "Access." (like "dbo." in SQL Server) so the
'remote table name is "Access." & strLocTblName[/COLOR]
        strRemTblName = "Access." & strLocTblName
        
            For Each td In CurrentDb.TableDefs
                If td.Name = strLocTblName Then
                    CurrentDb.TableDefs.Delete strLocTblName
                End If
            Next
[COLOR="SeaGreen"]'ODBC driver must be ODBC Driver 11 for SQL Server[/COLOR] 
     stConnect = "ODBC;DRIVER={ODBC Driver 11 for SQL Server};SERVER=tcp:" & strServer & ";DATABASE=" & strDatabase & ";UID=" & strUser & ";PWD=" & strPWD '& ";Encrypt=yes;"

    Set td = CurrentDb.CreateTableDef(strLocTblName, dbAttachSavePWD, strRemTblName, stConnect)
    
    CurrentDb.TableDefs.Append td
    CurrentDb.TableDefs(strLocTblName).RefreshLink
        
        rst.MoveNext
        Loop

    End If

   On Error GoTo 0
   Exit Sub

cmdConnect_Click_Error:

    MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure cmdConnect_Click of VBA Document Form_AfrmTestCode"

End Sub

I hope this helps a few others!
Sup

 

[msaddict]

@supmktg
Many thanks for this wonderful work!!! I did mess around with this (whats a few days to an addict?) and believe me pulling out teeth is kinder. I do believe someone somewhere writing books on MS Access Web Apps are sitting there laughing at us all trying to work it out inch by painful inch. That said, I've also tried your code and tried and tried but it simply did not allow me to get on without an error...... More searching ----- I was convinced something was still wrong with the ODBC driver name so I did some more searching - whats new

Here's a very interesting comment from Pang
on another forum - cant put link here cause I'm too young!!!!

essentially saying try the other odbc possibility for MS Access Web apps
So i changed as follows and error free.
stConnect = "ODBC;DRIVER={SQL Server Native Client 11.0}


Kudos for your massive efforts!

Happiness is getting to bed whenever you like.

 

[David232]

Thank you SupMktg, did this a lot of years ago in SQL but wanted to do this with a pure SQL Azure DB as opposed to A SharePoint one.

Main changes were the table name being very old school so.
Azure SQL Table Name : dbo.t_Employee
tblLinkTables table name: t_Employee

This then makes the linked tables that name as well.

Also on Windows 10 it is 'ODBC Driver 13'

Thanks for the code and hope these tweaks help someone else.

Just need to figure out how to get the dbo. back now as may be a need in the future.

Cheers
Dave