[AccessD] Link to AdventureWorks Database

[Arthur Fuller]

Thanks, Paul.
It worked this time. I was using the wrong authentication to get into SSMS.
Doh!

On Tue, Jan 10, 2023 at 3:30 PM Paul Wolstenholme <
Paul.W at industrialcontrol.co.nz> wrote:

> 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
>


-- 
Arthur