[AccessD] [dba-SQLServer] DSN Less Tables - Could not find installable ISAM

Fri Dec 20 00:49:04 CST 2019

Hi David

As the syntax is:

    UID=Username at YourInstanceName

I believe you should use:

    UID=SUPPORT-Dalyn at Healthcarenz.co.nz@prism-uat

If that will work - with the doubled ampersands - I don't know.

/gustav

-----Oprindelig meddelelse-----
Fra: David Emerson <newsgrps at dalyn.co.nz> 
Sendt: 19. december 2019 21:36
Til: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Cc: Gustav Brock <gustav at cactus.dk>
Emne: RE: [AccessD] [dba-SQLServer] DSN Less Tables - Could not find installable ISAM

Hi Gustav,

I have confirmed that my user ID is SUPPORT-Dalyn at Healthcarenz.co.nz.  This is the reply from the IT guy:
" Can you please confirm that my UID for Azure is SUPPORT-Dalyn at Healthcarenz.co.nz.  It has been questioned that this looks like an email address."
" Yes it is, I have changed the uon to reflect the. Co.nz vs local."

He also confirmed that he installed the 64 bit version of the driver.

I cut and pasted your code into my application.

I used this call:
    Call AttachSqlServer("prism-uat.database.windows.net", "PRISM-UAT", "SUPPORT-Dalyn at Healthcarenz.co.nz", "MYPASSWORD")

This gave a connection string of:
ODBC;DRIVER=SQL Server Native Client
17.0;Description=Prism_UAT;APP=Microsoft® Access;SERVER= prism-uat.database.windows.net;DATABASE=
PRISM-UAT;UID=SUPPORT-Dalyn at Healthcarenz.co.nz@prism-uat.database.windows.ne
t;PWD=MYPASSWORD;Trusted_Connection=No;

This gave error 3151 - ODCB--connection to 'SQL Server Native Client 17.0prism-uat.database.windows.net' failed.

I also tried this call and got the same error message 3151:

    Call AttachSqlServer("prism-uat.database.windows.net", "PRISM-UAT", "SUPPORT-Dalyn", "MYPASSWORD")

Changing the connection string to this had the same result:
ODBC;DRIVER=SQL Server Native Client
17.0;Description=Prism_UAT;APP=Microsoft® Access;SERVER= prism-uat.database.windows.net;DATABASE=
PRISM-UAT;UID=SUPPORT-Dalyn at Healthcarenz.co.nz;PWD=MYPASSWORD;Trusted_Connec
tion=No;

I know I can connect to the data base because the following connection string works:

gADODBConnection.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=tcp:prism-uat.database.windows.net,1433;Database=Prism-UAT;Ui
d=SUPPORT-Dalyn at Healthcarenz.co.nz;Pwd=MYPASSWORD;Encrypt=yes;TrustServerCer
tificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword"

Any more thoughts?

Regards

David

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock via AccessD
Sent: Thursday, 19 December 2019 8:16 p.m.
To: 'Access Developers discussion and problem solving'
Cc: Gustav Brock
Subject: Re: [AccessD] [dba-SQLServer] DSN Tables - Could not find installable ISAM

Hi David

> "[ODBC Driver manager] data source name not found and no default 
> driver
specified"

That error means that the Data Source Name (DSN) you are specifying in your connection configuration is not being found in the windows registry.

But my connection method is DSN-less, so you are mixing the two methods.

Also, your UID seems to be an email address; Azure SQL knows nothing about that.
So, at least as a starting point, stick with the exact syntax from my example.

As for the driver install, and as I recall it, it installs by default both 32-bit and 64-bit if Windows is 64-bit. But you can install the 32-bit only, thus - if you run a 64-bit application - that will fail.

/gustav

-----Oprindelig meddelelse-----
Fra: David Emerson <newsgrps at dalyn.co.nz>
Sendt: 18. december 2019 22:49
Til: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Cc: Gustav Brock <gustav at cactus.dk>
Emne: RE: [AccessD] [dba-SQLServer] DSN Tables - Could not find installable ISAM

Hi Gustav,

I have had the Tech guy install SQL Server Native Client 17.0.

When I use a connection testing program this connection string connects to the server:

Driver={ODBC Driver 17 for SQL
Server};Server=tcp:prism-uat.database.windows.net,1433;Database=Prism-UAT;Ui
d=SUPPORT-Dalyn at Healthcarenz.co.nz;Pwd=MYPASSWORD;Encrypt=yes;TrustServerCer
tificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword

But I am still getting the "Could not find installable ISAM" error when I use this in my table linking code.

When I use this one from your example in the connection test program it doesn't connect:

DRIVER=SQL Server Native Client 17.0;Description=PRISM;APP=Microsoft®
Access;SERVER=tcp:prism-uat.database.windows.net,1433;DATABASE=Prism-UAT;Tru
sted_Connection=No;UID=SUPPORT-Dalyn at Healthcarenz.co.nz;PWD=MYPASSWORD

The error message is "[ODBC Driver manager] data source name not found and no default driver specified"

I am using system.data.odbc as the provider.

What am I doing wrong?

Regards

David

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock via AccessD
Sent: Wednesday, 18 December 2019 8:02 p.m.
To: Access Developers discussion and problem solving
Cc: Gustav Brock
Subject: Re: [AccessD] [dba-SQLServer] DSN Tables - Could not find installable ISAM

Hi David

Yes. Use the latest ODBC driver and a connection string like:

DRIVER=SQL Server Native Client 17.0;Description=Your application name;APP=Microsoft® Access;SERVER=tcp:YourInstanceName.database.windows.net,1433;DATABASE=YourDb
Name;Trusted_Connection=No;UID=Username at YourInstanceName;PWD=YourPassword;

We have these two functions to control that and relink the tables and PT
queries:

<code>
' Create ODBC connection string from its variable elements.
'
' 2018-10-02. Cactus Data ApS, CPH.
'
Public Function ConnectionString( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal Password As String) _
    As String

    Const AzureDomain   As String = ".windows.net"
    Const OdbcConnect   As String = _
        "ODBC;" & _
        "DRIVER=SQL Server Native Client 11.0;" & _
        "Description=Cactus TimeSag og Finans;" & _
        "APP=Microsoft® Access;" & _
        "SERVER={0};" & _
        "DATABASE={1};" & _
        "UID={2};" & _
        "PWD={3};" & _
        "Trusted_Connection={4};"

'    Examples (Note, optional encryption):
'        "ODBC;Driver=SQL
Server;Server=tcp:b8uros8p4x.database.windows.net;Database=cactus;UID=cactus
@ b8uros8p4x;Pwd=Password;Encrypt=yes"
'
'        "ODBC;Driver=SQL Server Native Client
17.0;Server=(localdb)\MSSQLLocalDB;Database=Test;Trusted_Connection=Yes"

    Dim FullConnect     As String

    If Right(Hostname, Len(AzureDomain)) = AzureDomain Then
        ' Azure SQL connection.
        ' Append servername to username.
        Username = Username & "@" & Split(Hostname)(0)
    End If
    FullConnect = OdbcConnect
    FullConnect = Replace(FullConnect, "{0}", Hostname)
    FullConnect = Replace(FullConnect, "{1}", Database)
    FullConnect = Replace(FullConnect, "{2}", Username)
    FullConnect = Replace(FullConnect, "{3}", Password)
    FullConnect = Replace(FullConnect, "{4}", IIf(Username & Password = "", "Yes", "No"))

    ConnectionString = FullConnect

End Function

' Attach all tables linked via ODBC to SQL Server or Azure SQL.
'
' 2016-04-24. Cactus Data ApS, CPH.
'
Public Function AttachSqlServer( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal Password As String) _
    As Boolean

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim qdf             As DAO.QueryDef

    Dim strConnect      As String
    Dim strName         As String

    On Error GoTo Err_AttachSqlServer

    Set dbs = CurrentDb
    strConnect = ConnectionString(Hostname, Database, Username, Password)

    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
            If InStr(tdf.Connect, cstrDbType) = 1 Then
                If Left(strName, Len(cstrAcPrefix)) = cstrAcPrefix Then
                    tdf.Name = Mid(strName, Len(cstrAcPrefix) + 1)
                End If
                tdf.Connect = strConnect
                tdf.RefreshLink
                Debug.Print Timer, tdf.Name, tdf.SourceTableName, tdf.Connect
                DoEvents
            End If
        End If
    Next

    For Each qdf In dbs.QueryDefs
        If qdf.Connect <> "" Then
            Debug.Print Timer, qdf.Name, qdf.Type, qdf.Connect
            qdf.Connect = strConnect
        End If
    Next
    Debug.Print "Done!"

    AttachSqlServer = True

Exit_AttachSqlServer:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function

Err_AttachSqlServer:
    Call ErrorMox
    Resume Exit_AttachSqlServer

End Function
</code>

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces at databaseadvisors.com> På vegne af David Emerson
Sendt: 17. december 2019 19:30
Til: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Emne: Re: [AccessD] [dba-SQLServer] DSN Tables - Could not find installable ISAM

Does anyone have experience with linking tables to Azure? 