[AccessD] [dba-SQLServer] DSN Less Tables - Could not find installable ISAM
David Emerson
newsgrps at dalyn.co.nz
Thu Dec 19 14:35:56 CST 2019
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?
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
More information about the AccessD
mailing list