access

Understanding and Creating an Access Project – ASP Free

Creating the Project

Open the MS Access application from Start -> All Programs. This brings up the application and displays the following screen. The MS Access Project related items are shown bracketed in a red rectangle. You have two options, if you have created a Access project. For now, click on Project (New Data).

Access application just opened

It immediately opens a File New database window, with the default location My Documents with a default file name, adp1.adp. You should change this to something meaningful, such as ProjAcc in this case.

This makes the MS Access application name change to the chosen name, and at the same time opens up the Microsoft SQL Server database Wizard (there are more wizards in MS than in the Harry Potter books). The wizard has already recognized the resident SQL 2000 Server, XPHTEK including the authentication information (use a trusted connection, namely Windows authentication) and it has even given a database name, which is the Project name concatenated with “SQL” as shown.

Click Next and then click Finish.

The MS Access application screen has changed. Instead of being disconnected, it is now connected. Compare the two screens. Now it displays ProjAcc: Project-ProjAccSQL (Access 2000 Format).

If you now open SQL 2000 Server’s Enterprise Manager and look for the database ProjAccSQL, you will sure enough see a complete SQL database with all the objects as seen in the next picture.

Populating with Data

With the database created, it is now possible to add in data. In this tutorial, we shall see how we can link to an already existing SQL database so that we can use that data in the Access Project.

Linking with Data

You can link to existing data by going to the File –>Get External Data–> Link Tables…. In this tutorial you will link to the tables, and therefore you choose Link Tables.

This action wakes up another wizard, the Linked Table Wizard. The wizard helps you with linking to the tables on the database, using an OLEDB connection. Here you have two choices. The radio button Linked Server choice gives the most functionality, and allows storing the connection information on the SQL Server. For the tables that need to be used (or linked to), views will be created on the SQL Server. Make sure you read the information provided on this screen. By choosing the Linked Server, it may even be possible to update data on the server, if the OLEDB supports updating. The choice Transact SQL provides a read-only connection. After the choice, click Next.

This opens up the Select Data Source which allows you to browse for an existing connection from a listed source of existing connections, My Data Sources. Here you will make a new connection by clicking on the New Source… button as shown in the next picture.

This brings up the Data Connection Wizard as shown. Here you will see a list of all sources that support the OLEDB connectivity, such as OLAP Server, Oracle, and so on. Choose the Microsoft SQL Server and click Next.

Connecting to SQL Server

You need to provide the connection information to the server by filling out the needed information, namely, the server name and authentication information. If you choose the SQL Server authentication, you should provide User Name and Password. After this you click Next to access the next step of the wizard.

The next step is to choose the database and table. The wizard automatically opens up with the default database, master, as shown.

Selecting the Database

However, you can choose any other existing databases on the SQL Server. All the databases can be accessed from the drop down. Here the Northwind database is chosen as well as the the table Product Sales for 1997. Since this is just the connection step to the database, the table selection at this point does not really matter as you will shortly see. You may now click on Next.

This bring us to the next step, where the connection information is saved. The filename of this connection is the Server name, concatenated with the table you chose in the Database server connection step earlier, as seen in this screen. You may add a short description to this to help your future searches for a connection. You may now click Finish.

This brings you to this final screen in the Select Data Source screen. The connection file will also be saved in your My Data Sources folder.You may now click on Open to enter the next step, choosing the tables.

via Understanding and Creating an Access Project – ASP Free.

Migration considerations for Access 2007

Access Data Projects (ADPs)

An Access Data Project is an OLE document file, such as the .xls or.doc file formats. It contains forms, reports, macros, VBA modules, and a connection string. All tables and queries are stored in SQL Server. The ADP architecture was designed to create client-server applications. Because of this, there is a limit to the number of records that Access returns in any recordset. This limit is configurable, but you typically must build enough filtering into your application so that you do not reach the limit.

Access uses OLEDB to communicate with SQL Server. To provide the Jet-like cursor behavior desired for desktop applications, Access implements the Client Data Manager (CDM) as an additional layer between Access and OLEDB.

Because of the layers required to get from Access to SQL Server in the ADP architecture, it is often easier to optimize MDB/ACCDB file solutions. However, there are some scenarios where a report might be generated significantly faster in an ADP file. To add these performance improvements and retain the flexibility of SQL Server, you can build the majority of the application in an MDB or ACCDB file and have the file load reports from a referenced ADP file.

One advantage that ADP files have over files in MDB or ACCDB format is the ability to make design changes to SQL Server objects. ADP files include graphical designers for tables, views, stored procedures, functions, and database diagrams.

You cannot directly modify the design of Linked Tables. You must use an ADP file or Enterprise Manager included in SQL Server to make schema changes or design changes.

via Migration considerations for Access 2007.