[AccessD] [dba-SQLServer] DSN Tables - Could not find installable ISAM
David McAfee
davidmcafee at gmail.com
Thu Dec 19 12:24:40 CST 2019
Probably not related, but Microsoft put out another bad update this week
that broke existing apps on our end (2nd bad update in 3 weeks!)
All of the sudden we were experiencing ODBC call failed and/or invalid
cursor state errors.
I reverted our O365 boxes with version 1912 (build 12325-20172) to 1911
(build 12228.20364) and the odbc errors went away.
Once again, might not be your issue, but I'd hate to be troubleshooting an
odbc issue, going nuts, only to find out it was due to a bad ms update!
Grrrr!!!
On Wed, Dec 18, 2019, 11:16 PM Gustav Brock via AccessD <
accessd at databaseadvisors.com> wrote:
> 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