Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
افزودن یادداشت
افزودن یادداشت جدید










Recipe 14.8 Reference Data from More than One SQL Server Database in an ADP



14.8.1 Problem


You'd like to have
your ADP connect to multiple SQL Server databases at one time.
However, the Data Link dialog allows room for only one SQL Server
database.


14.8.2 Solution


Although at first glance this seems to
be a problem, the solution is readily at hand with SQL
Server's three-part naming convention. You are
probably already familiar with the
OwnerName.ObjectName syntax for referring to SQL
Server objects, which is needed when users other than the owner (or
creator) of that object wish to use the object. The three-part naming
syntax is:

DatabaseName.OwnerName.ObjectName

To refer to another SQL Server database in your ADP, follow these
steps:

  1. Create a new project and link it to the Northwind database. You can
    look at the list of tables and see only the tables from Northwind.

  2. Create a new form. Type the following
    statement into the RecordSource property of the form:

    SELECT * FROM pubs.dbo.authors
  3. You will then see the Field List for the authors
    table in the pubs database. Figure 14-18 displays the Field List from the sample form,
    frmPubsAuthorsSQL, and shows that the form is now
    bound to data in the pubs database, not the
    Northwind database.



Figure 14-18. The Field List from frmPubsAuthorsSQL


  1. Alternately, you can create a view in the Northwind database that
    selects data from the pubs.authors table:

    CREATE VIEW vwPubsAuthors
    AS
    SELECT au_id, au_lname, au_fname, phone,
    address, city, state, zip, contract
    FROM pubs.dbo.authors

    You can then base forms and reports in your Northwind project on the
    view.



14.8.3 Discussion


SQL Server allows users to access other databases residing on the
same server when the three-part naming syntax is used. However, users
must have been granted permissions in the source database if data is
to be accessed with a direct SQL statement. SQL Server will return a
permissions error message if those permissions have not been granted.

Working with data from multiple databases is easy in ADPs, even
though you see the objects from only one database listed in the
database window. Just remember to use the three-part naming syntax.

If the data you need is not just in
another database but on another server, it gets a little more
complicated. In this case, you need to set up a linked server in SQL
Server to access the data. Linked servers in SQL Server use OLE DB
providers, which means you are not limited to only SQL Server data.
Linked servers allow you to use SQL Server as a gateway to many
different data sources, just as you may use Access databases to link
to multiple data sources.


/ 232