[AccessD] Error opening ADODB connection

[Jim Dettman]

 And to add, I always add a suffix to the DSN name of _32 or _64

 Because depending on the type of the DSN, both versions of the ODBC applet
can show 32 or 64 bit DSN's.  That's covered here:

https://docs.microsoft.com/en-us/troubleshoot/sql/connect/odbc-tool-displays
-32-bit-64-bit

Jim.

-----Original Message-----
From: AccessD On Behalf Of Stuart McLachlan
Sent: Sunday, October 24, 2021 9:05 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Error opening ADODB connection

>"[Microsoft][ODBC Driver Manager] Data
> source name not found and no default driver specified".

Sounds like you're got a 32bit  ODBC DSN but are using 64 bit Office

Make sure you are using the odbcad32.exe in System32 (that's the one that
says "64 bit" on 
the caption bar) and not the version in SYSWOW64 (whihc says 32 bit in the
caption bar).

Check that you have the appropriate 64 bit drivers on the Drivers tab. and a
DSN named 
VROM.




On 24 Oct 2021 at 16:46, dw-murphy at cox.net wrote:

> I have a MS Access application that has been in use for at least 10
> years. Recently I moved my work to a new development machine with
> Office 365 (Access 2019) and SQL Server Express 2019 installed. The
> machine OS is Windows 10 Pro. In my old machine my application would
> run just fine. There is a query using a stored procedure that
> retrieves a piece of data from a table in the SQL Server backend. I
> call this procedure using a ADODB recordset based on the stored
> procedure output. All my calls use a connection string based on an
> ODBC DNS. This connection string works fine on the new machine when
> relinking tables to the SQL Server backend, but when I use it in the
> ADODB connection I get an error "[Microsoft][ODBC Driver Manager] Data
> source name not found and no default driver specified". On the old
> Windows 7 development machine this ran fine. It also runs on the
> client's machines which are Windows 10 and Office 365. The code that
> raises the error follows. The error occurs at the open co
>  nnection line. The connection string is:
>  ODBC;DSN=VROM;Trusted_Connection=Yes;APP=2019 Microsoft Office
>  system;DATABASE=VROM1.
> 
> Set con = New ADODB.Connection
>             con.ConnectionString = DLookup("Link", "tblLinkData", "Use
>             = True") con.Open
>      Set rs = New ADODB.Recordset
> My question "Is there a setting in SQL Server that could be causing
> this error?". As far as I can tell both systems are set up the same,
> but there is obviously something different between the two. Is there
> another reason this could be working on one system and not another?
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com