Re: SQL server question

On Jun 6, 7:14 pm, "David W. Fenton" <XXXuse...@xxxxxxxxxxxxxxxxxxx>
wrote:
"Albert D. Kallal" <PleaseNOOOsPAMmkal...@xxxxxxx> wrote innews:5EAWl.3116$ke5.2669@xxxxxxxxxxxx:

I really only recommend using ADP's if you don't have an existing
application

Microsoft is deprecating it even for new development, so I don't
think I'd recommend it even in those circumstances.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

A funny way of deprecating:
(from http://office.microsoft.com/en-us/access/HA101679531033.aspx?pid=CH102098441033)

Create an Access project

Applies to: Microsoft Office Access 2007

Show All

An Access project is an Access file (.adp) that provides a direct,
efficient
connection to a Microsoft SQL Server database through the OLE DB
component
architecture. You can connect an Access project to a remote SQL Server
database,
a local SQL Server database, or a local installation of the SQL Server
Desktop
engine.

Microsoft Office Access 2007 allows you to create new Access project
(.adp)
files, in addition to working with existing Access projects that you
created in
earlier versions of Access. This article provides a brief explanation
of Access
projects, tells you how Access projects work in Office Access 2007,
and then
tells you how to create a new Access project in Access 2007 or connect
an Access
project to an existing SQL Server database.

What do you want to do?

Understand Access projects

Understand how to work with Access projects in Office Access 2007

Create an Access project

Connect an existing Access project to a different SQL Server database

Understand Access projects

Creating an Access project provides several advantages over linking to
SQL
Server tables or views. For example, in an Access project, you can
make design
changes to SQL Server objects, such as tables and views, and you can
create,
edit, and use other SQL Server features, such as database diagrams,
stored
procedures, and user-defined functions. In contrast, when you link to
a SQL
Server database, you cannot make design changes to any SQL Server
objects, and
you can only link to SQL Server tables and views.

For more information about either importing or linking to SQL Server
data, click
the links in the See Also section.

Unlike an Access database, an Access project does not contain any data
or data-definition-based
objects. Instead, these database objects are stored in the SQL Server
database.

Working with an Access project is very similar to working with an
Access
database. The processes of creating forms, reports, data access pages,
macros,
and modules are virtually the same as that used to create an Access
database.

After you connect to a SQL Server database, you can view, create,
modify, and
delete tables, views, stored procedures, user-defined functions, and
database
diagrams by using the Table Designer, Query Builder, and SQL Text
Editor tools
in Access. Although the user interface used for working with these
database
objects in an Access project is different from the user interface used
for
working with equivalent database objects in an Access database, the
Access
project user interface is just as user friendly. An Access project
also contains
many of the same wizards as an Access database, such as the Form
Wizard, the
Report Wizard, and the Input Mask Wizard. These wizards can help you
to quickly
create a prototype or simple application, and they can also make it
easier to
create an advanced application.

Top of Page

Understand how to work with Access projects in Office Access 2007

The process for creating an Access project in Access 2007 is not as
obvious as
in earlier versions of Access. However, you can still create new
Access projects
and continue to use your existing ones in Access 2007. There are no
file format
differences between Access 2007 projects and those created in earlier
versions
of Access.

When you work with Access projects, you can take advantage of many of
the new
features of Access 2007, such as the Navigation Pane customization
tools, the
improved form and report design tools, and embedded macros. However,
certain new
features of Access 2007 are not available in Access projects, such as
attachments, multivalued fields, or integration with other programs,
such as
Microsoft Office Outlook 2007 or Microsoft Windows SharePoint Services
3.0. In
addition, Layout View is not available for designing forms and
reports.

Top of Page

Create an Access project

Click the Microsoft Office Button , and then click New.

Alternatively, on the Getting Started with Microsoft Office Access
page, under
New Blank Database, click Blank Database.

Do one of the following:

Create an Access project in the default file location

Under Blank Database, type a name for the project in the File Name box
using the
..adp file name extension.

-or-

Create an Access project in a different file location

Under Blank Database, click Browse for a location in which to save
your database.

In the File New Database dialog box, browse to the location where you
want to
save the project, or accept the default location.

Type a file name for the project in the File name box.

In the Save as type list, select Microsoft Office Access Projects
(*.adp), and
then click OK.

Under New Project, click Create.

Access prompts you with the following message:

Do you want to connect to an existing SQL Server database?

If you want to connect to an existing SQL Server database, click Yes
and
continue with step 4; otherwise, click No and skip to step 5 to create
a new SQL
Server database.

Connect to an existing SQL Server database

In the Data Link Properties dialog box, enter the required information
for the
following:

Server name

Information needed to log on to the server

Database name

Click Test Connection to verify that the settings are correct and that
the
connection succeeds.

If the connection does not succeed, the Microsoft Data Link Error
dialog box
displays a message describing the problem. Click OK to close the
message, check
your settings in the Data Link Properties dialog box, and then click
Test
Connection again. Also, you might need to check with the SQL Server
database
administrator for the database you are connecting to, to make sure
your settings
are correct or that the computer is available.

After the Microsoft Data Link dialog box displays the message Test
connection
succeeded, click OK to close the dialog box, and then click OK to
close the Data
Link Properties dialog box.

Access connects the project to the SQL Server database.

Create a new SQL Server database

On the first page of the Microsoft SQL Server Database Wizard, enter
the
following information:

The SQL Server computer you want to use for this database

Your logon information

A name for the SQL Server database

Note To skip the connection process at this time, click Cancel. Access
creates
the project, but it is not connected to a SQL Server database. Before
you can do
anything with the project, you will need to connect it to a SQL Server
database,
which you can do by using the steps in the next section.

Click Next to continue.

Access attempts to connect to the SQL server computer you specified.
If Access
cannot connect to the SQL server computer, it displays an error
message
describing the problem. The SQL server computer name or logon
information might
be incorrect, or you might not have the necessary permissions to
create a
database on the SQL Server computer that you specified. You might need
to check
with the SQL Server database administrator for the database you are
creating, to
make sure your settings are correct or that the computer is
available.

After Access successfully connects to the SQL Server computer, the
wizard
displays the message: The wizard has all the information it needs to
create your
SQL Server database. Click Finish to continue.

Access creates the SQL Server database and connects it to the
project.

Top of Page

Connect an existing Access project to a different SQL Server database

If you have an existing Access project that is not connected to a SQL
Server
database, or if you want to change the SQL Server computer or database
to which
your project is connected, use the following procedure.

Click the Microsoft Office Button , and then click Open.

In the Open dialog box, select and open the Access project.

A Microsoft Office Access Security Notice dialog box might appear. If
you trust
the source of the database, click Open to continue.

Click the Microsoft Office Button , point to Server, and then click
Connection.

The Data Link Properties dialog box appears.

In the dialog box, enter the required information for the following:

Server name

Information needed to log on to the server

Database name

Click Test Connection to verify that the settings are correct and that
the
connection succeeds. If the connection does not succeed, Access
displays an
error message describing the problem. Click OK to close the message,
check your
settings in the Data Link Properties dialog box, and then click Test
Connection
again. Also, you might need to check with the SQL Server database
administrator
for the database you are connecting to, to make sure your settings are
correct
or that the computer is available.

After the Microsoft Data Link dialog box displays the message Test
connection
succeeded, click OK to close the dialog box, and then click OK to
close the Data
Link Properties dialog box.

Access connects the project to the SQL Server database you specified.

.