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

Sat Dec 21 01:29:43 CST 2019

Hi David

OK. You could give it a last attempt with:

    UID=SUPPORT-Dalyn at prism-uat

Also, the Server key must include the tcp:

    SERVER=tcp:prism-uat.database.windows.net,1433

/gustav

________________________________
Fra: David Emerson <newsgrps at dalyn.co.nz>
Sendt: 21. december 2019 01:58
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,

That didn't work either.  The company say they can use Microsoft Premium
Support so I will give that a go and let everyone know how we get on.

Thanks for your time.

David

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

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;