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

Paul Wolstenholme Paul.W at industrialcontrol.co.nz
Sun Apr 11 16:36:42 CDT 2021


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
>


More information about the AccessD mailing list