[AccessD] DSNLess remote tables return 'dbo' for user_name()

[Ryan W]

Thanks Paul!

I do have re-linking code, but I do like your way to check if the files
exist.

I think I'll integrate the FileExists method into my ConnStr function, if
it finds the latest driver it uses that, if not it falls back to the "oldy
moldy" one that comes with Windows....

I've also deployed VC 15 Redist (a dependency of ODBC Driver 17) and the
ODBC driver to my workstations this morning before people came into work
(Thanks to PDQ Deploy!) so I'm looking forward to see if there's any
perceived difference.



On Sun, Apr 11, 2021 at 4:37 PM Paul Wolstenholme <
Paul.W at industrialcontrol.co.nz> wrote:

> Ryan,
>
> Until recently my customer had been using SQL Server 2008R2 with SQL Server
> native client 11.  That single driver handles both ODBC and ADO
> connections, each of which has its advantages.  For our upgrade to SQL
> Server 2014 we needed to replace this with 2 drivers (Microsoft ODBC Driver
> 17 for SQL Server and Microsoft OLE DB Driver for SQL Server) because both
> options have not been available in the same driver since SQL Server 2012.
>
> It is easy enough to put some code into your Access front end database that
> checks for drivers.  In my case (all customers in the same organisation) I
> made some buttons visible like this:
> Private Sub DriverInstallWarningUpdate()
> On Error GoTo MyErrorHandler
>     ' Test whether Microsoft "ODBC Driver 17 for SQL Server" is installed
>     Me.cmdDriverInstallODBC.Visible = Not
> FileExists("C:\Windows\System32\MSODBCSQL17.DLL")
>     ' Test whether "Microsoft OLE DB Driver for SQL Server" is installed
>     Me.cmdDriverInstallOLE.Visible = Not
> FileExists("C:\Windows\System32\MSOLEDBSQL.DLL")
> MyExit:                 ' Label to resume after error.
>     Exit Sub            ' Exit before error handler.
> MyErrorHandler:         ' Label to jump to on error.
>  ...
> End Sub
>
> The FileExists routine came from Allen Browne. http://allenbrowne.com
>
> Because my customer's computers are all on the same LAN, I was able to have
> those buttons execute a copy of the driver install package that had been
> downloaded to their LAN.  All my efforts to have users download and test
> the new drivers prior to the SQL Server upgrade were in vain - driver
> installation only happened after the new Server went live.
>
> Note also that including the code in your Access database to relink to
> another SQL Server database is a VERY good idea:
> + The linked table manager doesn't deal with pass-thru queries.  Their
> connection strings need changing.
> + The linked table manager fails to relink SQL Server views correctly.  It
> fails to create the index in Access that is lost during relinking - and
> that renders the linked view as read-only).
> + By including this code you add the ability to switch between the live
> back end database and a development or test database.
> + It sets you up for your planned Server upgrade by giving you the ability
> to develop and test Access with either back-end environment and deploy it
> for either.
> I found Chipman & Baron "Microsoft Access developer's guide to SQL Server"
> chapter 11 to be useful.
>
> Paul Wolstenholme
>
>
> On Sun, 11 Apr 2021 at 02:02, Ryan W <wrwehler at gmail.com> wrote:
>
> > According to connectionstrings site the 17 version works on 2008. It
> > seemed to work on my workstation just fine but I do have the driver
> loaded
> > (something must have installed it).
> >
> > That said a more bare bones machine without all the dev stuff I have on
> > doesn’t have anything but the plain Jane “sql server” driver. No version
> 11
> > or 13.
> >
> > As I said that would become a dependency I’d have to remember to install
> > once we roll out workstation updates again. May not be worth it.
> >
> > Sent from my iPhone
> >
> > > On Apr 10, 2021, at 8:50 AM, Stuart McLachlan <stuart at lexacorp.com.pg>
> > wrote:
> > >
> > > 
> > >>
> > >> still on SQL Server 2008.
> > >
> > > Ouch!  ver 17:   "works with all currently suppported versions of SQL
> > Server."
> > > 2008 is no longer supported.
> > > 2012 Extended support ends Dex 2022.
> > >
> > > Best to stay with Ver11 or 13 for now.
> > >
> > >
> > >> On 10 Apr 2021 at 8:37, Ryan W wrote:
> > >>
> > >> I just checked the big cheeses machine (he barely does anything on it)
> > >> and does NOT have the ODBC Driver 17 loaded... so apparently even
> > >> Windows 10 just comes with the base driver.  Which is probably the
> > >> reason I've kept using DRIVER=SQL Server for so long, because it's the
> > >> least common denominator and does not add dependencies when rolling
> > >> out the app when we upgrade workstations.
> > >>
> > >> I'll have to consider whether the move is worth it, if it's just for
> > >> compatibility with new datatypes, at this time it's not a problem
> > >> since we're still on SQL Server 2008... but have plans to move to SQL
> > >> Server 2019 within the year.
> > >>
> > >
> > > --
> > > 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
>