[AccessD] Error opening ADODB connection

[Paul Wolstenholme]

Doug,

https://www.connectionstrings.com/sql-server/

Have a look at the drivers you have installed.  The old SQL Server Native
client and earlier handled both ODBC and OLE in the one driver.  Microsoft
"improved" on that by requiring 2 drivers in the latest versions.

You now need one connection string or DSN for linking tables and another
for ADODB connections.

Regards,
Paul Wolstenholme


On Tue, 26 Oct 2021 at 07:24, <dw-murphy at cox.net> wrote:

> Thanks Stuart,
>
> I checked and I am using a 32 bit DSN, at least it says 32 bit platform. I
> am using 32 bit office.
>
> The frustrating part of this is that the DSN works for relinking tables in
> SQL Server, just not for the ADODB connection. Rather than spending time to
> figure out the issue with this machine I modified the routine that executes
> the stored procedure to use DAO and it works fine.
>
> Doug
>
> -----Original Message-----
> From: AccessD <accessd-bounces+dw-murphy=cox.net at databaseadvisors.com> On
> Behalf Of Stuart McLachlan
> Sent: Sunday, October 24, 2021 6: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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>