[AccessD] Link to AdventureWorks Database

[Jim Dettman]

<< Be aware that the Access's linked table manager is hopeless when it comes
to linking to SQL Server views (this does not apply if linking only to
tables).  You will be asked which fields uniquely identify records and it
will (secretively) create an index in Access based on that.  When you use
the linked table manager again to refresh the links you do not get asked,
the index is destroyed and the linked table becomes read-only.  I don't
know whether this has been improved since Access 2010>>

 The linked table manager did get an overhaul, but I believe it still does
not remember the indexes on linked views.

 Dale Fye's linked table manager does, and it works with older versions of
Access:

http://dev-soln.com/linked-table-manager/

 And it does views as well as PT queries.

 Here's a presentation of it:

https://www.youtube.com/watch?v=_7irB89Usls

Jim.

-----Original Message-----
From: AccessD On Behalf Of Paul Wolstenholme
Sent: Tuesday, January 10, 2023 3:29 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Link to AdventureWorks Database

Arthur,

You should use "ODBC driver 17 for SQL Server" if any of the more recent
SQL Server features (e.g. newer data types) are being used.  Older drivers
look like they do the job but they will actually connect to new fields as
read-only and will convert the data type to a text string.
For recent connection string details I find this link useful:
https://www.connectionstrings.com/sql-server/

The original drivers supported both ODBC and ADO in the same driver.  The
newer drivers support one or the other.  To link tables you need the ODBC
drivers.  Perhaps AdventureWorks doesn't use ADO at all - I use both on my
databases.

Be aware that the Access's linked table manager is hopeless when it comes
to linking to SQL Server views (this does not apply if linking only to
tables).  You will be asked which fields uniquely identify records and it
will (secretively) create an index in Access based on that.  When you use
the linked table manager again to refresh the links you do not get asked,
the index is destroyed and the linked table becomes read-only.  I don't
know whether this has been improved since Access 2010.

Paul Wolstenholme


On Wed, 11 Jan 2023 at 04:42, Arthur Fuller <fuller.artful at gmail.com> wrote:

> Oops! I have the connection working, but am point at master, not
> AdventureWorks. On the dialog that wanted me to select the default
> database, the combo-box would not drop down, so I left it at default. I
> will try again and see if I can select AdventureWorks now.
>
> On Tue, Jan 10, 2023 at 10:39 AM Arthur Fuller <fuller.artful at gmail.com>
> wrote:
>
> > I'm in! Hooray! Thanks for the assistance.
> >
> > On Tue, Jan 10, 2023 at 10:34 AM Arthur Fuller <fuller.artful at gmail.com>
> > wrote:
> >
> >> Still having trouble. In the New Data Source dialog, I see several
> >> entries:
> >> ODBC Driver for SQL Server
> >> SQL Server
> >> SQL Server Native Client 11.0
> >> SQL Server Native Client RDA 11.0
> >> Which of these did you choose? I selected SQL Server. Perhaps that was
> >> wrong. While I await your response, I will try the ODBC driver instead.
> >>
> >> On Tue, Jan 10, 2023 at 10:18 AM Arthur Fuller <fuller.artful at gmail.com
> >
> >> wrote:
> >>
> >>> Interesting. I shall try it again. Thanks for looking into it.
> >>>
> >>> On Tue, Jan 10, 2023 at 9:57 AM Ryan W <wrwehler at gmail.com> wrote:
> >>>
> >>>> What sort of problem are you having?
> >>>>
> >>>> I just downloaded adventureworks and linked the HumanResources and
> >>>> Person
> >>>> schemas. the tables are named such in access:
> >>>>
> >>>> HumanResources.Department became HumanResources_Department
> >>>>
> >>>> Person.Address became Person_Address
> >>>>
> >>>>
> >>>>
> >>>> On Tue, Jan 10, 2023 at 8:30 AM Arthur Fuller <
> fuller.artful at gmail.com>
> >>>> wrote:
> >>>>
> >>>> > That's what I am trying to do. It seems that Access is confused by
> the
> >>>> > namespaces. The tables are not simply called <tablename> but rather
> >>>> > <namespace>.<tablename>.
> >>>> >
> >>>> > On Tue, Jan 10, 2023 at 9:27 AM Ryan W <wrwehler at gmail.com> wrote:
> >>>> >
> >>>> > > I've never tinkered with the AdventureWorks DB but if you need a
> >>>> one time
> >>>> > > link just use the Linked Table Manager?
> >>>> > >
> >>>> > >
> >>>> > >
> >>>> > > On Tue, Jan 10, 2023 at 8:25 AM Arthur Fuller <
> >>>> fuller.artful at gmail.com>
> >>>> > > wrote:
> >>>> > >
> >>>> > > > I am trying to figure out how to link Access to the SQL server
> >>>> > > > AdventureWorks database. This database uses namespaces to
gather
> >>>> the
> >>>> > > tables
> >>>> > > > into groups that describe their collective function: names such
> as
> >>>> > > > Sales.TableName, and so on.
> >>>> > > >
> >>>> > > > Has anyone successfully linked to this particular database, or
> any
> >>>> > other
> >>>> > > > that uses namespaces? If so, how did you achieve this?
> >>>> > > >
> >>>> > > > --
> >>>> > > > Arthur
> >>>> > > > --
> >>>> > > > 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
> >>>> > >
> >>>> >
> >>>> >
> >>>> > --
> >>>> > Arthur
> >>>> > --
> >>>> > 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
> >>>>
> >>>
> >>>
> >>> --
> >>> Arthur
> >>>
> >>>
> >>
> >> --
> >> Arthur
> >>
> >>
> >
> > --
> > Arthur
> >
> >
>
> --
> Arthur
> --
> 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